Absolutely, you can capture those short-lived blocking session details in real-time on Oracle 12c. Since the blocks clear within seconds, you need methods that can sample or react quickly to the event. Here are proven approaches tailored to your scenario:

1. Periodic Custom Script (Lightweight & Flexible) #

This is the most straightforward way to catch transient blocks. You can write a PL/SQL or shell script that queries key dynamic views at short intervals (1-2 seconds works well) and logs results to a custom table.

Step 1: Create a Log Table #

First, set up a table to store blocking details for later analysis:

CREATE TABLE blocking_session_log (
    log_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP,
    blocking_sid NUMBER,
    blocking_serial# NUMBER,
    blocking_username VARCHAR2(30),
    blocking_osuser VARCHAR2(30),
    blocking_program VARCHAR2(48),
    blocked_sid NUMBER,
    blocked_serial# NUMBER,
    blocked_username VARCHAR2(30),
    blocked_osuser VARCHAR2(30),
    blocked_program VARCHAR2(48),
    lock_type VARCHAR2(20),
    lock_mode VARCHAR2(20),
    wait_event VARCHAR2(64)
);

Step 2: Run a Continuous PL/SQL Query #

Execute this block in a dedicated session (or schedule it via DBMS_SCHEDULER) to capture blocks in real-time:

DECLARE
    v_interval NUMBER := 1; -- Check every 1 second
BEGIN
    LOOP
        INSERT INTO blocking_session_log (
            blocking_sid, blocking_serial#, blocking_username,
            blocking_osuser, blocking_program, blocked_sid,
            blocked_serial#, blocked_username, blocked_osuser,
            blocked_program, lock_type, lock_mode, wait_event
        )
        SELECT
            b.sid AS blocking_sid,
            b.serial# AS blocking_serial#,
            b.username AS blocking_username,
            b.osuser AS blocking_osuser,
            b.program AS blocking_program,
            s.sid AS blocked_sid,
            s.serial# AS blocked_serial#,
            s.username AS blocked_username,
            s.osuser AS blocked_osuser,
            s.program AS blocked_program,
            l.type AS lock_type,
            DECODE(l.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)',
                   4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', 'Unknown') AS lock_mode,
            sw.event AS wait_event
        FROM v$session s
        JOIN v$session b ON s.blocking_session = b.sid
        JOIN v$lock l ON s.sid = l.sid
        JOIN v$session_wait sw ON s.sid = sw.sid
        WHERE s.blocking_session IS NOT NULL
          AND s.status = 'ACTIVE';
        
        COMMIT;
        DBMS_LOCK.SLEEP(v_interval);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Note: Adjust v_interval if your blocks clear faster than 1 second. If you don't want a dedicated session, use DBMS_SCHEDULER to run the insert logic on a recurring schedule.

2. Use Automatic Session History (ASH) Real-Time Queries #

Oracle 12c's ASH samples active sessions every second by default—perfect for catching sub-10-second blocks. You can query the real-time ASH view directly or set up a monitoring script.

Real-Time ASH Query #

Run this repeatedly (or in a loop) to see current and recent blocking events:

SELECT
    sample_time,
    session_id AS blocked_sid,
    session_serial# AS blocked_serial#,
    username AS blocked_username,
    blocking_session AS blocking_sid,
    event AS wait_event,
    sql_id AS blocked_sql_id,
    blocking_session_status
FROM v$active_session_history
WHERE blocking_session IS NOT NULL
ORDER BY sample_time DESC;

For longer retention, check dba_hist_active_sess_history, but v$active_session_history holds data for the last ~1 hour (depending on your ASH configuration).

3. Event-Driven Database Trigger #

If you want to capture blocks the moment they occur, create a trigger that fires when a session hits a blocking wait event. This avoids polling and reacts immediately.

Example Enqueue Block Trigger #

CREATE OR REPLACE TRIGGER capture_blocking_enqueues
AFTER LOGON ON DATABASE
BEGIN
    -- Trace enqueue lock activity (common cause of blocking)
    EXECUTE IMMEDIATE 'ALTER SYSTEM SET EVENTS ''10400 trace name context forever, level 1''';
EXCEPTION
    WHEN OTHERS THEN
        NULL;
END;
/

Note: System-wide traces can generate large files. Restrict tracing to specific users/sessions using DBMS_MONITOR.SESSION_TRACE_ENABLE if possible, instead of enabling it for the entire database.

4. EM Real-Time Monitoring (GUI Option) #

If you have access to Oracle Enterprise Manager (EM Cloud Control or EM Express), use the Real-Time Performance page. It visualizes blocking chains in real-time and captures details automatically—great if you prefer a no-code approach.

内容的提问来源于stack exchange，提问作者sasikumar