[Oracle] PL/SQL, Object Table_Row, INVALID 메타 데이터 조회

PL/SQL 메타데이터

Oracle 의 PL/SQL 메타 데이터 조회 쿼리가 필요한 경우 아래의 쿼리를 실행시킨다.

SELECT A.OWNER AS OWNER
     , A.NAME AS OBJ_NAME
     , MAX(OBJECT_TYPE) AS OBJ_TYPE
     , MAX(LINE) AS TOTAL_LINES
  FROM DBA_SOURCE A
     , DBA_OBJECTS B
 WHERE 0=0
   AND A.NAME = B.OBJECT_NAME
   AND A.OWNER = B.OWNER
   AND A.owner NOT IN ( 'SYS', 'SYSTEM', 'SYSMAN', 'PERFSTAT', 'DBSNMP', 'OUTLN', 'TSMSYS', 'MDSYS',
                       'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS', 'IX', 'CTXSYS', 'OLAPSYS', 'ANONYMOUS',
                       'OUTLN', 'DIP', 'OE', 'XDB', 'ORDPLUGINS', 'WKSYS', 'WK_TEST','ORDDATA',
                       'APEX_030200','FLOWS_FILES','OWBSYS','APPQOSSYS','ORACLE_OCM')
GROUP BY A.OWNER, A.NAME
ORDER BY 1,3 DESC,2
;

Object_table_row 조회

SELECT A.OWNER
     , A.OBJECT_NAME AS TABLE_NAME
     , MAX(C.NUM_ROWS) AS ABOUT_ROWS
     , SUM(B.BYTES)/POWER(1024,2) AS TAB_SIZE_MB
  FROM DBA_OBJECTS  A
     , DBA_SEGMENTS B
     , DBA_TABLES   C
 WHERE 0=0
   AND A.OWNER = B.OWNER
   AND A.OWNER = C.OWNER
   AND A.OBJECT_NAME = B.SEGMENT_NAME(+)
   AND A.OBJECT_NAME = C.TABLE_NAME(+)
   AND A.owner NOT IN ( 'SYS', 'SYSTEM', 'SYSMAN', 'PERFSTAT', 'DBSNMP', 'OUTLN', 'TSMSYS', 'MDSYS',
                       'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS', 'IX', 'CTXSYS', 'OLAPSYS', 'ANONYMOUS',
                       'OUTLN', 'DIP', 'OE', 'XDB', 'ORDPLUGINS', 'WKSYS', 'WK_TEST','ORDDATA',
                       'APEX_030200','FLOWS_FILES','OWBSYS','APPQOSSYS','ORACLE_OCM') 
GROUP BY A.OWNER, A.OBJECT_NAME          
;

INVALID

select owner, object_type, object_name, status
from dba_objects
where status='INVALID'
order by owner;