dbaXchange.com : Script Library

Main Menu
- Database Resources
- Oracle Scripts
- White Papers
- Oracle Sites
- Oracle Books
- Oracle Jobs
- Under Review
- Miscellaneous Stuff
- Non-Oracle Stuff
- Submit Articles
- Feedback

Oracle's technology Sites

http://technet.oracle.com

http://metalink.oracle.com

http://asktom.oracle.com/

http://www.oramag.com

Oracle Scripts

This page has numerous scripts that can used on an everyday basis. They are mostly monitoring scripts along with a few utility scripts. These are a collection of scripts collected from various sources including oracle's metalink along with some that we've come up with.

Disclaimer: No responsibility can be accepted for any loss of data, performance problems, or any other problems that you might experience, as a result of the application of the information or scripts presented at dbaXchange.com. You are strongly advised to thoroughly test scripts and techniques on a test database, before even considering to apply them to a production environment.

Oracle Scripts :

highdiskreads.sql disksorts.sql buffer_cache.sql redologbuffer.sql rollback.sql	These are quick database health check scripts. They give information on disk IO, disk sorts, buffer cache, redo log and rollback.
rollback_monitor.sql Additional_rollback_monitor.sql	Scripts for monitoring rollback segments.
control_space_used.sql	Control file space usage.
current_events.sql	Gives a list of current events set for the instance.
db_blk_buff_used.sql db_blk_hit_ratio.sql	Scripts for block buffers. The first script gives details on the block buffer usage for DEFAULT, KEEP and RECYCLE buffers while the second script gives details on the hit ration for each of the 3 buffer types.
session_find_mts.sql dispatcher_monitor.sql	Scripts for the multi-treaded server (MTS). The first script helps find the dispatcher and shared server information for a given user while the second script gives details on the dispatcher and queue wait time.
get_cached_plan.sql	Gives details about the cached execution plan for a session's hash value.
get_cached_plan_2.sql (new)	Gives details about the cached execution plan for a supplied session Id (SID) value.
hidden_parms_values.sql	Gives a list of hidden parameter and their values.
latch_details.sql	This is a 3 part script. The first part identifies the latch contention, the second part gives latch children information while the final part identifies the segments that might be contributing to the latch contention.
reporton_locks.sql	Report on locks being held by various sessions on the database.
currentsqlstatement.sql	Details on the SQL statement being executed by a given session.
shared_pool_details.sql	Shared pool monitor script. This is a 5 part script. Part1: Tracks allocations in the shared pool that cause existing objects in the pool to be aged out. Part2: Listing of existing objects in the Shared Pool with size greated than 10K. Part3: Large pl/sql objects currently loaded in the shared pool and not marked as kept. Part4: Queries with more than 1 version. Part5: Percentage of share pool space used.
session_hit_ratio.sql	Report on session level hit ratio for active sessions.
redo_hitratio.sql redo_generate.sql redo_percent_full.sql	Redo log scripts. Script 1: Details of redo waits Script 2: Level of redo generation Script 3: Percentage full within the current redo log
file_wait.sql	Report on file waits.

A few Utilities :

reverse_eng_schema.sql	Provided by Oracle's metalink, this script can be used for reverse engineering a schema.
get_invalid.sql	This script can be used to generate a list of invalid objects within the database in the order of dependency.
monitor_all_tbs.sql (new)	An improved way to list tablespace whose space is below the user supplied threshold along with percentage of space free within the tablespace. Takes datafile autoextend in to consideration.
reverse_eng_idxbytable.sql	Script for generating index scripts for a supplied table name. Handles function based indexes also.

If you've come across websites with good oracle technical content, email us the details at services@dbaxchange.com and we'll add to the list above.