Show summary of number of objects in schemas
SYNONYM, CLUSTER, CONSUMER GROUP, INDEX PARTITION, TABLE PARTITION, LIBRARY, QUEUE
Exclude above types [Y] :
Include SYS/SYSTEM [N] :
Include users without objects [N] :
DATABASE EVALUATI FUNCTION INDEX INDEXTYP JAVA CLA JAVA RES JOB LOB MATERIAL OPERATOR PACKAGE PACKAGE PROCEDUR RULE SET SEQUENCE TABLE TABLE SU TRIGGER TYPE TYPE BOD VIEW XML SCHE
LINK ON CONTE E SS OURCE IZED VIE BODY E BPARTITI Y MA
-------- -------- -------- ----- -------- -------- -------- --- --- -------- -------- ------- -------- -------- -------- -------- ----- -------- ------- ---- -------- ---- --------
ACCOUNT . . . 28 . . . . . . . 3 3 2 . 2 45 . . . . . . 83
ALPH_USER . . . 1 . . . . . . . 1 1 . . . 1 . . 8 . . . 12
APEX_030200 . . 12 1,101 . . . . 155 . . 189 181 19 . 4 360 . 366 4 . 125 . 2,516
APPQOSSYS . . . . . . . . . . . . . . . . 4 . . . . . . 4
CTXSYS . . 2 63 4 . . . 2 . 6 74 63 2 . 3 50 . . 35 6 77 . 387
DATA1_A_B_1 . . . 13 . . . . . . . . . . . 11 33 . 11 . . . . 68
DATA2_A_B_2 . . . 7 . . . . . . . . . . . 9 26 . 11 . . . . 53
DATA3_A_B_3 . . . 5 . . . . . . . . . . . 9 25 . 11 . . . . 50
DATA4_A_B_4 . . . 5 . . . . . . . . . . . 9 24 . 11 . . . . 49
DBSNMP . . . 13 . . . . . . . 4 4 1 . 2 25 . . 8 . 7 . 64
DEV_TOOLS . . . . . . . . . . . 1 1 . . . . . . . . . . 2
DF_TEST_USER1 . . 1 32 . . . . . 3 . 1 1 4 . 4 31 . 5 7 . 5 . 94
DM_OBJECTS_OWNE . . . 14 . . . . . . . 1 1 . . 15 16 . 29 . . . . 76
DOBS_TEST . . . 1 . . . . 1 . . . . . . . 1 . . . . . . 3
EXFSYS . . 26 41 1 47 1 2 2 . 1 18 18 10 . 1 47 . 5 30 5 56 . 311
FLOWS_FILES . . . 5 . . . . 1 . . . . . . . 1 . 1 . . . . 8
MAIN_RELEASE . . . . . . . . . . . 1 1 . . . 6 . . . . . . 8
MDSYS . . 108 367 2 535 3 . 214 . 25 70 66 2 . 15 148 . 64 207 43 86 . 1,955
OLAPSYS . . 1 137 . . . . 2 . . 45 43 . . 5 126 . 48 7 . 307 . 721
ORACLE_OCM . . . . . . . 2 . . . 3 3 . . . . . . . . . . 8
ORDDATA . . . 138 . . . . 9 . . . . . . 12 73 . . . . 25 . 257
ORDPLUGINS . . . . . . . . . . . 5 5 . . . . . . . . . . 10
ORDSYS . . 32 4 . 1,876 72 . . . . 28 20 7 . . 5 . . 446 15 5 . 2,510
OUTLN . . . 5 . . . . 1 . . . . 1 . . 3 . . . . . . 10
OWBSYS . . . 1 . . . . . . . . . . . . 1 . . . . . . 2
PERS_DATA . . . 3 . . . . . . . 3 1 3 . . 2 . . . . . . 12
PURGE001 . . . . . . . . . . . . . . . . 2 . . . . . . 2
R3XX_AUDIT_DML . . . 5 . . . . 2 . . 2 2 1 . 4 34 . 23 . . . . 73
R4XX_AUDIT_RSCE 7 . 1 36 . . . . 3 . . 19 19 8 . 23 59 . 48 . . 1 . 224
R5XX_RPT_RSCE . . . . . . . . . . . . . . . . 12 . . . . . . 12
SCOTT . . . 2 . . . . . . . . . . . . 4 . . . . . . 6
SYSMAN . 2 12 1,028 . . . . 75 1 . 193 193 3 4 13 729 . 97 672 49 471 . 3,542
TEST_ACCESS_OBJ . . 1 . . . . . . . . 1 . 1 . . 6 . . . . . . 9
TEST_ACCESS_OBJ . . 1 . . . . . . . . 1 . 1 . . 6 . . . . . . 9
TEST_DATA_MAINT . . . . . . . . . . . . . . . . 6 . . . . . . 6
TEST_DATA_MAINT . . . . . . . . . . . . . . . . 6 . . . . . . 6
TEST_ON_MOVE1 . . . 7 . . . . . . . 2 2 2 . 1 4 . 2 . . 1 . 21
TEST_ON_MOVE2 . . . 7 . . . . . . . 2 2 2 . 1 4 . 2 . . 1 . 21
TEST_OWNER . . 1 21 . . . . 1 . . 8 9 13 . 10 72 . 10 . . 5 . 150
TEST_RELATION0 . . . . . . . . . . . . . 1 . 1 2 . . . . . . 4
TEST_RELATION2 . . . . . . . . . . . . . 1 . . . . . . . . . 1
TSMSYS . . . 2 . . . . 1 . . . . . . . 1 . . . . . . 4
WMSYS . 1 4 70 . . . . 9 . 9 22 22 4 2 9 46 . 2 18 1 111 . 330
XDB . . 6 697 2 . . . 586 . 7 35 33 4 . 5 94 . 27 97 5 20 53 1,671
No of users displayed : 44
PL/SQL procedure successfully completed.
prompt
spool uobjects.rpt
prompt SYNONYM, CLUSTER, CONSUMER GROUP, INDEX PARTITION, TABLE PARTITION, LIBRARY, QUEUE
prompt
accept x_exclude prompt "Exclude above types [Y] : " default 'Y'
accept x_sys_tem prompt "Include SYS/SYSTEM [N] : " default 'N'
accept x_all_users prompt "Include users without objects [N] : " default 'N'
declare
ls_exclude varchar2( 1 ) := upper( substr( nvl( '&x_exclude', 'N' ), 1, 1 ) );
ls_all_users varchar2( 1 ) := upper( substr( nvl( '&x_all_users', 'N' ), 1, 1 ) );
ls_sys_tem varchar2( 1 ) := upper( substr( nvl( '&x_sys_tem', 'N' ), 1, 1 ) );
cursor c_users is
select *
from dba_users
where ( username not in ( 'SYS', 'SYSTEM' ) or ls_sys_tem <> 'N' )
order by username;
cursor c_object_types is
select unique object_type
from dba_objects
where ( ls_exclude <> 'Y'
or object_type not in ( 'SYNONYM', 'CLUSTER', 'CONSUMER GROUP', 'INDEX PARTITION', 'TABLE PARTITION', 'LIBRARY', 'QUEUE' )
)
and ( owner not in ( 'SYS', 'SYSTEM' ) or ls_sys_tem <> 'N' )
order by 1;
cursor c_u_objects ( x_user in varchar2, x_obj_type in varchar2 ) is
select count(*)
from dba_objects
where owner = x_user
and object_type = x_obj_type;
li_num_objects integer;
type table_obj_type is table of dba_objects.object_type%type index by binary_integer;
t_obj_type table_obj_type;
li_counter integer := 1;
li_no_users integer := 0;
li_column_width integer := 8;
ls_header1 varchar2( 255 );
ls_header2 varchar2( 255 );
ls_header3 varchar2( 255 );
ls_out_string varchar2( 255 );
begin
for i_c_object_types in c_object_types loop
t_obj_type( t_obj_type.count+1 ) := i_c_object_types.object_type;
end loop;
ls_header1 := ' ';
ls_header2 := ' ';
ls_header3 := ' ';
for x in 1..t_obj_type.count loop
ls_header1 := ls_header1 || lpad( t_obj_type( x ), li_column_width ) || ' ';
ls_header2 := ls_header2 || lpad( nvl( substr( t_obj_type( x ), li_column_width + 1 ), ' ' ), li_column_width ) || ' ';
ls_header3 := ls_header3 || lpad( rpad( '-', length( t_obj_type( x ) ), '-' ), li_column_width ) || ' ';
end loop;
dbms_output.put_line( ls_header1 );
dbms_output.put_line( ls_header2 );
dbms_output.put_line( ls_header3 );
for i_c_users in c_users loop
ls_out_string := rpad( i_c_users.username, 15 );
li_counter := 0;
for x in 1..t_obj_type.count loop
li_num_objects := 0;
open c_u_objects ( i_c_users.username, t_obj_type( x ) );
fetch c_u_objects into li_num_objects;
close c_u_objects;
li_counter := li_counter + li_num_objects;
if li_num_objects > 0 then
ls_out_string := ls_out_string || lpad( to_char( li_num_objects, '999,999' ), li_column_width ) || ' ';
else
ls_out_string := ls_out_string || lpad( ' .', li_column_width ) || ' ';
end if;
end loop;
if li_counter > 0 or ls_all_users <> 'N' then
li_no_users := li_no_users + 1;
dbms_output.put_line( ls_out_string || lpad( to_char( li_counter, '999,999' ), li_column_width ) );
end if;
end loop;
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'No of users displayed : ' || li_no_users );
end;
/
spool off