HANDY UTILITY SCRIPTS
Note: Save the script file with .SQL extension - the
default extension while saving is .TXT
| Script Name |
buser.sql
|
| 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 |
cinvobj.sql
|
| 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 |
ind_espace.sql
|
| 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 |
ind_rspace.sql
|
| 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 |
pct0.sql
|
| Purpose |
To make the PCTINCREASE value of tablespaces and tables to 0. |
| Parameters |
None |
| Command Line |
SQL> @pct0 |
| View |
Script |
| Script Name |
rbshrink.sql
|
| Purpose |
Shrink the rollback segments to their initial (or optimal)
size. |
| Parameters |
None |
| Command Line |
SQL> @rbshrink |
| View |
Script |
| Script Name |
tabunload.sql
|
| 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 |
tsess.sql
|
| 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 |
userbs.sql
|
| 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 |
DOWNLOAD ALL UTILITY 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.
|
|
