[오라클]사용자별 오브젝트 갯수 조회

SELECT
        A.OWNER,
        A.TABLE_COUNT,
        A.VIEW_COUNT,
        B.PK_COUNT,
        A.INDEX_COUNT,
        B.UK_COUNT,
        B.FK_COUNT,
        B.CK_COUNT,
        A.SYNONYM_COUNT,
        A.SEQUENCE_COUNT,
        A.FUNCTION_COUNT,
        A.PROCEDURE_COUNT,
        A.PACKAGE_COUNT,  
        A.PACKAGE_BODY_COUNT,
        A.TRIGGER_COUNT,
        A.LOB_COUNT,
        A.TABLE_PARTITION_COUNT,
        A.INDEX_PARTITION_COUNT,
        A.CLUSTER_COUNT,   
        A.LIBRARY_COUNT,   
        A.DIRECTORY_COUNT,   
        A.JAVA_SOURCE_COUNT,   
        A.JAVA_CLASS_COUNT,
        A.JAVA_RESOURCE_COUNT,
        A.JAVA_DATA_COUNT,
        A.INDEXTYPE_COUNT,
        A.OPERATOR_COUNT,
        A.TYPE_COUNT,
        A.TYPE_BODY_COUNT,
        A.RESOURCE_PLAN_COUNT,
        A.CONSUMER_GROUP_COUNT,
        A.MATERIALIZED_VIEW_COUNT,
        A.TABLE_SUBPARTITION_COUNT,
        A.INDEX_SUBPARTITION_COUNT,
        A.LOB_PARTITION_COUNT,
        A.LOB_SUBPARTITION_COUNT,
        A.DIMENSION_COUNT,
        A.CONTEXT_COUNT,  
        A.RULE_SET_COUNT,  
        A.XML_SCHEMA_COUNT,
        A.SECURITY_PROFILE_COUNT,
        A.RULE_COUNT,
        A.CAPTURE_COUNT,
        A.APPLY_COUNT,
        A.EVALUATION_CONTEXT_COUNT,
        A.PROGRAM_COUNT,
        A.JOB_COUNT,
        A.JOB_CLASS_COUNT,
        A.SCHEDULE_COUNT,
        A.WINDOW_COUNT,
        A.WINDOW_GROUP_COUNT,
        A.CHAIN_COUNT,
        A.NEXT_OBJECT_COUNT,
        A.QUEUE_COUNT,
        A.FILE_GROUP_COUNT
 FROM
 (SELECT /*+ ALL_ROWS */
         U.NAME                      AS OWNER,
         SUM(DECODE(O.TYPE#,  0, 1)) AS NEXT_OBJECT_COUNT,
         SUM(DECODE(O.TYPE#,  1, 1)) AS INDEX_COUNT,
         SUM(DECODE(O.TYPE#,  2, 1)) AS TABLE_COUNT,
         SUM(DECODE(O.TYPE#,  3, 1)) AS CLUSTER_COUNT,
         SUM(DECODE(O.TYPE#,  4, 1)) AS VIEW_COUNT,
         SUM(DECODE(O.TYPE#,  5, 1)) AS SYNONYM_COUNT,
         SUM(DECODE(O.TYPE#,  6, 1)) AS SEQUENCE_COUNT,
         SUM(DECODE(O.TYPE#,  7, 1)) AS PROCEDURE_COUNT,
         SUM(DECODE(O.TYPE#,  8, 1)) AS FUNCTION_COUNT,
         SUM(DECODE(O.TYPE#,  9, 1)) AS PACKAGE_COUNT, 
         SUM(DECODE(O.TYPE#, 11, 1)) AS PACKAGE_BODY_COUNT,
         SUM(DECODE(O.TYPE#, 12, 1)) AS TRIGGER_COUNT,
         SUM(DECODE(O.TYPE#, 13, 1)) AS TYPE_COUNT,
         SUM(DECODE(O.TYPE#, 14, 1)) AS TYPE_BODY_COUNT,
         SUM(DECODE(O.TYPE#, 19, 1)) AS TABLE_PARTITION_COUNT,
         SUM(DECODE(O.TYPE#, 20, 1)) AS INDEX_PARTITION_COUNT,
         SUM(DECODE(O.TYPE#, 21, 1)) AS LOB_COUNT,
         SUM(DECODE(O.TYPE#, 22, 1)) AS LIBRARY_COUNT,
         SUM(DECODE(O.TYPE#, 23, 1)) AS DIRECTORY_COUNT,
         SUM(DECODE(O.TYPE#, 24, 1)) AS QUEUE_COUNT,
         SUM(DECODE(O.TYPE#, 28, 1)) AS JAVA_SOURCE_COUNT,
         SUM(DECODE(O.TYPE#, 29, 1)) AS JAVA_CLASS_COUNT,
         SUM(DECODE(O.TYPE#, 30, 1)) AS JAVA_RESOURCE_COUNT,
         SUM(DECODE(O.TYPE#, 32, 1)) AS INDEXTYPE_COUNT,
         SUM(DECODE(O.TYPE#, 33, 1)) AS OPERATOR_COUNT,
         SUM(DECODE(O.TYPE#, 34, 1)) AS TABLE_SUBPARTITION_COUNT,
         SUM(DECODE(O.TYPE#, 35, 1)) AS INDEX_SUBPARTITION_COUNT,
         SUM(DECODE(O.TYPE#, 40, 1)) AS LOB_PARTITION_COUNT,
         SUM(DECODE(O.TYPE#, 41, 1)) AS LOB_SUBPARTITION_COUNT,
         SUM(DECODE(O.TYPE#, 42, NVL((SELECT DISTINCT 'REWRITE EQUIVALENCE'
                                        FROM SYS.SUM$ S
                                       WHERE S.OBJ#=O.OBJ#
                                         AND bitand(S.XPFLAGS, 8388608) = 8388608),
                                      1))) AS MATERIALIZED_VIEW_COUNT,
         SUM(DECODE(O.TYPE#, 43, 1)) AS DIMENSION_COUNT,
         SUM(DECODE(O.TYPE#, 44, 1)) AS CONTEXT_COUNT,
         SUM(DECODE(O.TYPE#, 46, 1)) AS RULE_SET_COUNT,
         SUM(DECODE(O.TYPE#, 47, 1)) AS RESOURCE_PLAN_COUNT,
         SUM(DECODE(O.TYPE#, 48, 1)) AS CONSUMER_GROUP_COUNT,
         SUM(DECODE(O.TYPE#, 55, 1)) AS XML_SCHEMA_COUNT,
         SUM(DECODE(O.TYPE#, 56, 1)) AS JAVA_DATA_COUNT,
         SUM(DECODE(O.TYPE#, 57, 1)) AS SECURITY_PROFILE_COUNT, 
         SUM(DECODE(O.TYPE#, 59, 1)) AS RULE_COUNT, 
         SUM(DECODE(O.TYPE#, 60, 1)) AS CAPTURE_COUNT, 
         SUM(DECODE(O.TYPE#, 61, 1)) AS APPLY_COUNT,
         SUM(DECODE(O.TYPE#, 62, 1)) AS EVALUATION_CONTEXT_COUNT,
         SUM(DECODE(O.TYPE#, 66, 1)) AS JOB_COUNT,
         SUM(DECODE(O.TYPE#, 67, 1)) AS PROGRAM_COUNT,
         SUM(DECODE(O.TYPE#, 68, 1)) AS JOB_CLASS_COUNT,
         SUM(DECODE(O.TYPE#, 69, 1)) AS WINDOW_COUNT,
         SUM(DECODE(O.TYPE#, 72, 1)) AS WINDOW_GROUP_COUNT,
         SUM(DECODE(O.TYPE#, 74, 1)) AS SCHEDULE_COUNT,
         SUM(DECODE(O.TYPE#, 79, 1)) AS CHAIN_COUNT,
         SUM(DECODE(O.TYPE#, 81, 1)) AS FILE_GROUP_COUNT
    FROM SYS.OBJ$ O, SYS.USER$ U
   WHERE O.OWNER# = U.USER# 
     AND U.NAME NOT IN ('SYS','SYSTEM','BIZMAX','DBSNMP','OUTLN','WMSYS','ORDSYS','TSMSYS',
                        'ORDPLUGINS','RMAN','MDSYS','CTXSYS','WKSYS','WKPROXY','ODM',
                        'ODM_MTR','OLAPSYS','PERFSTAT','ORANGE','WASJMS','SYSMAN',
                        'TMAX','DBMSTOOL','MIBWINE' )
   GROUP BY U.NAME ) A,
  (SELECT /*+ ALL_ROWS */
         OWNER                       AS OWNER,
         SUM(DECODE(C.CONSTRAINT_TYPE, 'P', 1)) AS PK_COUNT,
         SUM(DECODE(C.CONSTRAINT_TYPE, 'U', 1)) AS UK_COUNT,
         SUM(DECODE(C.CONSTRAINT_TYPE, 'R', 1)) AS FK_COUNT,
         SUM(DECODE(C.CONSTRAINT_TYPE, 'C', 1)) AS CK_COUNT 
    FROM SYS.DBA_CONSTRAINTS C
   WHERE C.OWNER NOT IN ('SYS','SYSTEM','BIZMAX','DBSNMP','OUTLN','WMSYS','ORDSYS','TSMSYS',
                         'ORDPLUGINS','RMAN','MDSYS','CTXSYS','WKSYS','WKPROXY','ODM',
                         'ODM_MTR','OLAPSYS','PERFSTAT','ORANGE','WASJMS','SYSMAN',
                         'TMAX','DBMSTOOL','MIBWINE' )
     AND C.GENERATED = 'USER NAME'
   GROUP BY C.OWNER ) B
   WHERE A.OWNER = B.OWNER(+);