--------------------------------------------------
-- Who has locks on a table?
--  (whole instance, all db)
-- 
-- V.Shulzhenko DBA_Tools (by Scott Kolaya (CDI))
--------------------------------------------------
set isolation to dirty read;
select  dbsname[ 1 , 18 ]
        ,username[ 1 , 8 ]
        ,owner  sid_owner
        ,type
        ,case when rowidlk= 0  then 'all' when rowidlk= 256  then 'page' else 'row' end   locked
--        ,hex(rowidlk) 
from syslocks l, syssessions s
     ,outer flags_text f
where l.owner = s.sid
      and f.tabname='syslcktab' and f.flags=rowidlk
      and l.tabname matches
'table_name'    -- select table name
order by  1 
;

--------------------------------------------------------------------
-- Who has locks on a tables (include fragmented tables)
--    selected db only
--    NOT USING sysmaster:SYSTABNAMES
-- 
-- найти сессию, которая имеет блокировки на таблице (без использования systabnames)
-- 
--   для решения проблемы утечки памяти при работе с огромной systabnames (десятки тысяч)
--   из-за Bug 115066 - SYSTEM WITH LARGE NUMBER OF TABLES CAN CONSUME LARGE AMOUNTS OF MEMORY IN THE RSAM POOL
-- 
-- V.Shulzhenko DBA_Tools (by Taн (SQL.RU 2006-10))
--------------------------------------------------------------------
set isolation to dirty read;
select  
        t.tabname[ 1 , 18 ]
        ,e.txt[ 1 , 4 ]     lock_type
        ,username[ 1 , 8 ]
        ,sid            sid_owner
from sysmaster:sysrstcb a, sysmaster:syslcktab c, sysmaster:flags_text e, systables t
where   c.owner = a.txp 
        and e.tabname = 'syslcktab' and e.flags = c.type
        and ( c.partnum = hex(t.partnum) 
              or
              c.partnum in (select hex(partn) from sysfragments f,systables t 
                       where t.tabid=f.tabid and fragtype = 'T')
            )
        and t.tabname matches 
--'table-name or *'
order by  1 , 2 , 3 , 4