Oracle ebusiness consultants and dbas

Search Site
About Think Forward
Contact Think Forward
Proactive Low Cost Database Support
Reactive Low Cost Database Pay Per Incident Support
Creative Solutions and Independent Advice from our expert consultants
Oracle High Availability Consultancy Services
Fast Forward Training
Professional Oracle Build Services
Revive your poorly database with an Oracle Health Checkup
Low Cost Oracle Development
Think Forward Home Page

Oracle Certified Professional

E-Mail Us and make cost savings on Oracle databases

info@think-forward.com

Oracle DBA Scripts and Tips

It has come to our attention that an Oracle Development company have copied our scripts without our permission and are using them as part of their own products for which they are charging users a license fee.

As a result of this, we have decided to suspend access to our DBA scripts until further notice and we regret the impact that this decision will inevitably have upon genuine users of the scripts repository.

We are continuing to monitor the situation and would encourage you to check back in the future for developments.

Script Home 8i

Scripts Sorted by Category, Title



  1. Export Table, Index & Partition Statistics ( Category : Analyze )
    SQL Script : This script copies all the statistics captured using the DBMS_STATS package into
    Updated: 31-DEC-02

  2. Gather Table Statistics ( Category : Analyze )
    SQL Script : Dynamically analyzes all tables for an owner
    Updated: 26-SEP-02

  3. Import Table/Index and Partiton Statistics ( Category : Analyze )
    SQL Script : Uses the DBMS_STATS package to import all statistics from a table to the data di
    Updated: 31-DEC-02

  4. Index Statistics ( Category : Analyze )
    SQL Script : This script validates an index and reports upon the index stats table. This scri
    Updated: 28-FEB-02

  5. List Table Statistics ( Category : Analyze )
    SQL Script : Uses the DBMS_STATS package to list the table, index and column statsistics
    Updated: 26-SEP-05

  6. List the Last Analyzed Date ( Category : Analyze )
    SQL Script : Lists the last analyze date for tables, indexes and partitions. This script shou
    Updated: 28-NOV-02

  7. Create Database Resource Manager Plan ( Category : DB Resource Manager )
    SQL Script : This script creates a simple resource manager plan.
    Updated: 30-JUN-02

  8. Database Resource Manager Configuration ( Category : DB Resource Manager )
    SQL Script : This script lists the configuration of the Database Resource Manager
    Updated: 03-APR-02

  9. Drop Database Resource Manager ( Category : DB Resource Manager )
    SQL Script : Drops resource manager plan and any associated subplans
    Updated: 19-JUL-04

  10. Grant Resource Manager Privileges ( Category : DB Resource Manager )
    SQL Script : This script assigns a resource manager plan to users
    Updated: 30-JUL-02

  11. Monitor Database Resource Manager ( Category : DB Resource Manager )
    SQL Script : This script lists the CPU consumed and the Consumer Group a session is using whe
    Updated: 03-APR-02

  12. Standby Database Gap Detection ( Category : General Database Information )
    SQL Script : This script lists a simple method in which to detect archive gaps. IT does need
    Updated: 24-NOV-04

  13. Database DeadLocks ( Category : Monitor Scripts )
    SQL Script : This script lists details of all current database deadlocks on the system, who i
    Updated: 03-NOV-98

  14. MTS Monitor Script ( Category : Monitor Scripts )
    SQL Script : Lists difference aspects of MTS monitoring.
    Updated: 27-MAY-03

  15. Monitor All Database Locks ( Category : Monitor Scripts )
    SQL Script : This script lists details of all current database locks on the system, who is ho
    Updated: 03-NOV-98

  16. Monitor Cursors ( Category : Monitor Scripts )
    SQL Script : This script listsdetails of current open cursors.
    Updated: 02-NOV-98

  17. Monitor Latch Contention ( Category : Monitor Scripts )
    SQL Script : The first statement lists information critical to determining if a database inst
    Updated: 29-MAR-02

  18. Monitor Long Operations ( Category : Monitor Scripts )
    SQL Script : Queries the session details for long running operations such as a Full Table /In
    Updated: 03-JAN-03

  19. Monitor Redo Logs ( Category : Monitor Scripts )
    SQL Script : Simple listing of redo log status and the key system statistics. The key statist
    Updated: 29-MAR-02

  20. Monitor Rollback Segments ( Category : Monitor Scripts )
    SQL Script : This script reports key indicators in determining whats happening with rollback
    Updated: 24-NOV-05

  21. Monitor SQL Area ( Category : Monitor Scripts )
    SQL Script : This script queries the SQL area ordered by the the average cost of the statemen
    Updated: 24-NOV-04

  22. Monitor SQL Text and Locks ( Category : Monitor Scripts )
    SQL Script : This script reports the SQL text of locks currently being held in the database.
    Updated: 18-MAR-02

  23. Monitor Session Activity ( Category : Monitor Scripts )
    SQL Script : Script displays many different session related details for a specific Oracle Sys
    Updated: 01-OCT-02

  24. Monitor User ( Category : Monitor Scripts )
    SQL Script : This script reports session statistics and what queries the current user has ope
    Updated: 01-OCT-02

  25. Redo Log History ( Category : Monitor Scripts )
    SQL Script : Simple listing of redo log switching over time. A useful tip is to change the fo
    Updated: 04-JAN-03

  26. Session Idle Time ( Category : Monitor Scripts )
    SQL Script : Two different ways to monitor idle sessions. This script displays the top 5 long
    Updated: 24-NOV-04

  27. Adjust Sequences ( Category : Object Management )
    SQL Script : This script will drop and re-create sequences based upon the value of a sequence
    Updated: 04-AUG-98

  28. Count Partition Rows ( Category : Object Management )
    SQL Script : Displays each individual table partition row counts. Also now lists the number
    Updated: 27-SEP-02

  29. Database Link Listing ( Category : Object Management )
    SQL Script : This script lists all database link details, including the password of the remot
    Updated: 01-JUN-98

  30. Disable Foreign Keys ( Category : Object Management )
    SQL Script : Disables all enabled.foreign keys for a given owner.
    Updated: 04-MAR-02

  31. Dynamic Code Public Synonyms ( Category : Object Management )
    SQL Script : This script creates public synonyms for all PACKAGES/PROCEDURES/FUNCTIONS owned
    Updated: 02-MAR-02

  32. Dynamic Execute Granting ( Category : Object Management )
    SQL Script : This script performs dynamic granting of PROCEDURES/FUNCTIONS/PACKAGES to users/
    Updated: 03-MAR-02

  33. Dynamic Granting ( Category : Object Management )
    SQL Script : This script performs dynamic granting of tables,views,sequences of the SELECT, I
    Updated: 05-MAR-02

  34. Dynamic Granting ( Category : Object Management )
    SQL Script : This script performs dynamic granting of tables,views,sequences of the SELECT pr
    Updated: 05-MAR-02

  35. Dynamic Granting ( Category : Object Management )
    SQL Script : This script performs dynamic granting of tables,views,sequences to users/roles/P
    Updated: 05-MAR-02

  36. Dynamic Public Synonyms ( Category : Object Management )
    SQL Script : This script creates public synonyms for all TABLES/VIEWS/SEQUENCES owned by a us
    Updated: 05-MAR-02

  37. Enable Foreign Keys ( Category : Object Management )
    SQL Script : Enables all disabled.foreign keys for a given owner. Check out the partner disab
    Updated: 01-MAR-02

  38. Indexes belonging to a table ( Category : Object Management )
    SQL Script : This script lists index details for a given table and index owner. Everyone shou
    Updated: 04-MAR-02

  39. Object Dependencies ( Category : Object Management )
    SQL Script : This script lists all dependent objects.
    Updated: 24-NOV-04

  40. Partition Details ( Category : Object Management )
    SQL Script : This script lists details of table and index partitions.
    Updated: 28-FEB-01

  41. Rebuild Unusable Indexes ( Category : Object Management )
    SQL Script : This script rebuilds unusable indexes.
    Updated: 24-NOV-04

  42. Table/System Privileges ( Category : Object Management )
    SQL Script : This script all table and system privileges granted to a user. The scripts also
    Updated: 10-JUN-98

  43. Unindexed Foreign Key Listing ( Category : Object Management )
    SQL Script : This script lists tables with foreign keys which are not indexed. Unindexed fore
    Updated: 11-FEB-04

  44. DB Block Buffer ( Category : Performance )
    SQL Script : This script lists the state of all the DB_BLOCK_BUFFERS in the database. This sc
    Updated: 03-JUN-05

  45. Database File Statistics ( Category : Performance )
    SQL Script : Script displays file statistics within the database. This script displays the
    Updated: 02-JUN-05

  46. Database Hit Ratios ( Category : Performance )
    SQL Script : This script calculates the database hit ratio on you database. It also displays
    Updated: 30-MAR-02

  47. Events the System is Waiting on ( Category : Performance )
    SQL Script : Excessive waits on system events can indicate disk I/O bottlenecks, network bott
    Updated: 10-JAN-03

  48. Free Memory for SGA ( Category : Performance )
    SQL Script : This script list the total memory in the shared_pool and the amount of free memo
    Updated: 30-JUN-02

  49. Free Memory for SGA ( Category : Performance )
    SQL Script : This script list the total memory in the shared_pool and the amount of free memo
    Updated: 30-JUL-02

  50. Library Cache ( Category : Performance )
    SQL Script : Library Cache Misses indicate that the Shared Pool is not big enough to hold the
    Updated: 24-NOV-04

  51. List Unpinned Objects in DB Cache ( Category : Performance )
    SQL Script : List details of all packages, procedures, functions that are in the db_object_ca
    Updated: 20-MAR-02

  52. Objects in DB Cache ( Category : Performance )
    SQL Script : List of what objects are current in the DB Cache
    Updated: 02-JUN-05

  53. Open Cursors ( Category : Performance )
    SQL Script : This script reports current open cursors ordered by the the average cost of the
    Updated: 22-MAY-03

  54. Oracle Parallel Server Statistics ( Category : Performance )
    SQL Script : Lists Basic File Pinging and other statistics related to Parallel Server
    Updated: 26-SEP-02

  55. Pin All Packages ( Category : Performance )
    SQL Script : Pin all packages that exist in the database above a minimum size. This script sh
    Updated: 20-MAR-02

  56. Pin All Standard Packages ( Category : Performance )
    SQL Script : Pin all packages standard database packages. This script should be run on databa
    Updated: 20-MAR-04

  57. Recently Accessed Indexes ( Category : Performance )
    SQL Script : When maintaining the database, it is sometimes useful to know which indexes were
    Updated: 14-MAR-02

  58. Session Details ( Category : Performance )
    SQL Script : This script reports for each session in the database the objects accessed by the
    Updated: 20-MAR-02

  59. Session Hit Ratios ( Category : Performance )
    SQL Script : This script lists the database hit ratio for each session
    Updated: 17-JAN-99

  60. Set Session in Trace ( Category : Performance )
    SQL Script : Script displays the set session in trace command, but does not run the command
    Updated: 08-JAN-02

  61. Snapshot Of Database Performance ( Category : Performance )
    SQL Script : This script lists the key database performance indicators for a quick overview o
    Updated: 02-MAR-02

  62. Sort Usage ( Category : Performance )
    SQL Script : Script reports sort statistics
    Updated: 08-JAN-02

  63. Statspack Database Load Profile Script ( Category : Performance )
    SQL Script : Displays all Load Profile statistics for many statspack snapshots in single repo
    Updated: 02-JUN-05

  64. Statspack Historical Analysis Script ( Category : Performance )
    SQL Script : Displays all Wait statistics for many statspack snapshots in single report. T
    Updated: 02-JUN-05

  65. Statspack Historical File statistics Script ( Category : Performance )
    SQL Script : Displays all File statistics for many statspack snapshots in single report. T
    Updated: 02-JUN-05

  66. Statspack Historical Memory Script ( Category : Performance )
    SQL Script : Displays all Memory statistics for many statspack snapshots in single report.
    Updated: 02-JUN-05

  67. TOP CPU Users ( Category : Performance )
    SQL Script : Script displays details so the session which have used the most CPU. Needs the I
    Updated: 08-JAN-02

  68. TOP Consistent Gets ( Category : Performance )
    SQL Script : Script displays the top 10 consistent gets within the database - a good indicato
    Updated: 08-JAN-02

  69. TOP Cursors ( Category : Performance )
    SQL Script : Script displays details of the sessions holding open the most cursors. It also a
    Updated: 08-JAN-01

  70. TOP PGA Users ( Category : Performance )
    SQL Script : Scripts displays detail so the sesion which are using the most PGA (Program Glob
    Updated: 04-OCT-02

  71. TOP Physical Read Users ( Category : Performance )
    SQL Script : Script displays details so the session which have issued the most physical read
    Updated: 10-OCT-02

  72. TOP UGA Users ( Category : Performance )
    SQL Script : Scripts displays detail so the sesion which are using the most UGA (User Global
    Updated: 04-OCT-02

  73. Tables accessed Recently By Full Scan ( Category : Performance )
    SQL Script : Lists tables that have been recently accessed by a full table scan. This script
    Updated: 14-MAR-02

  74. Cold Database Backup ( Category : Platform Specific UNIX Scripts )
    Shell Script : Retreives a list of all database files from the database whilst still running an
    Updated: 24-NOV-04

  75. Create Database Template ( Category : Platform Specific UNIX Scripts )
    Shell Script : Creates a database. You will need to edit this and enter you system tablespace d
    Updated: 18-APR-02

  76. Database Shutdown ( Category : Platform Specific UNIX Scripts )
    Shell Script : Simple database shutdown script. Gets the ORACLE_HOME from /etc/oratab. This scr
    Updated: 18-APR-02

  77. Database Size ( Category : Platform Specific UNIX Scripts )
    Shell Script : Displays physical size off all databases on a UNIX Machine.
    Updated: 03-APR-02

  78. Database Startup Script ( Category : Platform Specific UNIX Scripts )
    Shell Script : Simple database startup script. Gets the ORACLE_HOME from /etc/oratab. This scri
    Updated: 18-APR-04

  79. Full Database Export ( Category : Platform Specific UNIX Scripts )
    Shell Script : Full database export and compress using named pipes. Use the full database impor
    Updated: 29-JUL-98

  80. Full Database Import ( Category : Platform Specific UNIX Scripts )
    Shell Script : Full database import and uncompress using named pipes.You will need this if you
    Updated: 24-MAY-99

  81. Hot Database Backup ( Category : Platform Specific UNIX Scripts )
    Shell Script : Generates SQL script put the tablespace in backup mode and to do a unix copy to
    Updated: 13-SEP-02

  82. Set Database ARCHVELOG Mode ( Category : Platform Specific UNIX Scripts )
    Shell Script : Shut database down and sets the database in Archive Log Mode.
    Updated: 18-APR-02

  83. Show Installed Oracle Version ( Category : Platform Specific UNIX Scripts )
    Shell Script : This script will list out all Oracle products installed by the last Oracle insta
    Updated: 08-OCT-02

  84. Standby Database Gap Detection Script. ( Category : Platform Specific UNIX Scripts )
    Shell Script : Detects if a standby database is up to date. Send an OEM alert is not. Script
    Updated: 02-JUN-05

  85. Monitor RMAN ( Category : RMAN )
    SQL Script : Script displays active RMAN sessions within the database
    Updated: 08-JAN-02

  86. RMAN Archivelog Backup ( Category : RMAN )
    RMAN Script : RMAN Archivelog Backup & Delete
    Updated: 24-NOV-04

  87. RMAN Full Backup ( Category : RMAN )
    Shell Script : This script assumes that you have the rmanfull script and have renamed it {ORACL
    Updated: 24-NOV-04

  88. RMAN Full Backup ( Category : RMAN )
    RMAN Script : Full Database backup script using RMAN
    Updated: 24-NOV-04

  89. RMAN Incomplete Recovery ( Category : RMAN )
    RMAN Script : RMAN Script to Recover to a defined log sequence number. This script will per
    Updated: 24-NOV-04

  90. RMAN Incremental Backup ( Category : RMAN )
    RMAN Script : Incremental Database backup using RMAN
    Updated: 24-NOV-04

  91. RMAN Incremental Backup ( Category : RMAN )
    Shell Script : This script assumes that you have the rmanincr script and have renamed it {ORACL
    Updated: 24-NOV-04

  92. RMAN Point in Time Recovery ( Category : RMAN )
    RMAN Script : RMAN Script to Recover until point in time. This script will perform an incom
    Updated: 24-NOV-04

  93. RMAN Recover Database ( Category : RMAN )
    RMAN Script : RMAN Script to Recover Database. This script will perform an complete recover
    Updated: 02-JUN-05

  94. RMAN Recover and Move Tablespaces ( Category : RMAN )
    RMAN Script : RMAN Script which shows how to recover tablespaces into a different location.
    Updated: 24-NOV-04

  95. RMAN Recovery Controlfiles ( Category : RMAN )
    RMAN Script : RMAN Script to Recover Controlfiles. This script will perform an complete rec
    Updated: 24-NOV-04

  96. RMAN Recovery System Tablespace ( Category : RMAN )
    RMAN Script : RMAN Script to Recover the System tablespace. This script will perform a reco
    Updated: 24-NOV-04

  97. RMAN Recovery Tablespace ( Category : RMAN )
    RMAN Script : RMAN Script to Recover a Tablespace. This script will perform a tablespace re
    Updated: 24-NOV-04

  98. RMAN Statistic Summary ( Category : RMAN )
    SQL Script : Script reports RMAN backup statistics direct from the recovery catalog.
    Updated: 08-JAN-02

  99. RMAN Validate Database PIT recovery ( Category : RMAN )
    RMAN Script : RMAN script which check that all files needed to perform a database point in tim
    Updated: 24-NOV-04

  100. RMAN Validate Recover Database ( Category : RMAN )
    RMAN Script : RMAN script which check that all files needed to perform a database recovery are
    Updated: 24-NOV-04

  101. RMAN Validate Recovery ( Category : RMAN )
    RMAN Script : This script will check RMAN can access files needed for controlfile recovery.
    Updated: 24-NOV-04

  102. RMAN Validate Tablespace Recovery ( Category : RMAN )
    RMAN Script : RMAN script which check that all files needed to perform a tablespace recovery a
    Updated: 24-NOV-04

  103. Tivoli Storage Manager PIT RMAN recovery ( Category : RMAN )
    RMAN Script : Point in Time Recovery using TSM as the backup Media Manager. Restores a pre
    Updated: 24-NOV-04

  104. Tivoli Storage Manager RMAN Script ( Category : RMAN )
    RMAN Script : Full RMAN database Recovery from files backed up to TSM Restores a previousl
    Updated: 24-NOV-04

  105. Coalesce Tablespaces ( Category : Space Management )
    SQL Script : Coalesces All tablespaces within a database.
    Updated: 26-SEP-02

  106. Count Table space used ( Category : Space Management )
    SQL Script : Calculates the actual space used.
    Updated: 19-JUL-04

  107. Database Datafile Listing ( Category : Space Management )
    SQL Script : This script lists all the database datafiles, control files and redo log files w
    Updated: 18-MAR-02

  108. Database Space Info ( Category : Space Management )
    SQL Script : Script reports most a number of space related information
    Updated: 08-JAN-02

  109. Database Tablespace Usage ( Category : Space Management )
    SQL Script : This script lists gives a breakdown of who is using the space in each tablespace
    Updated: 28-SEP-02

  110. Datafile Free Space ( Category : Space Management )
    SQL Script : This script lists the free space in each datafile. It is useful is you have ove
    Updated: 03-APR-02

  111. Does Next Extent Fit ? ( Category : Space Management )
    SQL Script : This scripts reports any tables,indexes,clusters (and table/index partitions) wh
    Updated: 30-JUN-02

  112. List all Tablespace FreeSpace ( Category : Space Management )
    SQL Script : This script tablespace free space in bytes and percentage. Totals at foot of rep
    Updated: 30-MAR-02

  113. Move Tables to New Tablespace ( Category : Space Management )
    SQL Script : Dynamic script to move tables to a new tablespace
    Updated: 02-JUN-05

  114. Rebuild Indexes ( Category : Space Management )
    SQL Script : Dynamically rebuilds indexes for an owner into a given tablespace
    Updated: 19-JUL-04

  115. Table Extents for Given Owner ( Category : Space Management )
    SQL Script : This script is very similar to the List all tables/indexes extents in a given ta
    Updated: 13-SEP-02

  116. Table Extents for Given Owner ( Category : Space Management )
    SQL Script : This script is very similar to the List all tables/indexes extents in a given ta
    Updated: 13-SEP-02

  117. Table Fragmentation ( Category : Space Management )
    SQL Script : This script lists details how chained or migrated rows there are within a table.
    Updated: 13-SEP-02

  118. Tablespace Fragmentation Script ( Category : Space Management )
    SQL Script : This script lists details of the extents within a tablespace. This will help you
    Updated: 13-SEP-02

  119. Tablespace Mapper ( Category : Space Management )
    SQL Script : This script lists all used and free extents of a given tablespace. This is an ea
    Updated: 04-AUG-02

  120. Unused Space in extents ( Category : Space Management )
    SQL Script : This scripts displays details about how must unused space there is in segments i
    Updated: 14-MAR-02

  121. Amended sqlplus GLogin Script ( Category : Utilities )
    SQL Script : This glogin scripts includes the SID name and the database username you are logg
    Updated: 02-JUN-05

  122. Compare Two Databases ( Category : Utilities )
    SQL Script : Ever done a full database import to make a copy of a database and found that the
    Updated: 28-SEP-01

  123. Connect as another user ( Category : Utilities )
    SQL Script : This script allows you to change to connect to another schema from any DBA datab
    Updated: 01-JUN-98

  124. Count Table Rows ( Category : Utilities )
    SQL Script : This script lists the number of rows in all tables for a given schema. Can be us
    Updated: 28-SEP-02

  125. Database Documentor ( Category : Utilities )
    SQL Script : This displays table and column comments. Useful for a quick summary of databases
    Updated: 28-MAR-02

  126. Database Layout ( Category : Utilities )
    SQL Script : This query lists the physical location of datafiles, controlfile , online redolo
    Updated: 30-JUL-02

  127. Database Uptime ( Category : Utilities )
    SQL Script : Simple Script to display the database uptime i.e. How long the database has been
    Updated: 27-MAR-02

  128. Database User Account Listing ( Category : Utilities )
    SQL Script : This script lists the database user details and their status.
    Updated: 20-MAY-03

  129. Disable All Database Triggers ( Category : Utilities )
    SQL Script : This script disables all database triggers. If you are supporting a database tha
    Updated: 03-APR-02

  130. Display Data Dictionary Descriptions ( Category : Utilities )
    SQL Script : This script displays the name and description of all tables matching the search
    Updated: 31-DEC-02

  131. EPOC Date ( Category : Utilities )
    SQL Script : This converts a UNIX epoc date into a normal date. This is useful for third part
    Updated: 06-JUN-00

  132. Enable All Database Triggers ( Category : Utilities )
    SQL Script : This script enable all database triggers. If you are supporting a database that
    Updated: 01-APR-02

  133. End Backup Script ( Category : Utilities )
    SQL Script : IF a hot backup script fails for some reason, the tablespaces will still be in h
    Updated: 10-JAN-03

  134. Explain Plan Template ( Category : Utilities )
    SQL Script : The old faithful explain plan script. No Oracle professional should be without i
    Updated: 21-MAR-05

  135. Generate Create Rollback Segments ( Category : Utilities )
    SQL Script : Crerate the syntax for recreating rollback segments
    Updated: 19-JUL-04

  136. Generate Create Tablespace Script ( Category : Utilities )
    SQL Script : This script generates a create tablespace script from an existing database. It i
    Updated: 19-JUL-04

  137. Number Of CPUS ( Category : Utilities )
    Shell Script : A UNIX platform independent script which displays the number of CPUS on a server
    Updated: 24-NOV-04

  138. Random Number Generator ( Category : Utilities )
    SQL Script : This script contains 3 useful functions for generating random numbers, string an
    Updated: 28-FEB-03

  139. Size System Global Area ( Category : Utilities )
    SQL Script : This script will calculate the size of the SGA of an existing database based upo
    Updated: 03-APR-02

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links