Application inventory script

This Oracle script will retrieve the number of rows, objects and their definitions, and descriptions of schema objects.

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.

This was first published in February 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close