ORACLE 存储过程解及表解锁和停止执行

查看进程:

select * from v$process

根据存储过程名称查找是否被锁：

select * FROM dba_ddl_locks where name =upper('sp_1');

select * FROM dba_ddl_locks where name like upper('%sp_1%');

select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

select  s.*
from v$locked_object l, dba_objects o, v$session s, v$process p
 where l.object_id = o.object_id
   and l.session_id = s.sid
   and s.paddr = p.addr
   AND object_name= upper(''表或存储过程名字'）;

　　

查找SID:

select t.sid,t.serial# from v$session t
where t.sid=550;

杀session解锁:

alter system kill session '550,10047' immediate;

--sid,t.serial#

如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program from v$session s,v$process p where 
s.paddr=p.addr and s.sid=550 

(550就是上面的sid)

单机：
SELECT A.PID, B.SID, B.SERIAL#, C.SQL_TEXT,A.SPID
  FROM V$PROCESS A, V$SESSION B, V$SQLAREA C
 WHERE A.ADDR = B.PADDR
   AND B.SQL_ADDRESS = C.ADDRESS

rac:

SELECT A.PID, B.SID, B.SERIAL#, C.SQL_TEXT,A.SPID
  FROM GV$PROCESS A, GV$SESSION B, GV$SQLAREA C
 WHERE A.ADDR = B.PADDR
   AND B.SQL_ADDRESS = C.ADDRESS

再到linux下 kill -9 pid

杀会话

select  a.USERNAME,a.MACHINE, sql_text,'alter system kill session '''||a.SID||','||a.SERIAL#||',@'||a.INST_ID||'''immediate;' ,status 
from gV$session a inner join GV$sql b on a.sql_id=b.sql_id 
WHERE status = 'ACTIVE'

定时任务，杀掉特定服务器上执行超过一个小时的语句

create or replace procedure ks_kill_sp_timeout
    /******************************************************************
      存储过程名称：
      存储过程内容：监控存储过程是否正常
      作者姓名: 
      编写时间: 2017年4月17日
      输入参数：

      输出参数：
    *******************************************************************/
as
    v_sql varchar2(2000);
    v_count number;
begin

select count(distinct 'alter system kill session '''||a.SID||','||a.SERIAL#||',@'||a.INST_ID||''' immediate;')  into v_count
from gV$session a inner join GV$sql b on a.sql_id=b.sql_id
WHERE a.status = 'ACTIVE'
and a.MACHINE = 'db'
and ceil( (sysdate - a.SQL_EXEC_START) * 24 * 60) >= 60 ;

if v_count > 0 then
  select distinct 'alter system kill session '''||a.SID||','||a.SERIAL#||',@'||a.INST_ID||''' immediate;'  into v_sql
  from gV$session a inner join GV$sql b on a.sql_id=b.sql_id
  WHERE a.status = 'ACTIVE'
  and a.MACHINE = 'db'
  and ceil( (sysdate - a.SQL_EXEC_START) * 24 * 60) >= 60 ;
  
  dbms_output.put_line(v_sql);  
  execute immediate v_sql;
else
  dbms_output.put_line(v_count);
end if;




end;