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 |
- Object Owner (Wild Character % may be used)
- 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
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.
|
|
