关于oracle:Oracle-查看阻塞锁信息

做监控用的SQL局部，整顿到一列输入次要内容，不喜爱就本人拆开用，或增加其余须要应用的列信息。

解析：
1、只输入有阻塞者跟被阻塞者，并且阻塞工夫>=60s局部；
2、chr(10)换行输入，sqlplus命令行中可见成果，PLSQL Dev外面没啥用；
3、思考执行效率，不查找SQL_TEXT，只抓SQL_ID，Prev_SQL_ID，基于此辅助定位程序问题点；
4、最初生成查找锁住数据的语句。

SELECT 'Blocker_SID: ' || A.SID || ', ' || A_S.SCHEMANAME || ', From: ' ||       A_S.MACHINE || ', ' || A_S.PROGRAM || ', Cur_SQL: ' || A_S.SQL_ID ||       ',Prev_SQL: ' || A_S.PREV_SQL_ID || ', Status:' || A_S.STATUS ||       ', Lock_Time: ' || A.CTIME || 's.' || CHR(10) || ' -> Locked_SID: ' ||       B.SID || ', Blocked_SQL: ' || B_S.SQL_ID || ' , Locked_ON: ' ||       OBJ.OWNER || '.' || OBJ.OBJECT_NAME || ', Lock_Mode: ' ||       DECODE(A.LMODE,              0,              '0,none',              1,              '1,NULL',              2,              '2,row-S(SS)',              3,              '3,row-X(SX)',              4,              '4,share(S)',              5,              '5,S/Row-X(SSX)',              6,              '6,exclusive(X)') || CHR(10) || ' -> Locked_data_query_SQL: ' ||       (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)) AS BLOCK_DETAIL  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.CTIME >= 60   AND A.BLOCK > 0   AND B.REQUEST > 0   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;

附基于SQL_ID查找SQL_TEXT语句：

SELECT Q.SQL_ID, Q.SQL_TEXT, Q.SQL_FULLTEXT  FROM V$SQL Q WHERE Q.SQL_ID = '&SQL_ID';-- 执行后，将变量SQL_ID录入下面提取的SQL_ID即可