oracle group objects by owner(users)

Dieses Skript gibt einfach ein weiteres Skript aus, welches gestartet werden kann und die Anzahl (z.b. Tabellen,Sequenzen,ect…) pro User ausgibt.

select  'select owner,' from dual
union all  
select  'sum(case object_type '||to_Char(replace(wm_concat('when '''||o||''' then 1 '),','))||' else null end) as "'||case count(*) when 1 then max(o) else min(o) end||'",'
  from (select  object_type o
          from  dba_objects
          group by object_Type)
  group by substr(o||' ',0,4)
union all
select  'count(*) cnt from dba_objects group by owner order by 1 asc'
  from  dual;

Wer das ausgegebene Skript noch etwas Formatiert mag kann auch dieses Skript nutzen:

select  'select  owner,' from dual
union all  
select  lpad(' ',8,' ')||
        'sum(case object_type '||
        to_Char(replace(wm_concat('when '''||o||''''||lpad(' ',(20-length(o)),' ')||' then 1 '),',',rpad(chr(10),30,' ')))||
        ' else null end) as "'||
        case count(*) when 1 then max(o) else min(o) end||'",'
  from (select  object_type o
          from  dba_objects
          group by object_Type)
  group by substr(o||' ',0,4)
union all
select  '        count(*) cnt'||chr(10)||
        '  from  dba_objects'||chr(10)||
        '  group by  owner'||chr(10)||
        '  order by  1 asc'
  from  dual;

Der Output der beiden Skripte wird ca. so aussehen (je nach Formatierung):

select  owner,
        sum(case object_type when 'CLUSTER'              then 1  else null end) as "CLUSTER",
        sum(case object_type when 'CONSUMER GROUP'       then 1  else null end) as "CONSUMER GROUP",
        sum(case object_type when 'CONTEXT'              then 1  else null end) as "CONTEXT",
        sum(case object_type when 'DESTINATION'          then 1  else null end) as "DESTINATION",
        sum(case object_type when 'DIRECTORY'            then 1  else null end) as "DIRECTORY",
        sum(case object_type when 'EDITION'              then 1  else null end) as "EDITION",
        sum(case object_type when 'EVALUATION CONTEXT'   then 1  else null end) as "EVALUATION CONTEXT",
        sum(case object_type when 'FUNCTION'             then 1  else null end) as "FUNCTION",
        sum(case object_type when 'INDEX PARTITION'      then 1 
                             when 'INDEXTYPE'            then 1 
                             when 'INDEX'                then 1  else null end) as "INDEX",
        sum(case object_type when 'JAVA DATA'            then 1 
                             when 'JAVA SOURCE'          then 1 
                             when 'JAVA CLASS'           then 1 
                             when 'JAVA RESOURCE'        then 1  else null end) as "JAVA CLASS",
        sum(case object_type when 'JOB CLASS'            then 1 
                             when 'JOB'                  then 1  else null end) as "JOB",
        sum(case object_type when 'LIBRARY'              then 1  else null end) as "LIBRARY",
        sum(case object_type when 'LOB PARTITION'        then 1 
                             when 'LOB'                  then 1  else null end) as "LOB",
        sum(case object_type when 'OPERATOR'             then 1  else null end) as "OPERATOR",
        sum(case object_type when 'PACKAGE'              then 1 
                             when 'PACKAGE BODY'         then 1  else null end) as "PACKAGE",
        sum(case object_type when 'PROCEDURE'            then 1  else null end) as "PROCEDURE",
        sum(case object_type when 'PROGRAM'              then 1  else null end) as "PROGRAM",
        sum(case object_type when 'QUEUE'                then 1  else null end) as "QUEUE",
        sum(case object_type when 'RESOURCE PLAN'        then 1  else null end) as "RESOURCE PLAN",
        sum(case object_type when 'RULE'                 then 1 
                             when 'RULE SET'             then 1  else null end) as "RULE",
        sum(case object_type when 'SCHEDULE'             then 1 
                             when 'SCHEDULER GROUP'      then 1  else null end) as "SCHEDULE",
        sum(case object_type when 'SEQUENCE'             then 1  else null end) as "SEQUENCE",
        sum(case object_type when 'SYNONYM'              then 1  else null end) as "SYNONYM",
        sum(case object_type when 'TABLE SUBPARTITION'   then 1 
                             when 'TABLE'                then 1 
                             when 'TABLE PARTITION'      then 1  else null end) as "TABLE",
        sum(case object_type when 'TRIGGER'              then 1  else null end) as "TRIGGER",
        sum(case object_type when 'TYPE BODY'            then 1 
                             when 'TYPE'                 then 1  else null end) as "TYPE",
        sum(case object_type when 'UNDEFINED'            then 1  else null end) as "UNDEFINED",
        sum(case object_type when 'VIEW'                 then 1  else null end) as "VIEW",
        sum(case object_type when 'WINDOW'               then 1  else null end) as "WINDOW",
        sum(case object_type when 'XML SCHEMA'           then 1  else null end) as "XML SCHEMA",
        count(*) cnt
  from  dba_objects
  group by  owner
  order by  1 asc;