Oracle锁表处理

Oracle锁表处理

1、查看被锁表及进程id，序列id

select b.username, c.object_name, b.sid, b.serial#, 
logon_time

  from v$locked_object a, v$session b, dba_objects c

 where a.session_id = b.sid

   and c.object_id = a.object_id

 order by b.logon_time;

2、生成杀死进程的sql

select distinct a

  from (select 'alter system kill session ''' || b.sid || 
',' || b.serial# ||''';' a
 from v$locked_object a, v$session b, dba_objects c
 where a.session_id = b.sid
and c.object_id = a.object_id
 order by b.logon_time);

3、查看连接的用户

select sid, serial#,username from v$session where username 
is not null;

4、删除指定连接的用户

select distinct a

  from (select 'alter system kill session ''' || b.sid || 
',' || b.serial# ||''';' a
 from v$session b
where username = 'ISS');