Script to show the DML locks into the Oracle instance

De Notas pessoais


. Script dml_locks.sql

select session_id "sid",SERIAL#  "Serial",
substr(object_name,1,20) "Object",
 substr(os_user_name,1,10) "Terminal",
 substr(oracle_username,1,10) "Locker",
 nvl(lockwait,'active') "Wait",
 decode(locked_mode,
   2, 'row share',
   3, 'row exclusive',
   4, 'share',
   5, 'share row exclusive',
   6, 'exclusive',  'unknown') "Lockmode",
 OBJECT_TYPE "Type"
FROM
 SYS.V_$LOCKED_OBJECT A,
 SYS.ALL_OBJECTS B,
 SYS.V_$SESSION c
WHERE
 A.OBJECT_ID = B.OBJECT_ID AND
 C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/


. Results

      sid     Serial Object                     Terminal             Locker                            Wait Lockmode     Type
--------- ---------- -------------------------- -------------------- ------------------------ ------------- ------------ ---------
       42      30400 TB_MESSAGE                 Adminhub02           HUB_PROD         active            row exclusive     TABLE
       42      30400 TB_MESSAGE_INFORMATI       Adminhub02           HUB_PROD         active            row exclusive     TABLE
      118      50914 TB_MESSAGE                 Adminhub02           HUB_PROD         active            row exclusive     TABLE
      127      48427 TB_MESSAGE                 Adminhub02           HUB_PROD         active            row exclusive     TABLE
Ferramentas pessoais

contador de visitas