Object Info Scripts

SCRIPTS TO SHOW INFORMATION ON DB OBJECTS

Note: Save the script file with .SQL extension - the default extension while saving is .TXT

 
Script Name

audinfo.sql

Purpose Information of audits enabled on database - Statement audits, privilege audits, object audits and default audits. Screen output saved at /tmp/audinfo.lst
Parameters None
Command Line SQL> @audinfo
View Script         Sample Output
 
Script Name

dependinfo.sql

Purpose Information on dependency of objects with respect to the object that is passed in as parameter. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/dependinfo.lst
Parameters
  1. Object Owner (Wild Character % may be used)
  2. Object Name (Wild Character % may be used)
Command Line SQL> @dependinfo example %
View Script         Sample Output
 
Script Name

jobinfo.sql

Purpose Information on jobs scheduled in Oracle database through dbms_job package. Screen output saved at /tmp/jobinfo.lst
Parameters None
Command Line SQL> @jobinfo
View Script         Sample Output
 
Script Name

procinfo.sql

Purpose Shows body of the procedure name that is passed in as parameter. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/procinfo.lst
Parameters
1. Program Owner (Wild character % may be used)
2. Program Name (Wild character % may be used)
Command Line SQL> @procinfo % sal%
View Script         Sample Output
 
Script Name

rbsinfo.sql

Purpose Information on Rollback segments and status. Screen output saved at /tmp/rbsinfo.lst
Parameters
None
Command Line SQL> @rbsinfo
View Script         Sample Output
 
Script Name

roleinfo.sql

Purpose Information of Roles, other roles granted to this role, system privileges, object privileges, column privileges, users/roles assigned this role. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/roleinfo.lst
Parameters
1. Role Name (Wild character % may be used)
Command Line SQL> @roleinfo updt_sc%
View Script         Sample Output
 
Script Name

tabinfo.sql

Purpose Information on Tables - storage, rows, columns, indexes, primary keys, foreign keys, check constraints, triggers, users/roles granted explicit privilege, objects depended on this table. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/tabinfo.lst
Parameters 1. Table Owner (Wild character % may be used)
2. Table Name (Wild character % may be used)
Command Line SQL> @tabinfo scott emp
View Script         Sample Output
Updates Stephen Rea has made some modifications to tabinfo.sql, including prompting for an optional file name to output the results to, and increasing the dbms_output size to 1,000,000 bytes. He has also created tabinfobig.sql which uses a temporary table to store the results from the inline procedure and, then, selects the lines out into the listing file, thus, getting around the 1,000,000-byte limit for mapping large sets of tables (his SCT Banner application has over 1,200 tables, and couldn't be mapped in it's entirety with the 1,000,000-byte limit). These two scripts have been posted to his web site http://www.uaex.edu/srea. Thank you Stephen!
 
Script Name

tabspace.sql

Purpose Information on table allocated, used & free space, including the indexes related to the table name passed in as parameter. Screen output saved at /tmp/tabspace.lst. This pl/sql program uses dbms_space, a supplied package from Oracle.
Parameters 1. Table Owner (Wild character % may be used)
2. Table Name (Wild character % may be used)
Command Line SQL> @tabspace AA A%
View Script         Sample Output
 
Script Name

tabstore.sql

Purpose Detailed information on table storage parameters and actual storage, including the indexes related to the table name, which is passed in as parameter. Screen output saved at /tmp/tabstore.lst
Parameters 1. Table Owner (Wild character % may be used)
2. Table Name (Wild character % may be used)
Command Line SQL> @tabstore scott emp
View Script         Sample Output
 
Script Name

triginfo.sql

Purpose Body of Trigger (name passed in as parameter). Wild characters may be used (%) in the parameter list.Screen output saved at /tmp/triginfo.lst
Parameters 1. Trigger Owner (Wild character % may be used)
2. Trigger Name (Wild character % may be used)
Command Line SQL> @triginfo scott %
View Script         Sample Output
 
Script Name

tsinfo.sql

Purpose Information on Tablespaces - size, freespace, datafiles. Wild characters may be used (%) in the parameter list.Screen output saved at /tmp/tsinfo.lst
Parameters 1. Tablespace Name (Wild character % may be used)
Command Line SQL> @tsinfo USERS
View Script         Sample Output
 
Script Name

userinfo.sql

Purpose Information on Schema owners - tablespaces, profile, roles granted, system privileges granted, object and column privileges granted. Wild characters may be used (%) in the parameter list. Screen output saved at /tmp/userinfo.lst
Parameters 1. User/Schema Name (Wild character % may be used)
Command Line SQL> @userinfo scott
View Script         Sample Output
 

DOWNLOAD ALL OBJECT INFO SCRIPTS

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.