Wednesday, June 3, 2015

Find the session and SQL query which was previously locked.

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.

No comments:

Post a Comment