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 10g

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 using the gather_schema_stats proce
    Updated: 24-NOV-04

  3. Get System Stats ( Category : Analyze )
    SQL Script : Displays the values of any System Statistics in place or in a stats table
    Updated: 02-JUN-05

  4. 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

  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 Plan ( Category : DB Resource Manager )
    SQL Script : This script drops a resource manager plan and any associated subplans.
    Updated: 30-AUG-02

  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: 26-SEP-02

  12. Database Alerts ( Category : General Database Information )
    SQL Script : Reports on any 10g Database Alerts.
    Updated: 20-JUL-04

  13. Fast Start MTTR settings ( Category : General Database Information )
    SQL Script : Displays the settings of the MTTR. Also displays the new 10g feature of optimal
    Updated: 20-JUL-04

  14. Flashback Recovery Area Information ( Category : General Database Information )
    SQL Script : list your Flashback Recovery Area details
    Updated: 28-DEC-05

  15. High Water Mark Statistics ( Category : General Database Information )
    SQL Script : Displays the Database High Water mark setting of certain database features.
    Updated: 20-JUL-05

  16. 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

  17. Find Redo ( Category : Monitor Scripts )
    SQL Script : Script to see what session are using redo
    Updated: 28-DEC-05

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

  19. 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

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

  21. Monitor Data Guard ( Category : Monitor Scripts )
    SQL Script : This script lists the important tables from which you can tell if Data Guard is
    Updated: 19-JUL-04

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

  23. 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

  24. 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

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

  26. 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-MAR-05

  27. 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

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

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

  30. 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: 31-DEC-02

  31. 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

  32. 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

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

  34. 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

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

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

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

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

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

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

  41. 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

  42. 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

  43. 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

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

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

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

  47. 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

  48. 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

  49. 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

  50. DB Cache Advice ( Category : Performance )
    SQL Script : New 9i view which details the potential benefits of increasing the buffer_pool p
    Updated: 26-MAR-05

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

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

  53. 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

  54. 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

  55. Fully Decoded Locking Script ( Category : Performance )
    SQL Script : Fully Decoded Locking Script
    Updated: 28-DEC-05

  56. 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

  57. 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

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

  59. 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

  60. 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

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

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

  63. 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

  64. 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

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

  66. 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

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

  68. 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

  69. 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

  70. 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

  71. 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

  72. 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

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

  74. 10g Segment Advisor ( Category : Space Management )
    SQL Script : Sample Script in order to run the Segment Advisor from PLSQL
    Updated: 02-JUN-05

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

  76. Count Table Space Used ( Category : Space Management )
    SQL Script : This script calculates the actual space used by a table (and not just the space
    Updated: 28-FEB-02

  77. 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

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

  79. 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

  80. 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

  81. 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

  82. 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

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

  84. Rebuild Indexes ( Category : Space Management )
    SQL Script : Dynamically rebuild all indexes for an owner into a given tablespace. Updated fo
    Updated: 26-SEP-02

  85. SYSAUX Occupants ( Category : Space Management )
    SQL Script : Displays the contents of the SYSAUX tablespace
    Updated: 28-DEC-05

  86. Size Flash Recovery Area ( Category : Space Management )
    SQL Script : A script to estimate the size of the flash_recovery_area in 10G
    Updated: 02-JUN-05

  87. Space Usage ( Category : Space Management )
    SQL Script : This procedure used a new procedure in Oracle 9i which displays a breakdown of t
    Updated: 14-MAR-02

  88. 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

  89. 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

  90. 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

  91. 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

  92. 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

  93. Undo Statistics ( Category : Space Management )
    SQL Script : This script provides a summary of the values in the v$undostat table. This table
    Updated: 08-APR-03

  94. 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

  95. Check Grid Control Blackout Details ( Category : Utilities )
    SQL Script : Use this to debug any Grid Control Blackout problems
    Updated: 28-DEC-05

  96. 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

  97. 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

  98. Data Pump through PLSQL Example ( Category : Utilities )
    SQL Script : Example script to run Data Pump through PLSQL
    Updated: 02-JUN-05

  99. DataPump Monitor ( Category : Utilities )
    SQL Script : This script displays active 10g Data Pump Sessions
    Updated: 19-MAR-05

  100. Database Information ( Category : Utilities )
    SQL Script : Basic Database details - useful to put in as part of a glogin.sql script if you
    Updated: 28-MAR-05

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

  102. Database Scheduler ( Category : Utilities )
    SQL Script : Details Job Windows and Jobs in the 10g Scheduler.
    Updated: 20-MAR-05

  103. 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

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

  105. 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

  106. 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

  107. 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

  108. 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

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

  110. Generate Create Rollback Script ( Category : Utilities )
    SQL Script : This script generates a create rollback segment script. It is especially useful
    Updated: 14-MAR-04

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

  112. Generate Object DDL ( Category : Utilities )
    SQL Script : This script uses the DBMS_METADATA package tp generate the DDL of the majority o
    Updated: 24-NOV-04

  113. Logminer Script ( Category : Utilities )
    SQL Script : Simple Logminer script to analyze one log file
    Updated: 03-JAN-03

  114. 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

  115. Oracle 10G Grid Control Management Repository Health Check ( Category : Utilities )
    SQL Script : Script to report on the health of the management repository in Grid Control
    Updated: 02-JUN-05

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

  117. Show Average Row Length ( Category : Utilities )
    SQL Script : This script prompts for a table and then calculates the average row length. This
    Updated: 03-APR-02

  118. 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