Blocking Session In Oracle

Buğra PARLAYAN

2 years ago

In today’s article we will be learning How to See Block Session in Oracle Database.

We can see the Block Session with the help of the following code block.

SELECT H.SAMPLE_TIME AS "DATE",
          H.INSTANCE_NUMBER AS "SERVER",
          U.USERNAME AS "USER",
          H.PROGRAM AS " APPLICATION ",
          H.MODULE AS "MODULE",
          H.SQL_ID AS " PENDING SQL ID",
          H.TOP_LEVEL_SQL_ID AS "HOLDING SQL ID",
          H.SQL_OPNAME AS "DML",
          H.SESSION_ID "PENDING SESSION",
          H.BLOCKING_SESSION "BLOCKING SESSION",
          H.BLOCKING_SESSION_SERIAL# "BLOCKING SERIAL",
          S.SQL_TEXT AS " PENDING SQL "
     FROM DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_USERS u, DBA_HIST_SQLTEXT s
    WHERE sample_time BETWEEN TO_DATE ('01/06/2020 12:28:00',
                                           'DD/MM/YYYY HH24:MI:SS')
                              AND TO_DATE ('01/06/2020 16:33:00',
                                           'DD/MM/YYYY HH24:MI:SS')
          AND H.USER_ID = U.USER_ID
          AND H.SQL_ID = S.SQL_ID
          AND H.BLOCKING_SESSION IS NOT NULL -- OPEN ALL QUERY IF CANCELED
ORDER BY H.SAMPLE_TIME DESC

SELECT H.SAMPLE_TIME AS "DATE",

H.INSTANCE_NUMBER AS "SERVER",

U.USERNAME AS "USER",

H.PROGRAM AS " APPLICATION ",

H.MODULE AS "MODULE",

H.SQL_ID AS " PENDING SQL ID",

H.TOP_LEVEL_SQL_ID AS "HOLDING SQL ID",

H.SQL_OPNAME AS "DML",

H.SESSION_ID "PENDING SESSION",

H.BLOCKING_SESSION "BLOCKING SESSION",

H.BLOCKING_SESSION_SERIAL# "BLOCKING SERIAL",

S.SQL_TEXT AS " PENDING SQL "

FROM DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_USERS u, DBA_HIST_SQLTEXT s

WHERE sample_time BETWEEN TO_DATE ('01/06/2020 12:28:00',

'DD/MM/YYYY HH24:MI:SS')

AND TO_DATE ('01/06/2020 16:33:00',

'DD/MM/YYYY HH24:MI:SS')

AND H.USER_ID = U.USER_ID

AND H.SQL_ID = S.SQL_ID

AND H.BLOCKING_SESSION IS NOT NULL -- OPEN ALL QUERY IF CANCELED

ORDER BY H.SAMPLE_TIME DESC