[오라클]Invalid / Disabled 된 오브젝트 관리

오라클의 OBJECT들 중 INVALID나 DISABLED된 것을 체크하는 스크립트이다.

SET PAGESIZE 1000 LINESIZE 120
COLUMN OBJECT_DV   FORMAT A10
COLUMN OWNER       FORMAT A15
COLUMN TABLE_NAME  FORMAT A30
COLUMN OBJECT_TYPE FORMAT A20
COLUMN NAME        FORMAT A30
COLUMN STATUS      FORMAT A10

 

SELECT * FROM
(
SELECT 'CONSTRAINT'    OBJECT_DV        ,
       OWNER                            ,
       TABLE_NAME      OBJECT_NAME      ,
       CONSTRAINT_TYPE OBJECT_TYPE      ,
       CONSTRAINT_NAME NAME             ,
       STATUS
  FROM SYS.DBA_CONSTRAINTS
 WHERE STATUS = 'DISABLED'
UNION ALL
SELECT 'TRIGGER'    OBJECT_DV           ,
       OWNER                            ,
       TABLE_NAME   OBJECT_NAME         ,
       'TRIGGER'    OBJECT_TYPE         ,
       TRIGGER_NAME NAME                ,
       STATUS
  FROM SYS.DBA_TRIGGERS
 WHERE STATUS = 'DISABLED'
UNION ALL
SELECT 'OBJECT'    OBJECT_DV            ,
       OWNER                            ,
       'OBJECT'    OBJECT_NAME          ,
       OBJECT_TYPE OBJECT_TYPE          ,
       OBJECT_NAME NAME                 ,
       STATUS
  FROM SYS.DBA_OBJECTS
 WHERE STATUS      = 'INVALID'
UNION ALL
SELECT 'INDEX'           OBJECT_DV      ,
       OWNER             OWNER          ,
       INDEX_NAME        OBJECT_NAME    ,
       'INDEX'           OBJECT_TYPE    ,
       INDEX_NAME        NAME           ,
       STATUS
  FROM SYS.DBA_INDEXES
 WHERE STATUS IN ('UNUSABLE','INVALID')
UNION ALL
SELECT 'PART IDX'        OBJECT_DV      ,
       INDEX_OWNER       OWNER          ,
       INDEX_NAME        OBJECT_NAME    ,
       'PART INDEX'      OBJECT_TYPE    ,
       PARTITION_NAME    NAME           ,
       STATUS
  FROM SYS.DBA_IND_PARTITIONS
 WHERE STATUS IN ('UNUSABLE','INVALID')
)
 WHERE OWNER NOT IN ('SYS','SYSTEM');