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 opde 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', 'OPDE') |
| 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 |
| Script Name | monitor_database.ksh |
| Purpose | It is a Shell script which will connect all the remote databases through sqlnet and tnsnames.This scripts will also check the database is up and running or not.Based on that it will insert all the tablespace free size,used size available size in the sybase database table.You have to change the sybase insert command to Oracle for Oracle table. |
| Parameters | None |
| Command Line | monitor_database.ksh |
| View | Script |