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 8

Scripts Sorted by Category, Title



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

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

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

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

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

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

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

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

  9. Monitor SQL Area ( Category : Monitor Scripts )
    SQL Script : This script queries the SQL area ordered by the the average cost of the statemen
    Updated: 30-JUN-99

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

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

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

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

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

  15. Disable Foreign Keys ( Category : Object Management )
    SQL Script : Disables all enabled foreign keys for a given owner.
    Updated: 03-AUG-98

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

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

  18. Dynamic Granting ( Category : Object Management )
    SQL Script : This script performs dynamic granting of tables,views,sequences to users/roles/P
    Updated: 17-JAN-99

  19. Dynamic Granting ( Category : Object Management )
    SQL Script : This script performs dynamic granting of tables,views,sequences of the SELECT pr
    Updated: 17-JAN-99

  20. Dynamic Granting ( Category : Object Management )
    SQL Script : This script performs dynamic granting of tables,views,sequences of the SELECT, I
    Updated: 17-JAN-99

  21. Dynamic Public Synonyms ( Category : Object Management )
    SQL Script : This script creates public synonyms for all TABLES/VIEWS/SEQUENCES owned by a us
    Updated: 18-FEB-99

  22. Enable Foreign Keys ( Category : Object Management )
    SQL Script : Enables all disabled.foreign keys for a given owner. Check out the partner disab
    Updated: 04-AUG-98

  23. 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: 01-JUN-98

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

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

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

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

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

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

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

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

  32. Open Cursors ( Category : Performance )
    SQL Script : This script reports current open cursors ordered by the the average cost of the
    Updated: 17-JAN-99

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

  34. Pin All Packages ( Category : Performance )
    SQL Script : Pin all packages that exist in the database above a minimum size. This script sh
    Updated: 30-JUN-99

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

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

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

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

  39. 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-01

  40. TOP Consistent Gets ( Category : Performance )
    SQL Script : Script displays the top consistent gets within the database - a good indicator w
    Updated: 08-OCT-02

  41. TOP Cursors ( Category : Performance )
    SQL Script : Script displays details of the sessions holding open the most cursors. It also a
    Updated: 03-OCT-02

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

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

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

  45. 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: 08-JAN-02

  46. 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: 26-OCT-98

  47. Database Shutdown ( Category : Platform Specific UNIX Scripts )
    Shell Script : Simple database shutdown script. Gets the ORACLE_HOME from /etc/oratab. This scr
    Updated: 29-JUL-98

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

  49. Database Startup ( Category : Platform Specific UNIX Scripts )
    Shell Script : Simple database startup script. Gets the ORACLE_HOME from /etc/oratab. This scri
    Updated: 29-JUL-98

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

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

  52. 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: 29-JUL-98

  53. Set Database ARCHIVELOG Mode ( Category : Platform Specific UNIX Scripts )
    Shell Script : Shut database down and sets the database in Archive Log Mode. This script us
    Updated: 29-JUL-98

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

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

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

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

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

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

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

  61. Database Datafile Listing ( Category : Space Management )
    SQL Script : This script lists all the database datafiles, control files and redo log files w
    Updated: 01-JUN-98

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

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

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

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

  66. Rebuild Indexes ( Category : Space Management )
    SQL Script : Dynamically rebuild all indexes for an owner into a given tablespace
    Updated: 11-OCT-02

  67. 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: 01-JUN-98

  68. Table Extents for Given Owner ( Category : Space Management )
    SQL Script : This script first gives a list of tablespaces. It then prompts to enter a tables
    Updated: 01-JUN-98

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

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

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

  72. Unused Space in extents ( Category : Space Management )
    SQL Script : This scripts displays details about how must unused space there is in segments i
    Updated: 01-OCT-00

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

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

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

  76. Count Partition Rows ( Category : Utilities )
    SQL Script : Displays each individual table partition row counts.
    Updated: 30-JAN-02

  77. 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: 01-JUN-98

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

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

  80. Database Layout ( Category : Utilities )
    SQL Script : This query lists the physical location of datafiles, controlfile and redologs co
    Updated: 06-JUN-00

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

  82. Disable All Database Triggers ( Category : Utilities )
    SQL Script : This script disables all database triggers. If you are supporting a database tha
    Updated: 01-JUN-98

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

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

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

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

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

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

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

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

  91. Show Average Row Length ( Category : Utilities )
    SQL Script : This script prompts for a table and then calculates the average row length. This
    Updated: 06-JUN-00

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

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