Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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 |
