Oracle 找锁住的表并解锁

Sql代码

1. select a.sid,b.serial#,b.PROGRAM,b.USERNAME,b.MACHINE,b.OSUSER,b.LOGON_TIME,b.LAST_CALL_ET,b.process,decode(a.type, 
2.         'MR', 'Media Recovery', 
3.         'RT','Redo Thread', 
4.         'UN','User Name', 
5.         'TX', 'Transaction', 
6.         'TM', 'DML', 
7.         'UL', 'PL/SQL User Lock', 
8.         'DX', 'Distributed Xaction', 
9.         'CF', 'Control File', 
10.         'IS', 'Instance State', 
11.         'FS', 'File Set', 
12.         'IR', 'Instance Recovery', 
13.         'ST', 'Disk Space Transaction', 
14.         'TS', 'Temp Segment', 
15.         'IV', 'Library Cache Invalida-tion', 
16.         'LS', 'Log Start or Switch', 
17.         'RW', 'Row Wait', 
18.         'SQ', 'Sequence Number', 
19.         'TE', 'Extend Table', 
20.         'TT', 'Temp Table', 
21.         'Unknown') LockType, 
22.         rtrim(object_type) || ':' || rtrim(owner) || '.' || object_name object_name, 
23.         decode(lmode,   0, 'None', 
24.                 1, 'Null', 
25.                 2, 'Row-S', 
26.                 3, 'Row-X', 
27.                 4, 'Share', 
28.                 5, 'S/Row-X', 
29.                 6, 'Exclusive',        'Unknown') LockMode, 
30. decode(request, 0, 'None', 
31.                 1, 'Null', 
32.                 2, 'Row-S', 
33.                 3, 'Row-X', 
34.                 4, 'Share', 
35.                 5, 'S/Row-X', 
36.                 6, 'Exclusive', 'Unknown') RequestMode, 
37.                 ctime, block b 
38. from v$lock a,all_objects c,v$session b 
39. where a.sid > 6 and a.id1 = c.object_id and a.sid = b.sid; 

select a.sid,b.serial#,b.PROGRAM,b.USERNAME,b.MACHINE,b.OSUSER,b.LOGON_TIME,b.LAST_CALL_ET,b.process,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,
        rtrim(object_type) || ':' || rtrim(owner) || '.' || object_name object_name,
        decode(lmode,   0, 'None',
                1, 'Null',
                2, 'Row-S',
                3, 'Row-X',
                4, 'Share',
                5, 'S/Row-X',
                6, 'Exclusive',        'Unknown') LockMode,
decode(request, 0, 'None',
                1, 'Null',
                2, 'Row-S',
                3, 'Row-X',
                4, 'Share',
                5, 'S/Row-X',
                6, 'Exclusive', 'Unknown') RequestMode,
                ctime, block b
from v$lock a,all_objects c,v$session b
where a.sid > 6 and a.id1 = c.object_id and a.sid = b.sid;

生成Kill语句

Sql代码

1. select 'alter system kill session '''||a.sid||','||b.serial#||''';' 
2. from v$lock a,all_objects c,v$session b 
3. where a.sid > 6 and a.id1 = c.object_id and a.sid = b.sid;