Not really because it is not a *SQL* that is blocking your active session, it is a *lock*.
For example - in a 1 row table:
Session 1 does:
- delete the row
- insert a new row
- updates that row
- runs 500 queries against other tables
- goes to lunch
Session 2 comes along, tries to do something with that row and gets blocked.... it not really an SQL that has blocked them - it could one of any of the first three above, but they could have been run hours ago.
What you *can* work out is what *locks* the session has by examining v$lock. For example, a basic script I'll often use is:
--
-- an example locked row
--
SQL> delete from t_target where rownum = 1;
1 row deleted.
SQL> SELECT /*+ ORDERED */
2 s.username,
3 s.osuser,
4 S.SID,
5 L.TYPE,
6 L.ID1,
7 o.object_name,
8 DECODE(L.LMODE, 0, 'NONE', 1, 'NULL', 2, 'SS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 'X', '?') holding,
9 DECODE(L.REQUEST, 0, 'NONE', 1, 'NULL', 2, 'SS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 'X', '?') wanting,
10 l.ctime how_long,
11 decode(l.block,0,null,2,null,'YES') is_blocking
12 FROM V$SESSION S, V$LOCK L, sys.dba_objects o
13 WHERE L.SID=S.SID
14 AND S.USERNAME is not null
15 and l.type != 'AE'
16 and l.id1 = o.object_id
17 /
USERNAME OSUSER SID TY ID1 OBJECT_NAME HOLD WANT HOW_LONG IS_
-------------------- ------------------------------ ---------- -- ---------- ---------------------------------------- ---- ---- ---------- ---
MCDONAC XPS13\hamcdc 23 TM 166581 T_TARGET RX NONE 76
2 rows selected.
Here are some mitigation strategies
You can also use Resource Manager to terminate a session that is inactive but is blocking an active resource.
See
https://docs.oracle.com/database/122/ADMIN/managing-resources-with-oracle-database-resource-manager.htm#ADMIN11862 for details on the MAX_IDLE_BLOCKER_TIME directive.