Useful Scripts and Sample Programs

$Date: 29-Mar-2000 $
$Revision: 1.50 $
$Author: Frank Naudé $

Topics

WARNING: Make sure you customized and test all scripts to suite your needs and environment before using them.

1. Sample SQL matrix report

2. Lookup Oracle error messages

3. Display Database version, installed options and port string

4. "Who am I" script

5. Pass application info through to the Oracle RDBMS

6. SQL*Plus Help script

7. Test for Leap Years

8. Update/ delete from a huge table with intermittent commits

9. Hex to decimal, and decimal to hex conversion functions

10. Fetch Long column values piece-wise from PL/SQL

11. Random Number Generator SQL package

12. Count the number of rows for ALL tables in the current schema

13. Demonstrate Java stored procedures

14. Send e-mail messages from PL/SQL

1. Switch from one database user to another without password

2. Dynamically ZIP large process trace files

3. Tabular display of redo-log archiving history (logs/hour)

4. Demonstrate database triggers

5. Log all database errors to a table

6. Limit resources using the Database Resource Manager

1. List foreign keys to and from a given table

2. Script to identify everything to do with a table (Includes Triggers and Constraints)

3. Compile invalid database objects in a schema

4. Compile all invalid database objects

5. Compare indexes on two databases and list the differences

6. Re-create all non-system indexes

7. Re-build all non-system indexes on-line

8. Drop a column from a table

1. List segments that can not extend

2. Show database growth in Meg per month for the last year

3. Oracle segment sizing recommendations

4. List segments with more than 200 Meg of free DB Blocks

5. List tables with high water mark not equal to used blocks

6. Check index fragmentation status for a schema

7. List objects in the SYSTEM tablespace that doesn't belong to SYS or SYSTEM

1. Explain SQL execution plan

2. List analyzed tables with not-analyzed indexes

3. List all indexes for a given table

4. Analyze all table and index partitions individually

1. Display database locks and latched (with tables names, etc)

2. List available INIT.ORA parms

3. List unsupported INIT.ORA parms

4. List active database transactions

5. Reports free memory available in the SGA

1. Check for tablespaces in backup mode and take them out of backup mode

2. Take database data files out of backup mode

3. Monitor running RMAN Backups

4. List completed RMAN Backups

1. List Developer/2000 Applications

2. List table primary and foreign key references

3. List column descriptions per table

1. Inserts or retrieves a BLOB from an Oracle database

2. Oracle Log Switch Analyzer

3. Oracle Alert Log Monitor

4. Load Oracle NameServer from TNSNAMES.ORA file

1. Wrapper for Oracle SQL*Plus to give you command editing and history

2. Security fix for problem with arguments showing up in Unix 'ps -ef' output

3. Execute operating systems commands from PL/SQL

1. SQL*Plus replacement shell: Scroll through command history!!! (ZIP file)

2. An interactive ORAENV script

3. Check if a set of databases is running

4. Monitor if a Web Server is running or not

5. Monitor if an Oracle Names Server is functioning

6. Wait until a database is available

7. List Installed Oracle Products

8. Unix sed script to search and replace a string in all files in a directory

9. Check if all entries in the TNSNAMES.ORA file is valid

10. Download a file from a Web server (eg. latest TNSNAMES.ORA)

11. Export database directly to tape with label

12. Parameter driven script to rebuild indexes intelligently

13. Recatalog deleted archived log files with RMAN

• Tales of the Scripts

• The OracleResourceStop Script Archive

• Solv-IT, Inc's Oracle Database Scripts

• SQL Scripts no Oracle DBA should be without

• Mark Lang's Script collection

• OraMag's Code Depot

• Dieter Oberkofler's DBA Scripts

• Rick's Scripts

• Biju's Oracle tips and scripts page

• Steve Rea's Oracle Tips, Tricks, and Scripts

• OracleAlley: Collection of simple & beautiful scripts for the Oracle DBA

• Nyquest Consulting's Scripts and Config files