Scripts

Below are some free Oracle Scripts and code examples you can download:

You can have your scripts and code examples published here. Just upload them and include them on this page. However, please ensure all code is properly formated.

General SQL Scripts

• Sample SQL matrix report
• Lookup Oracle error messages
• Display Database version, installed options and port string
• "Who am I" script
• Select the Nth highest value from a table
• Select the Nth lowest value from a table
• Demonstrate default column values
• Display table and column comments
• Pass application info through to the Oracle RDBMS
• SQL*Plus Help script
• Test for Leap Years
• Spell out numbers to words
• Demonstrate simple encoding and decoding of messages
• Count the number of rows for ALL tables in current schema
• Demonstrate Oracle database types and object tables
• Demonstrate VARRAY database types
• Demonstrate Oracle temporary tables
• Convert LONG data types to LOBs
• Delete duplicate values from a table

General PL/SQL Scripts

• Update/ delete from a huge table with intermittent commits
• Simple program to demonstrate BULK COLLECT and BULK BIND operations
• Profile PL/SQL code for execution statistics
• Select records from a cursor into PL/SQL table
• Password encrypt/decrypt using DBMS Obfuscation Toolkit
• Pass result sets (REF CURSOR) between procedures and functions
• Convert between different numbering systems (binary, octal, decimal and hex)
• Random number/ string generator package
• Function to test for Leap Years
• Print the ASCII table
• Recursive algorithms to calculate Fibonacci and Factorials
• Fetch LOB column values piece-wise from PL/SQL
• Upload and save binary files (like pictures, documents, etc) to/from the DB
• Fetch LONG column values piece-wise from PL/SQL
• Demonstrate writing to a file using the UTL_FILE package
• Map an external file to a database view
• Demonstrate Dynamic SQL
• Demonstrate Java stored procedures
• Execute Operating System commands from PL/SQL (Java call)
• FTP Client (GET and PUT files from PL/SQL)
• Send e-mail messages from PL/SQL (using UTL_TCP)
• Send e-mail messages from PL/SQL with MIME attachments
• Same as above, but implemented using UTL_SMTP
• Mailmerge: Merge data from the table/view data sources into a custom template
• Read an Internet Web pages from PL/SQL
• Track DDL changes (create, drop, alter) within a schema
• Count the number of rows in ALL tables for the current schema
• List tables from schema with more than X rows
• Replace all occurrences of a substring with another substring
• Spell out numbers to words (handy for cheque printing)
• Print cheque amounts in Indian Style
• NYSIIS function (an improvement on SoundeX)
• Converts a string of text into separate soundex values
• Package to generate HTML-type documentation for Oracle objects

General DBA Scripts

• Show database uptime in days and hours
• Create database user like an exiting user (with exact privs)
• Switch from one database user to another without password (su.sql)
• Dynamically ZIP large process trace files
• Tabular display of redo-log archiving history (logs/hour)
• List control file structures with usage limits
• Log all database errors to a table
• Demonstrate database and schema level triggers
• Limit resources using the Database Resource Manager
• Log Miner - extract undo statements from log files
• Database cursor usage (open_cursors parameter)
• On-line table reorganizaton using the DBMS_REDEFINITION package
• Create a Primary key column on a table were this is not yet available

Object Management Scripts

• List foreign keys to and from a given table
• Script to identify everything to do with a table (Includes Triggers and Constraints)
• Compile invalid database objects in a schema
• Compile all invalid database objects
• Compare indexes on two databases and list the differences
• Re-create all non-system indexes
• Re-build all non-system indexes on-line
• Copy table from one database to another
• Drop a column from a table
• Sets an existing sequence to a value of choice without dropping it
• Reconstruct DDL for tables
• Reconstruct DDL for indexes
• Reconstruct DDL for snapshot logs
• Reconstruct DDL for triggers

Space Management Scripts

• Show used/free space per tablespace
• Show used/free space per datafile
• Show used/free space per segment type
• Save summary of database space history over time
• List segments that can not extend (tablespace full)
• List objects in the SYSTEM tablespace that doesn't belong to SYS or SYSTEM
• Oracle segment sizing recommendations
• Show database growth in Meg per month for the last year
• List segments with more than 200 Meg of free DB Blocks
• List tables with high water mark not equal to used blocks
• Check whether a tablespace is fragmented and show fragmentation type
• Check index fragmentation status for a schema

SQL Performance Tuning Scripts

• Explain SQL execution plan
• List analyzed tables with not-analyzed indexes
• List all indexes for a given table
• Analyze all table and index partitions individually

Database Security and Security Auditing Scripts

• Block users from using tools like TOAD, SQL Navigator and PLSQL Developer on production databases (DB login trigger)
• Audit User Logins and start traces for selected users (User Login Trigger)
• List database auditing information
• Try to connect to well known database users
• Database users with deadly system privileges assigned to them
• Database users with deadly roles assigned to them
• Security related database initialization parameters and password file users
• List security related profile information
• List Unix OS users that can startup, shutdown and admin Databases

Database Performance Tuning Scripts

• Measure the Buffer Cache Hit Ratio
• Display Database SGA Statistics
• Reports free memory available in the SGA
• List available INIT.ORA parms
• List unsupported INIT.ORA parms
• List active database transactions
• Rollback segment statistics
• Display database sessions using rollback segments
• Lookup database details for a given Unix process id
• Display database locks and latches (with tables names, etc)
• Another lock monitor script
• Display and release DBMS_LOCK locks
• OraYAPS - Yet another Oracle Performance Tuning script

Backup and Recovery Scripts

• Simple on-line backup script (user managed - put tablespaces in backup mode and copy out)
• Check for tablespaces in backup mode and take them out of backup mode
• Take database data files out of backup mode
• List RMAN Backups registered in RMAN catalog database
• Monitor running RMAN Backups
• Summary of datafiles and archlogs backed up with RMAN over past 24 hours
• List completed RMAN Backups for past 24 hours
• Jack van Zanen's RMAN Scripts
• Demonstrate transportable tablespaces (8i or higher)
• Monitor RMAN restore

Oracle XMLDB and general XML Scripts

• A method for logging the changes to an xml table

Oracle Advanced Replication Scripts

• Setup users, DB Links and schedules for Oracle Advanced Replication
• Define replication groups with replication objects (simple)
• Define replication groups with replication objects (more advanced)
• Monitor replication status, sites and groups
• Show transaction details - including old and new column values
• Apply/ Delete Errors
• Remove replication support from database

Oracle (interMedia) Text Scripts

• Install Oracle Context on a database
• Demo the creation and maintenance of text indexes
• Load operating system files into Oracle database tables
• Demo thesaurus functionalities

Designer Repository Scripts

• List Developer/2000 Applications
• List table primary and foreign key references
• List column descriptions per table
• List Developer Repository Owners

Oracle Developer Forms and Reports

• Create a report of a Forms item and block properties
• Create MS-Word document from Developer Forms
• Tetris for Developer 6i

Oracle Applications Scripts

• Print Oracle Apps versions
• Display status of all Concurrent Managers

Perl Scripts

Perl (Practical Extraction and Report Language) scripts:

• Extract Oracle HOME and path from Windows Registry
• OERR command for Windows
• Inserts or retrieves a BLOB from an Oracle database
• Oracle Log Switch Analyzer
• Simple script for checking log switches
• Oracle Alert Log Monitor
• Load Oracle NameServer from TNSNAMES.ORA file
• Script to migrate data from FileMaker Pro to Oracle

AWK Scripts

• Indexfile beautifier (reformats indexfiles produced by 'imp indexfile=')
• Extract SQL Statements from export dump file
• Convert flat files to CSV format

Pro*C Programs

• Select records from a database
• Fetch multiple records from database
• Execute a database procedure with known IN OUT arguments
• Connect to more than one database to do work
• Describe a database procedure and print its arguments

SQLJ Programs

• Select records from a database
• Fetch multiple records from database
• Connect to more than one database to do work
• Execute a database procedure with known IN OUT arguments
• Execute DML against a database
• Fetch data from a REF CURSOR

C and C++ Programs

• Extracts archive log header information
• Wrapper for Oracle SQL*Plus to give you command editing and history
• Security fix for problem with arguments showing up in Unix 'ps -ef' output
• Execute operating systems commands from PL/SQL (external procedure call)

MS-Windows CMD and DOS Batch Scripts

• Extracts data from ODBC sources (including Excel and Access files) to different formats
• Registry update to add "SQL*Plus here" to right click menus
• Show machine info and Oracle environment variables
• Start an Oracle database
• Stop an Oracle database
• Check if database is up and running
• Start and stop the Oracle SQL*Net Listener
• Start and stop Oracle related Windows Services
• Run SQL*Plus script from DOS batch file
• Export contents of an Oracle database
• Schedule a job to execute at regular intervals
• OCopy.bat - Hot backup of oracle databases
• OraInit.bat - Start/Stop oracle service

Visual Basic

• Switch ALERT.LOG and LISTENER.LOG files on Windows Systems
• Schedule Archivelog backup when less than X Meg free and more than Y files to backup

Unix Shell Scripts

• Run the same SQL on multiple DBs from a centralized server
• Print alertlog messages with date/time stamp on the same line
• Manage Oracle trace files (delete old/ send mail for new)
• Maintain a daily cycle of Oracle alert log, trace and SQL*Net files
• Generic script to date, compress and delete old log files
• Generate "DB Creation Scripts" from a running instance
• An interactive ORAENV script
• Perform an on-line database backup
• Export database directly to tape with label
• Copy tables in parallel between databases (intelligent scheduler)
• Automate the generation of STATSPACK reports in a logical manner
• Delete RMAN backupsets older than a specified number of days
• Recatalog deleted archived log files with RMAN
• List Installed Oracle Products
• SQL*Plus replacement shell: Scroll through command history!!! (ZIP file)
• Check if a set of databases is running
• Check if databases are available by connecting via listener
• Wait until a database is available
• Monitor if an Oracle OID (LDAP) Server is functioning
• Monitor if an Oracle Names Server is functioning
• Swap (roll) and initialise the listener's .log files
• Check if all entries in the TNSNAMES.ORA file is valid
• Monitor if a Web Server is running or not
• Download a file from a Web server (eg. latest TNSNAMES.ORA)
• Parameter driven script to rebuild indexes intelligently
• Send E-mail from Unix with some file attachments (using mailx)
• Send E-mail from Unix with some file attachments (using sendmail)
• Unix sed script to search and replace a string in all files in a directory
• Map C-language sructure to binary data file
• List free space for disk groups (Veritas Volume Manager)
• Create new raw volume for a database file (Veritas Volume Manager)
• Delete a raw volume (Veritas Volume Manager)
• Rename a raw volume (Veritas Volume Manager)

Other sites with script collections

• OraMag's Code Depot
• Material Dreams' DBA Scripts
• Biju's Oracle tips and scripts page
• Steve Rea's Oracle Tips, Tricks, and Scripts

Warning

Some of these scripts may be dangerous and can potentially damage or destroy your database or system. Make sure you understand, customize and test all scripts to suit your needs and environment before using them!