檢視資料庫中的鎖(LOCK),找出程式及SQL

 

SELECT /*+ rule */
       s.username,
       DECODE (l.TYPE,
               'TM', 'TABLE LOCK',
               'TX', 'ROW LOCK',
               NULL
              ) lock_level, o.owner, o.object_name, o.object_type, s.SID,
       s.serial#, s.terminal, s.machine, s.program, s.osuser
FROM v$session s, v$lock l, dba_objects o
WHERE l.SID = s.SID AND l.id1 = o.object_id(+) AND s.username IS NOT NULL;

 

SELECT /*+ rule */
       s.username,
       DECODE (l.TYPE,
               'TM', 'TABLE LOCK',
               'TX', 'ROW LOCK',
               NULL
              ) lock_level, o.owner, o.object_name, o.object_type, s.SID,
       s.serial#, s.terminal, s.machine, s.program, s.osuser, q.sql_text
FROM v$session s, v$lock l, dba_objects o, v$open_cursor c, v$sql q
WHERE l.SID = s.SID
    AND l.id1 = o.object_id(+)
    AND s.username IS NOT NULL
    AND s.sql_address = c.address
    AND s.sql_hash_value = c.hash_value
    AND q.address = s.sql_address
    AND q.hash_value = s.sql_hash_value; 
   

 

alter system kill session 'SID,SERIAL#';