ORACLE 锁表 解锁

(1)锁表查询的代码有以下的形式：

select count(*) from v$locked_object;select * from v$locked_object;

(2)查看哪个表被锁

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

(4)查看是那个语句引起的

select l.session_id sid,        s.serial#,        l.locked_mode,        l.oracle_username,        s.user#,        l.os_user_name,        s.machine,        s.terminal,        a.sql_text,        a.action   from v$sqlarea a, v$session s, v$locked_object l where l.session_id = s.sid    and s.prev_sql_addr = a.address order by sid, s.serial#;</pre></code>

(3)查看是哪个session引起的

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time

(4)杀掉对应进程
执行命令：

alter system kill session'1025,41';

其中1025为sid,41为serial#.

——批量杀

declare cursor mycur is  select b.sid,b.serial#  　　from v$locked_object a,v$session b  　　where a.session_id = b.sid group by b.sid,b.serial#;  　　begin    for cur in mycur      loop         execute immediate ( 'alter system  kill session  '''||cur.sid || ','|| cur.SERIAL# ||''' ');       end loop;  end;