DBA SQL Queries

Oracle Apps DBA Monitoring SQL Queries

Being an Oracle Apps DBA, We need to act immediately on the Production issues . we have to analyze and resolve the issues promptly.

Below are the SQL Queries/Scripts which I have Consolidated to analyze the issues.

Click on Links below to View and Download the SQL Queries accordingly 

Oracle Database Monitoring SQL Queries:

Query To Monitor Database Session Waits
Query To Monitor Database Locks
Query To Check library cache pin Events/Locks
Query To Monitor Longops Sessions
Query To Check Database Tablespace Usage With Percentage
Query To Monitor High Temporary Tablespace Usage Sessions
Query To find which transactions/sessions consuming the UNDO tablespace
Query To Monitor Temporary Tablespace Usage
Query To Get Count and List of Invalid Objects
SQL Script To Lookup Database Details for a given sid
SQL Script To Lookup Database Details for a given spid

Oracle Standby Database Monitoring SQL Queries:

• Query To Get Archive Log Apply Rate Speed of a Standby Database
• Query To Monitor Archive Log Shipping and Gap Status on a Standby Database

Miscellaneous Database SQL Queries:

Query To Check Database Status and Properties
Query To Check Oracle Database Size
Query To Get List Of Tablespaces Exist in an Oracle Database
Query To Get Datafile Details for a Given Tablespace
Query To Get List of Database Links
Query To Get List of Database Directories
Query To Check Components Status Registered in an Oracle Database

Oracle EBS Application Monitoring SQL Queries:

• Query To Check Concurrent Managers Status
• Query To Check Inactive/Down Concurrent Managers Details
• Query To Check All Workflow Mailer Component Status
• Query To Check Inventory Interface Managers Status
• Query To Check Long Running Concurrent Requests
• Query To Get Details of All Current Running Concurrent Requests
• Query To Check Concurrent Program Run History
• Query To Check Cocurrent Request Status
• SQL Script To Analyze a Concurrent Request
• Query To Get Concurrent Request ID for a Given Oracle SID
• Query To Get Oracle SID Details for a Given Concurrent Request ID

Miscellaneous Oracle EBS Application Related Queries:

• Query To Monitor Invalid objects in Oracle EBS R12.2
• Query To Get Internal Manager (ICM) Log File Location
• Query To Check Oracle EBS Version, OATM Status and other Basic Details
• Query To Get FND NODES Details
• Query To Check FND Languages Installed in Oracle EBS
• Query To Get FND User Details
• SQL Script to Start and Stop EBS Workflow Service Containers