| layout | default |
|---|---|
| title | DBA Blocking Sessions | Oracle EBS SQL Report |
| description | Chain of currently blocking and blocked database sessions from v$waitchains – Oracle E-Business Suite SQL report |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, DBA, Blocking, Sessions, dba_objects, v$wait_chains, gv$session |
| permalink | /DBA%20Blocking%20Sessions/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Chain of currently blocking and blocked database sessions from v$wait_chains
dba_objects, v$wait_chains, gv$session, gv$process, gv$sqlarea
DBA SGA Active Session History, DBA SGA Blocking Session Summary, DBA AWR Blocking Session Summary, DBA AWR Active Session History, DBA AWR SQL Performance Summary, DBA SGA SQL Performance Summary, DBA ORDS Configuration Validation, DBA Blitz Report ORDS Configuration, FND Concurrent Requests 11i
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
| Resource | Link |
|---|---|
| Excel Example Output | DBA Blocking Sessions 06-May-2020 103312.xlsx |
| Blitz Report™ XML Import | DBA_Blocking_Sessions.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/dba-blocking-sessions/ |
The DBA Blocking Sessions report is a critical diagnostic tool for Oracle E-Business Suite Database Administrators (DBAs). It provides real-time visibility into database lock contention, identifying sessions that are blocking others and causing performance degradation. By visualizing the chain of blocking and blocked sessions, DBAs can quickly pinpoint the root cause of system slowness or "hanging" processes and take immediate corrective action.
In a high-concurrency environment like Oracle EBS, database locking is a normal mechanism to ensure data integrity. However, when a session holds a lock for an extended period (due to a long-running transaction, uncommitted changes, or a "zombie" process), it can prevent other users from accessing the same data. This leads to:
- System Slowness: Users experience delays or unresponsive screens.
- Operational Stalls: Critical business processes (e.g., order entry, month-end close) may be halted.
- User Frustration: Lack of visibility into why the system is slow leads to increased helpdesk tickets.
Identifying the specific session holding the lock—and the SQL statement it is executing—is often like finding a needle in a haystack without the right tools.
The DBA Blocking Sessions report solves this challenge by querying Oracle's v$wait_chains and related dynamic performance views. It presents a hierarchical view of the blocking chain, showing exactly which session is at the top of the chain (the "blocker") and which sessions are waiting (the "blockees").
Key Features:
- Hierarchical View: Displays the relationship between blocking and waiting sessions.
- Real-Time Analysis: Provides a snapshot of current lock contention.
- Detailed Diagnostics: Includes session IDs, user names, machine names, SQL text, and wait events.
- Actionable Intelligence: Enables DBAs to decide whether to contact the user, kill the session, or optimize the underlying SQL.
The report leverages Oracle's Active Session History (ASH) and wait interface architecture.
Primary Tables/Views:
v$wait_chains: The core view for identifying blocking relationships in RAC and non-RAC environments.gv$session: Provides details about the active sessions (User, Module, Machine, Status).gv$process: Links sessions to operating system processes.gv$sqlarea: Retrieves the SQL text currently being executed by the sessions.dba_objects: Identifies the database object (table, index) involved in the lock.
Logic:
The query joins v$wait_chains with session and SQL information to construct a readable output. It filters for sessions that are currently in a wait state due to another session.
Q: What is the difference between a "blocking" session and a "blocked" session? A: A blocking session holds a lock on a resource (like a row in a table). A blocked session is trying to acquire a lock on the same resource but must wait until the blocking session releases it (usually by committing or rolling back).
Q: Does this report show historical blocking sessions? A: No, this report shows current blocking sessions. For historical analysis, you would typically use AWR (Automatic Workload Repository) or ASH (Active Session History) reports.
Q: What should I do if I find a blocking session? A: First, analyze the "blocker." Is it a valid long-running job? If so, you may need to wait. Is it a user who left their screen open with uncommitted changes? You might contact them. Is it a "stuck" process? You might need to kill the session.
Q: Can this report handle RAC (Real Application Clusters) environments?
A: Yes, the use of gv$ (Global) views and v$wait_chains ensures that blocking chains across different RAC nodes are detected.
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© 2026 Enginatics