SQL被阻塞查询代码,Oracle代码库,德仔网

代码语言
代码分类
【Oracle】 SQL被阻塞查询
作者:imcto / 发布于2017/10/25/ 617
SQL被阻塞查询
SELECT '节点 ' || A.INST_ID || ' SESSION ' || A.SID || ',' || A_S.SERIAL# ||

       ' 阻塞了 节点 ' || B.INST_ID || ' SESSION ' || B.SID || ',' || B_S.SERIAL# BLOCKINFO,

       A.INST_ID,

       A_S.SID,

       A_S.SCHEMANAME,

       A_S.MODULE,

       A_S.STATUS,

       A.TYPE LOCK_TYPE,

       A.ID1,

       A.ID2,

       DECODE(A.LMODE,

              0,

              'NONE',

              1,

              NULL,

              2,

              'ROW-S (SS)',

              3,

              'ROW-X (SX)',

              4,

              'SHARE (S)',

              5,

              'S/ROW-X (SSX)',

              6,

              'EXCLUSIVE (X)') LOCK_MODE,



       '后为被阻塞信息' ,

       B.INST_ID BLOCKED_INST_ID,

       B_S.SID BLOCKED_SID,

       B.TYPE BLOCKED_LOCK_TYPE,

       DECODE(B.REQUEST,

              0,

              'NONE',

              1,

              NULL,

              2,

              'ROW-S (SS)',

              3,

              'ROW-X (SX)',

              4,

              'SHARE (S)',

              5,

              'S/ROW-X (SSX)',

              6,

              'EXCLUSIVE (X)') BLOCKED_LOCK_REQUEST,

       B_S.SCHEMANAME BLOCKED_SCHEMANAME,

       B_S.MODULE BLOCKED_MODULE,

       B_S.STATUS BLOCKED_STATUS,

       B_S.SQL_ID BLOCKED_SQL_ID,

       OBJ.OWNER BLOCKED_OWNER,

       OBJ.OBJECT_NAME BLOCKED_OBJECT_NAME,

       OBJ.OBJECT_TYPE BLOCKED_OBJECT_TYPE,

       CASE

          WHEN B_S.ROW_WAIT_OBJ# <> -1 THEN

             DBMS_ROWID.ROWID_CREATE(1,

                                     OBJ.DATA_OBJECT_ID,

                                     B_S.ROW_WAIT_FILE#,

                                     B_S.ROW_WAIT_BLOCK#,

                                     B_S.ROW_WAIT_ROW#) 

          ELSE 

            '-1' 

          END BLOCKED_ROWID, --THE BLOCKED ROWID

       DECODE(OBJ.OBJECT_TYPE,

              'TABLE',

              'SELECT * FROM ' || OBJ.OWNER || '.' || OBJ.OBJECT_NAME ||

              ' WHERE ROWID=''' ||

              DBMS_ROWID.ROWID_CREATE(1,

                                      OBJ.DATA_OBJECT_ID,

                                      B_S.ROW_WAIT_FILE#,

                                      B_S.ROW_WAIT_BLOCK#,

                                      B_S.ROW_WAIT_ROW#) || '''',

              NULL)  BLOCKED_DATA_QUERYSQL

  FROM GV$LOCK     A,

       GV$LOCK     B,

       GV$SESSION  A_S,

       GV$SESSION  B_S,

       DBA_OBJECTS OBJ

 WHERE A.ID1 = B.ID1

   AND A.ID2 = B.ID2

   AND A.BLOCK > 0 --BLOCK THE OTHER SQL

   AND B.REQUEST > 0

   AND ((A.INST_ID = B.INST_ID AND A.SID <> B.SID) OR

       (A.INST_ID <> B.INST_ID))

   AND A.SID = A_S.SID

   AND A.INST_ID = A_S.INST_ID

   AND B.SID = B_S.SID

   AND B.INST_ID = B_S.INST_ID

   AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+)

 ORDER BY A.INST_ID,A.SID;
试试其它关键字
同语言下
可能有用的
imcto贡献的其它代码(12)