Do the monitoring part of the SQL, sorted into a column output main content, do not like their own open to use, or add other need to use column information.
Resolution:
1. Only those who are blocked and those who are blocked are output, and the part where the blocking time >=60s;
2, CHR (10) newline output, sqlplus command line visible effect, PLSQL Dev is not used;
* * * * * * * * * * * * * * * * * * * * * * * * *
4. Finally generate the statement to find the locked data.
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_TEXT = SQL_TEXT = SQL_TEXT;
SELECT Q.SQL_ID, Q.SQL_TEXT, Q.SQL_FULLTEXT FROM V$SQL Q WHERE Q.SQL_ID = '&SQL_ID'; Type the variable SQL_ID into the SQL_ID extracted above