Oracle DBA Scripts and tips
Scripts and Tips

Make sure that you keep checking this page since we will be adding new scripts and tips on a regular basis. This page groups the scripts, tips and useful info by categories and is indexed below.

The quickest way to get going is to "Cut & Paste" between your Web Browser and a terminal emulation onto the appropriate platform all in the same directory.

  [General] [Object Management] [Space Management] [DB Resource Manager] [Performance] [RMAN] [Utilities] [Monitor] [Oracle Applications] [UNIX]  

Index Of Oracle Scripts

  1. General Oracle Database Information
    1. New database features introduced from Oracle 7.0 through Oracle9i Release 2 [Updated 05-JUN-02]
    2. How to use the Product User Profile table
    3. List Of 8i Init.ora parameters and descriptions [Updated 24-MAY-99]
    4. Translation table of UNIX commands to DEC VMS commands including examples
    5. SUN Kernel parameters explained with typical values
    6. Painless method to create a duplicate database (No messing with Import/export)
    7. V$ Table reference
  2. Server SQL/PLSQL Scripts
    1. Object Management
      1. Table and Index Partition details
      2. List all table, role, system privileges granted to a user
      3. List all database link details (including password)
      4. Indexes belonging to a table
      5. Enable a schemas foreign keys
      6. Disable a schemas foreign keys
      7. Adjust Sequence values based upon sequences in another database
      8. Analyze Tables Revisited / Gather Statistics [Updated 26-Feb-02]
      9. Dynamic CREATE PUBLIC SYNONYM script
      10. Dynamic SELECT,INSERT,UPDATE,DELETE grants
      11. Dynamic SELECT grants
      12. Dynamic SELECT,INSERT,UPDATE,DELETE,REFERENCES grants
      13. Dynamic synonyms on PROCEDURES/FUNCTIONS/PACKAGES
      14. Dynamic execute grants on PROCEDURES/FUNCTIONS/PACKAGES
    2. Space Management
      1. Tablespace Fragmentation
      2. Table Fragmentation
      3. Online Index Rebuilds
      4. Datafile Free Space
      5. Database Usage Report
      6. Tablespace Usage Report
      7. Index Statistics [Updated 28-FEB-01]
      8. Block Space Utilisation in 9i
      9. Unused Space in an Extent [Updated 12-SEP-00]
      10. Is there space for the next extent in the tablespace ? [Updated 30-JUL-02]
      11. Actual space used by table rows [Updated 28-FEB-01]
      12. Generate Create Rollback Segment Script
      13. Coalesce Tablespaces in Oracle 7.1 and 7.2
      14. Generate Create Tablespace Script
      15. List all database datafiles
      16. List all tables/indexes extents in a given tablespace
      17. List all tables/indexes with extents greater than a given value
      18. List all used and free extents in a given tablespace
      19. List %age and bytes free in tablespaces. [Updated 30-MAR-00]
    3. DB Resource Manager
      1. Create Resource Manager Plan
      2. Resource Manager Configuration
      3. Assign Plan Privileges
      4. Monitor Resource Manager Sessions
      5. Drop Resource Manager Plan
    4. Performance
      1. TOP 10 CPU Users
      2. TOP 10 Users Reading Data
      3. TOP 10 Users Open Cursors
      4. Sort Usage Report
      5. Datafile Statistics
      6. Latch Contention
      7. Database hit ratio [Updated 30-MAR-00]
      8. Quick database performance overview, check all key database performance indicators
      9. Free SGA Memory Usage
      10. Explain Plan TEMPLATE [Updated 28-FEB-01]
      11. List Unpinned Objects in DB Cache
      12. Pin Oracle Standard Packages
      13. Pin All Database Packages [Updated 30-JUN-99]
      14. Tables Recently queried by a full scan
      15. Recently Accessed Indexes
      16. DB Block Buffer usage [Updated 30-JUN-99]
      17. Cost of current open cursors
      18. Session Hit Ratios
      19. Session Details [Updated 29-MAR-00]
      20. SQL Text of database locks [Updated 12-SEP-00]
    5. RMAN
      1. RMAN Full Backup
      2. RMAN Incremental Backup
      3. RMAN Archive Log Backup
      4. Monitor RMAN
      5. RMAN Backup Summary
    6. Utilities
      1. Count Rows For Table Partitions
      2. Set Session in Trace
      3. Database Info
      4. Convert EPOC date
      5. Database Uptime [Updated 8-Jan-02]
      6. Database Layout [Updated 30-Jul-02]
      7. Database Documentor
      8. Count all records on database for given table owner
      9. Change to another database user without knowing the password
      10. Disable all database triggers
      11. Enable all database triggers
      12. Compare Objects of 2 different databases [Updated 28-Feb-01]
      13. Size SGA based on an existing database
      14. Calculate Average Row length for a given table [Updated 06-JUN-00]
    7. Monitor
      1. Monitor Session
      2. Redo History
      3. Monitor Redo Logs
      4. Monitor Users [Updated 06-JUN-00]
      5. Monitor all database lock details
      6. Monitor all database deadlocks
      7. Monitor what users are doing in Rollback Segments [Updated 06-JUN-00]
      8. Monitor the key statistics for Open Cursors
      9. Monitor SQL Area [Updated 30-JUN-99]
  3. Oracle Applications
    1. Profile Options
    2. Apps Login Details
  4. Platform Specific UNIX Scripts
    1. Common
      1. Database Size Summary
      2. List all installed Oracle products and versions (NO database needed) [Updated 08-JAN-02]
      3. Create Database TEMPLATE
      4. Database Startup
      5. Database Shutdown
    2. Backup Scripts
      1. Cold backup [Updated 8-Jan-02]
      2. Hot backup
      3. Set database in ARCHIVELOG mode
      4. Full database export and UNIX compress
      5. Full database import and UNIX uncompress

      New Since 30th July 2002

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz
Legal

Logo