Utility Scripts


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

Script Name


Purpose To become another user without knowing his/her password. Need alter user privilege.
Parameters 1. User Name
Command Line SQL> @buser username
View Script
Script Name


Purpose Compile invalid stored database objects such as view, procedure, package, function and trigger. A list of invalid objects after recompiling is also provided.
Parameters None
Command Line SQL> @cinvobj
View Script
Script Name


Purpose Estimate the size of an index before you build an index. This script may be useful if you have a large table and to know how much temporary space required (aprox 110% of the index size) to create the index or how much freespace you should have to create the index. The calculation is based on the formula specified in the Oracle Server Administrators Guide.
Parameters None - The table owner, table name and column names are specified inside the script. Change these values accordingly before running the script.
Command Line SQL> @ind_espace
View Script         Sample Output
Script Name


Purpose To show a comaprison report on how much space you save by rebuilding the index. If your table is very large and you do lot of deletes and inserts, there may be a lot of space unused in your blocks. Run this script to see how much storage space you save, rebuilding index improves the index performance also.
Parameters 1. Table Owner - Wild card may be specified
2. Table Name - Wild card may be specified
Command Line SQL> @ind_rspace biju salary1
View Script         Sample Output
Script Name


Purpose To make the PCTINCREASE value of tablespaces and tables to 0.
Parameters None
Command Line SQL> @pct0
View Script
Script Name


Purpose Shrink the rollback segments to their initial (or optimal) size.
Parameters None
Command Line SQL> @rbshrink
View Script
Script Name


Purpose Stored procedure to unload table data to a flat file. Appropriate where conditions may be added to limit the rows. Creates appropriate SQL*Loader control file also to upload data.
Parameters 1. Table name, table owner, index name (optional - used as a hint; can be changed to a WHERE condition also)
Command Line SQL> exec table_unload ('MY_PERS_TABLE', 'BIJU')
View Script   [Here is how you use in a shell script to schedule this as a cronjob, etc.]
Script Name


Purpose To trace multiple oracle sessions of same user. The script displays the session information such as username, o/s username, sid, serial#, unix process id and program name. The trace files can be easily associated with the unix process id number, i.e., the trace file name would be ora_UNIXID.trc.
Parameters 1. Username (Wild character may be used)
Command Line SQL> @tsess B%
View Script       Sample Output
Script Name


Purpose PL/SQL stored procedure to use a specific rollback segment in a pl/sql program. This procedure can be called in any pl/sql program with the rollback segment as a parameter. This procedure turns ON the rbs, if it is offline. The user who calls this procedure should have alter rollback segment privilege, since the procedure makes the RBS online if it is offline.
Parameters 1. RBS Name
Command Line SQL> execute use_rbs ('RBSLARGE');
View Script         Sample Usage


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.