Oracle数据库表查看锁以及解锁SQL(很好,很好用)

Oracle数据库表查看锁以及解锁SQL#

//查询被锁的表

select A.sid, b.serial#,

decode(A.type, 

    'MR', 'Media Recovery', 

    'RT','Redo Thread', 

    'UN','User Name', 

    'TX', 'Transaction', 

    'TM', 'DML', 

    'UL', 'PL/SQL User Lock', 

    'DX', 'Distributed Xaction', 

    'CF', 'Control File', 

    'IS', 'Instance State', 

    'FS', 'File Set', 

    'IR', 'Instance Recovery', 

    'ST', 'Disk Space Transaction', 

    'TS', 'Temp Segment', 

    'IV', 'Library Cache Invalida-tion', 

    'LS', 'Log Start or Switch', 

    'RW', 'Row Wait', 

    'SQ', 'Sequence Number', 

    'TE', 'Extend Table', 

    'TT', 'Temp Table', 

    'Unknown') LockType, 

c.object_name, 

b.username, 

b.osuser, 

decode(a.lmode,   0, 'None', 

            1, 'Null', 

            2, 'Row-S', 

            3, 'Row-X', 

            4, 'Share', 

            5, 'S/Row-X', 

            6, 'Exclusive', 'Unknown') LockMode, 

B.MACHINE,D.SPID 

from v$lock a,v$session b,all_objects c,V$PROCESS D 

where a.sid=b.sid and a.type in ('TM','TX') 

and c.object_id=a.id1 

AND B.PADDR=D.ADDR

//用于解锁

alter system kill session'SID,SERIAL#'