Thursday, August 13, 2015

Oracle : Script provides a report showing the type and count of objects by user

SET ECHO OFF 
set newpage 0 
ttitle center 'Object counts by User' - 
right 'Page:' format 999 sql.pno skip skip 
col ow format a18 heading 'Owner' 
col ta format 999,999 heading 'Tables' 
col ind format 999,999 heading 'Indexes' 
col sy format 999,999 heading 'Synonyms' 
col se format 999,999 heading 'Sequences' 
col ve format 999,999 heading 'Views' 
col clu format 999,999 heading 'Clusters' 
set verify off 
compute sum of ta on report 
compute sum of ow on report 
compute sum of sy on report 
compute sum of se on report 
compute sum of ind on report 
compute sum of ve on report 
compute sum of clu on report 
break on report 
spool tfsobnum.lst 
set feedback off 
start time 
set feedback 6 
set heading on 
select owner ow, 
       sum(decode(object_type,'TABLE',1,0)) ta , 
       sum(decode(object_type,'INDEX',1,0)) ind , 
       sum(decode(object_type,'SYNONYM',1,0)) sy , 
       sum(decode(object_type,'SEQUENCE',1,0)) se , 
       sum(decode(object_type,'VIEW',1,0)) ve , 
       sum(decode(object_type,'CLUSTER',1,0)) clu 
from dba_objects 
group by owner 

col dbl format 999,999 heading 'Database|Links' 
col pkg format 999,999 heading 'Packages' 
col pkb format 999,999 heading 'Package|Bodies' 
col pro format 999,999 heading 'Procedures' 
col ve format 999,999 heading 'Views' 
col clu format 999,999 heading 'Clusters' 
set verify off 
compute sum of dbl on report 
compute sum of ow on report 
compute sum of pkg on report 
compute sum of pkb on report 
compute sum of pro on report 
compute sum of ve on report 
compute sum of clu on report 
break on report 
select owner ow, 
       sum(decode(object_type,'DATABASE LINK',1,0)) dbl , 
       sum(decode(object_type,'PACKAGE',1,0)) pkg , 
       sum(decode(object_type,'PACKAGE BODY',1,0)) pkb , 
       sum(decode(object_type,'PROCEDURE',1,0)) pro  
from dba_objects 
group by owner 

Prompt 
prompt End of Report 
spool off 
btitle off 
ttitle off 
clear breaks 
clear columns 
clear computes 
set verify on 

No comments:

Post a Comment