Here I introduce a sample of PL/SQL code to monitor the blocking sessions every N minutes and output in a formatted report.
First of all, edit the sql script.
[oracle@primary01 ~]$ vi CheckBlockingSessions.sql
SET SERVEROUTPUT ON;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
DECLARE
V_MAX_COUNT NUMBER := 4;
V_WAIT_MIN NUMBER := 5;
CURSOR C_BLOCKING
IS
SELECT BLOCKER.INST_ID,
BLOCKER.SID BLOCKER_SID ,
WAITING.SID WAITING_SID ,
TRUNC(WAITING.CTIME/60) MIN_WAITING ,
WAITING.REQUEST
FROM
(SELECT * FROM GV$LOCK WHERE BLOCK != 0 AND TYPE = 'TX'
) BLOCKER ,
GV$LOCK WAITING
WHERE WAITING.TYPE ='TX'
AND WAITING.BLOCK = 0
AND BLOCKER.INST_ID = WAITING.INST_ID
AND WAITING.ID1 = BLOCKER.ID1
ORDER BY 1,2,3;
V_CUR_REC C_BLOCKING%ROWTYPE;
BEGIN
FOR I IN 1..V_MAX_COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('======== Begin To Check Blocking Sessions '||SYSDATE||' ========');
OPEN C_BLOCKING;
DBMS_OUTPUT.PUT_LINE(LPAD('INST_ID',8)||LPAD('BLOCKER_SID',12)||LPAD('WAITING_SID',12)||LPAD('MIN_WAITING',12)||LPAD('REQUEST',8));
DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-')||RPAD(' ',12,'-')||RPAD(' ',12,'-')||RPAD(' ',12,'-')||RPAD(' ',8,'-'));
LOOP
FETCH C_BLOCKING INTO V_CUR_REC;
EXIT
WHEN C_BLOCKING%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(LPAD(V_CUR_REC.INST_ID,8)||LPAD(V_CUR_REC.BLOCKER_SID,12)||LPAD(V_CUR_REC.WAITING_SID,12)||LPAD(V_CUR_REC.MIN_WAITING,12)||LPAD(V_CUR_REC.REQUEST,8));
END LOOP;
DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-')||RPAD(' ',12,'-')||RPAD(' ',12,'-')||RPAD(' ',12,'-')||RPAD(' ',8,'-'));
IF C_BLOCKING%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('-> NO ROWS SELECTED');
ELSE
DBMS_OUTPUT.PUT_LINE('FOUND '||C_BLOCKING%ROWCOUNT||' RECORDS');
END IF;
CLOSE C_BLOCKING;
DBMS_OUTPUT.PUT_LINE('======== End To Check Blocking Sessions '||SYSDATE||' ========');
IF I < V_MAX_COUNT THEN
DBMS_OUTPUT.PUT_LINE('WAITING FOR '||V_WAIT_MIN||' MINS.');
DBMS_LOCK.SLEEP(V_WAIT_MIN * 60);
DBMS_OUTPUT.PUT_LINE(CHR(10));
END IF;
END LOOP;
END;
/
In the above code, the key statement is the cursor definition that can monitor the blocking sessions presently:
SELECT BLOCKER.INST_ID,
BLOCKER.SID BLOCKER_SID ,
WAITING.SID WAITING_SID ,
TRUNC(WAITING.CTIME/60) MIN_WAITING ,
WAITING.REQUEST
FROM
(SELECT * FROM GV$LOCK WHERE BLOCK != 0 AND TYPE = 'TX'
) BLOCKER ,
GV$LOCK WAITING
WHERE WAITING.TYPE ='TX'
AND WAITING.BLOCK = 0
AND BLOCKER.INST_ID = WAITING.INST_ID
AND WAITING.ID1 = BLOCKER.ID1
ORDER BY 1,2,3; After finishing the sql scirpt, we can execute the block of code.
[oracle@primary01 ~]$ sqlplus / as sysdba @CheckBlockingSessions.sql
...
======== Begin To Check Blocking Sessions 2013-03-05 16:31:39 ========
INST_ID BLOCKER_SID WAITING_SID MIN_WAITING REQUEST
------- ----------- ----------- ----------- -------
1 111 110 430 6
1 111 123 429 6
------- ----------- ----------- ----------- -------
FOUND 2 RECORDS
======== End To Check Blocking Sessions 2013-03-05 16:31:39 ========
WAITING FOR 5 MINS.
======== Begin To Check Blocking Sessions 2013-03-05 16:36:39 ========
INST_ID BLOCKER_SID WAITING_SID MIN_WAITING REQUEST
------- ----------- ----------- ----------- -------
1 111 110 435 6
1 111 123 434 6
------- ----------- ----------- ----------- -------
FOUND 2 RECORDS
======== End To Check Blocking Sessions 2013-03-05 16:36:39 ========
WAITING FOR 5 MINS.
======== Begin To Check Blocking Sessions 2013-03-05 16:41:39 ========
INST_ID BLOCKER_SID WAITING_SID MIN_WAITING REQUEST
------- ----------- ----------- ----------- -------
1 111 110 440 6
1 111 123 439 6
------- ----------- ----------- ----------- -------
FOUND 2 RECORDS
======== End To Check Blocking Sessions 2013-03-05 16:41:39 ========
WAITING FOR 5 MINS.
======== Begin To Check Blocking Sessions 2013-03-05 16:46:39 ========
INST_ID BLOCKER_SID WAITING_SID MIN_WAITING REQUEST
------- ----------- ----------- ----------- -------
1 111 110 445 6
1 111 123 444 6
------- ----------- ----------- ----------- -------
FOUND 2 RECORDS
======== End To Check Blocking Sessions 2013-03-05 16:46:39 ========
PL/SQL procedure successfully completed.
SQL>
If you like to use the code on your job, you can change the default value of variables for your need.
The above anonymous PL/SQL is quiet simple, however, there is a defect that the output buffer will not flush until the end of execution. You can try to output the data by UTL_FILE or UTL_SMTP instead of DBMS_OUTPUT.