Oracle User Object Summary

prompt SYNONYM, CLUSTER, CONSUMER GROUP, INDEX PARTITION, TABLE PARTITION, LIBRARY, QUEUE
prompt
accept x_exclude prompt "Exclude above types ? : "
accept x_sys_tem prompt "Include SYS/SYSTEM ? : "
accept x_all_users prompt "Include users without objects ? : "

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;
/