Remote IT Support!! Please contact us

For Any Remote Support,Implementation/Upgrade Projects,Queries,Collaborations please mail us at support@funoracleapps.com

Query to find historical blocking sessions in Oracle Database

No comments

Query to find historical blocking sessions

We can use either GV$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY

Query:

SELECT DISTINCT ash.sql_id,
                ash.inst_id,
                ash.blocking_session         blocker_ses,
                ash.blocking_session_serial# blocker_ser,
                ash.user_id,
                s.sql_text,
                ash.module,
                ash.sample_time
  FROM GV$ACTIVE_SESSION_HISTORY ash, gv$sql s
 WHERE     ash.sql_id = s.sql_id
       AND blocking_session IS NOT NULL
       AND ash.user_id <> 0                                  -- exclude SYS user
       AND ash.sample_time BETWEEN SYSDATE -1 AND  SYSDATE 

or 

SELECT DISTINCT ash.sql_id,ash.session_id,
                ash.blocking_session         blocker_ses,
                ash.blocking_session_serial# blocker_ser,
                ash.user_id,
                s.sql_text,
                ash.module,
                ash.sample_time
  FROM DBA_HIST_ACTIVE_SESS_HISTORY ash, gv$sql s
 WHERE     ash.sql_id = s.sql_id
       AND blocking_session IS NOT NULL
       AND ash.user_id <> 0                                  -- exclude SYS user
       and upper(SQL_TEXT) like upper('%PRODUCT_TABLE%') --matching a specific table
       AND ash.sample_time BETWEEN SYSDATE -3 AND  SYSDATE 



If you like please follow and comment

No comments :

Post a Comment