Oracle FAQ
SQL
General SQL Scripts

PL/SQL
General PL/SQL Scripts

DBA Scripts
General DBA Scripts

Object Management
Object Management Scripts

Space Management
Space Management Scripts

SQL Tuning
SQL Performance Tuning Scripts

DB Security
Security Auditing Scripts

DB Tuning
Database Performance Tuning Scripts

Backup and Recovery
Backup and Recovery Scripts

 Unix Shell
Unix Shell Scripts

OS/390 (MVS) JCL
OS/390 (MVS) JCL Scripts

Add scripts
Add your own scripts

Other Sites
Other sites with script collections


General SQL Scripts
  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. Select the Nth highest value from a table

  6. Select the Nth lowest value from a table

  7. Demonstrate default column values

  8. Display table and column comments

  9. Pass application info through to the Oracle RDBMS

  10. SQL*Plus Help script

  11. Test for Leap Years

  12. Spell out numbers to words

  13. Demonstrate simple encoding and decoding of messages

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

  15. Demonstrate Oracle database types and object tables

  16. Demonstrate VARRAY database types

  17. Demonstrate Oracle temporary tables

  18. Convert LONG datatypes to LOBs


General PL/SQL: Scripts
  1. Update/ delete from a huge table with intermittent commits

  2. Select records from a cursor into PL/SQL table

  3. Pass result sets (REF CURSOR) between procedures and functions

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

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

  6. Print the ASCII table

  7. Fetch LOB column values piece-wise from PL/SQL

  8. Fetch LONG column values piece-wise from PL/SQL

  9. Random number/ string generator package

  10. Demonstrate writing to a file using the UTL_FILE package

  11. Map an external file to a database view

  12. Demonstrate Java stored procedures

  13. FTP Client (GET and PUT files from PL/SQL)

  14. Send e-mail messages from PL/SQL

  15. Send e-mail messages from PL/SQL with MIME attachments

  16. Access Internet Web pages from PL/SQL

  17. Track DLL changes (create, drop, alter) within a schema


General DBA Scripts
  1. Show database uptime in days and hours

  2. Create database user like an exiting user (with exact privs)

  3. Switch from one database user to another without password (su.sql)

  4. Dynamically ZIP large process trace files

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

  6. List control file structures with usage limits

  7. Log all database errors to a table

  8. Demonstrate database triggers

  9. Limit resources using the Database Resource Manager

  10. Log Miner - extract undo statements from log files

  11. Database cursor usage (open_cursors parameter)


Object Management Scripts
  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. Copy table from one database to another

  9. Drop a column from a table

  10. Reconstruct DDL for tables

  11. Reconstruct DDL for indexes

  12. Reconstruct DDL for snapshot logs

  13. Reconstruct DDL for triggers


Space Management Scripts
  1. Show used/free space per tablespace

  2. Show used/free space per segment type

  3. Save summary of database space history over time

  4. List segments that can not extend (tablespace full)

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

  6. Oracle segment sizing recommendations

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

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

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

  10. Check index fragmentation status for a schema


SQL Performance Tuning Scripts
  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


Database Security and Security Auditing Scripts
  1. List database auditing information

  2. Try to connect to well known database users

  3. Database users with deadly system privileges assigned to them

  4. Database users with deadly roles assigned to them

  5. Security related database initialization parameters and password file users

  6. List security related profile information

  7. List Unix OS users that can startup, shutdown and admin Databases


Database Performance Tuning Scripts
  1. Measure the Buffer Cache Hit Ratio

  2. Display Database SGA Statistics

  3. Reports free memory available in the SGA

  4. List available INIT.ORA parms

  5. List unsupported INIT.ORA parms

  6. List active database transactions

  7. Rollback segment statistics

  8. Display database sessions using rollback segments

  9. Lookup database details for a given Unix process id

  10. Display database locks and latches (with tables names, etc)

  11. Another lock monitor script

  12. Display and release DBMS_LOCK locks


Backup and Recovery Scripts
  1. Simple on-line backup script (put tablespaces in backup mode and copy out)

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

  3. Take database data files out of backup mode

  4. List RMAN Backups registered in RMAN catalog database

  5. List completed RMAN Backups (data from control file)

  6. Monitor running RMAN Backups

  7. Demonstrate Oracle 8i transportable tablespaces


Unix Shell Scripts
  1. Create a DB creation scripts from an running instance

  2. Perform on-line database backup

  3. Export database directly to tape with label

  4. Copy tables in parallel between databases (intelligent scheduler)

  5. Delete RMAN backupsets older than a specified number of days

  6. Recatalog deleted archived log files with RMAN

  7. Manage Oracle trace files (delete old/ send mail for new)

  8. Maintain a daily cycle of Oracle alert log, trace and SQL*Net files

  9. List Installed Oracle Products

  10. An interactive ORAENV script

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

  12. Check if a set of databases is running

  13. Wait until a database is available

  14. Monitor if an Oracle OID (LDAP) Server is functioning

  15. Monitor if an Oracle Names Server is functioning

  16. Swap (roll) and initialise the listener's .log files

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

  18. Monitor if a Web Server is running or not

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

  20. Parameter driven script to rebuild indexes intelligently

  21. Send E-mail from Unix with some file attachments (using mailx)

  22. Send E-mail from Unix with some file attachments (using sendmail)

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

  24. List free space for disk groups (Veritas Volume Manager)

  25. Create new volume for database (Veritas Volume Manager)

  26. Delete a volume (Veritas Volume Manager)

  27. Rename a volume (Veritas Volume Manager)


Other sites with script collections


Back to Oracle FAQ Index