1、查看被锁住的会话
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;
上图表确实被锁/
2、查看具体信息
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;
3、解锁
create or replace procedure proc_test(
--参数区域
)
is
--变量区域
begin
--执行区域
v_sql:='alter system kill session:1,:2';
execute immediate v_sql using '2269','5556';
end proc_test;
--其中2269是sid 5556是serial#
create or replace procedure proc_test(
--参数区域
)
is
--变量区域
--sql脚本
v_sql varchar2(2000) :='';
--记录学生数量
v_num number;
begin
--执行区域
-- execute immediate用法1:立刻执行sql语句
v_sql := 'create or replace view myview as select id,name from student';
execute immediate v_sql;
--- execute immediate用法2:立刻执行sql语句,并赋值给某个变量
v_sql := 'select count(1) from student';
execute immediate v_sql into v_num;
-- execute immediate用法3:带参数的sql
v_sql:='select * from student t where t.name=:1 and t.age=:2';
execute immediate v_sql using 'ZhangSan',23;
end proc_test;
/
4、解锁遇到的问题
用alert system kill session的方式杀会话,会话会被标注为killed状态,但不一定会立即释放。
所以对于确信可以杀掉的会话,
建议在操作系统级别使用kill -9的方式,简单粗暴又高效
windows呢,可以使用orakill sid spid的方式
v$process中的spid,是系统进程号。
贴个脚本给你,查杀会话用的,具体条件你自己筛选:
select s.USERNAME,
'alter system kill session '||''''||S.sid|| ','||S.serial#||'''' || ';',
'kill -9 ' || p.SPID,
s.STATUS,
s.SQL_ID,
s.STATE,
s.EVENT,
s.MACHINE,
sq.SQL_TEXT
from v$session s, v$sqlarea sq, v$process p
where s.PADDR = p.ADDR
and s.SQL_ID = sq.SQL_ID(+)
AND S.STATUS='ACTIVE'
--and s.SID = 449
and s.USERNAME ='DB_ZR'
--and s.EVENT like '%db file sequential read%'
declare cursor mycur is
select b.SID,b.SERIAL#
from v$locked_object a,v$session b
where a.SESSION_ID = b.SID;
begin
for cur in mycur
loop
execute immediate ('alter system kill session '''||cur.SID ||','||cur.SERIAL#||''' ');
end loop;
end;