Script to detect TX blocking locks Following script detects TX blocking looks:
set linesize 160
set trimspool on
column name format A28
column type format A18
column sid format 9999
column block format 99
column ctime format 99999
column serial format 99999
column id1 format 99999999
column id2 format 99999999
column LMODE format A12
column request format A12
spool log.lock
select /*+ ordered use_merge(X$KSUSE X$KSQEQ) */
l.ctime ...
View sorts per user session Run this query to see sorts per user session. Replace BLOCKSIZE with block size for your database i.e. if it's 8Kb replace it with 8192
SELECT s.username
,s.sid
,s.serial#
,u.SESSION_ADDR ...
