This Query will help you to find lock history.
SQL> SELECT DISTINCT A.INST_ID, A.SQL_ID ,A.INST_ID,A.BLOCKING_SESSION,A.BLOCKING_SESSION_SERIAL#,A.USER_ID,S.SQL_TEXT,A.MODULE
2 FROM GV$ACTIVE_SESSION_HISTORY A ,GV$SQL S
3 WHERE A.SQL_ID=S.SQL_ID
4 AND BLOCKING_SESSION IS NOT NULL
5 AND A.USER_ID <> 0;
INST_ID SQL_ID INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL# USER_ID SQL_TEXT
---------- ------------- ---------- ---------------- ------------------------ ---------- -------------------------------------------------------
1 0jgf8xdjf2h8w 1 74 9 84 UPDATE REGIONS SET REGION_NAME='TEST'
SQL>
Note : It will show data if your current log is enough.
SQL> SELECT DISTINCT A.INST_ID, A.SQL_ID ,A.INST_ID,A.BLOCKING_SESSION,A.BLOCKING_SESSION_SERIAL#,A.USER_ID,S.SQL_TEXT,A.MODULE
2 FROM GV$ACTIVE_SESSION_HISTORY A ,GV$SQL S
3 WHERE A.SQL_ID=S.SQL_ID
4 AND BLOCKING_SESSION IS NOT NULL
5 AND A.USER_ID <> 0;
INST_ID SQL_ID INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL# USER_ID SQL_TEXT
---------- ------------- ---------- ---------------- ------------------------ ---------- -------------------------------------------------------
1 0jgf8xdjf2h8w 1 74 9 84 UPDATE REGIONS SET REGION_NAME='TEST'
SQL>
Note : It will show data if your current log is enough.
No comments:
Post a Comment