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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.
Start the conversation
0 comments