Code:
create or replace type r_counter is object
( u_name varchar2(30),
o_cnt number
);
/
create or replace type t_counter as table of r_counter;
/
SQL> set lines 130
set pages 9999
col ot format a25
col cnt format a95 word
select a.ot,
cast(multiset( select b.owner, count(*)
from all_objects b
where b.object_type = a.ot
and
b.owner in ('SYS','SYSTEM','TRAIN')
group by owner )
as t_counter) cnt
from ( select distinct object_type ot from all_objects) a;
SQL>
OT CNT(U_NAME, O_CNT)
------------------------- -----------------------------------------------------------------------------------------------
CLUSTER T_COUNTER(R_COUNTER('TRAIN', 1))
CONSUMER GROUP T_COUNTER(R_COUNTER('SYS', 3))
FUNCTION T_COUNTER(R_COUNTER('SYS', 21), R_COUNTER('SYSTEM', 12), R_COUNTER('TRAIN', 12))
INDEX T_COUNTER(R_COUNTER('SYS', 208), R_COUNTER('SYSTEM', 18), R_COUNTER('TRAIN', 109))
LIBRARY T_COUNTER(R_COUNTER('SYS', 19))
PACKAGE T_COUNTER(R_COUNTER('SYS', 122), R_COUNTER('SYSTEM', 1), R_COUNTER('TRAIN', 4))
PACKAGE BODY T_COUNTER(R_COUNTER('SYS', 117), R_COUNTER('SYSTEM', 2), R_COUNTER('TRAIN', 4))
PROCEDURE T_COUNTER(R_COUNTER('SYS', 10), R_COUNTER('SYSTEM', 12), R_COUNTER('TRAIN', 7))
SEQUENCE T_COUNTER(R_COUNTER('SYS', 28), R_COUNTER('TRAIN', 10))
SYNONYM T_COUNTER(R_COUNTER('SYS', 6), R_COUNTER('SYSTEM', 8))
TABLE T_COUNTER(R_COUNTER('SYS', 186), R_COUNTER('SYSTEM', 24), R_COUNTER('TRAIN', 85))
TRIGGER T_COUNTER(R_COUNTER('TRAIN', 12))
TYPE T_COUNTER(R_COUNTER('SYS', 93), R_COUNTER('TRAIN', 10))
VIEW T_COUNTER(R_COUNTER('SYS', 1144), R_COUNTER('SYSTEM', 24), R_COUNTER('TRAIN', 12))
14 rows selected.