Application inventory script

• Dixie Hill
• Published: 26 Feb 2002

This script will dynamically get the number of rows, objects and their definitions, and descriptions of schema objects. Good for review after a database refresh. I run this is a Windows/NT environment from SQL*Plus.

               
Prompt 'This script is hard coded with c:\temp and will write there' 
set pagesize 0 
spool c:\temp\count_rows.sql 
set termout off 
set echo off 
select 'select count(*) from '|| owner ||'.'|| object_name||';' from dba_objects 
where object_type = 'TABLE' and owner in ('DACS','FOOD') 
order by 1; 
spool off 
spool c:\temp\obj_rep.lis 
-- set echo off 
set termout on 
PROMPT '###################THE # OF OBJECTS BY OBJECT TYPE##########' 
select decode(grouping(object_type),0,to_char(object_type),'Total') 
object_type, count(*) from dba_objects 
where owner in ('DACS','FOOD') 
group by rollup(object_type); 
Prompt '########################THE # OF ROWS PER TABLE##############' 
set echo on 
@c:\temp\count_rows.sql 
set echo off 
PROMPT '##################### A LISTING OF THE OBJECT BY NAME AND TYPE###' 
select substr(owner,1,5), substr(object_name,1,30) Name, 
 substr(object_type,1,10) Type, status 
 from dba_objects 
where owner in ('DACS','FOOD') 
order by 1,3; 
spool off; 

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.