Oracle _ uses the stored procedure to kill the session to solve the table lock problem

I believe everyone has encountered the lock table. I think everyone knows the unlock method, but the killing processes are sometimes low-level. We use stored procedures to implement this function, it becomes very simple.

1. Create a view and query process information,

create or replace view mysession asselect b.sid,b.serial# from v$lock a,v$session b where a.SID = b.SID and status='INACTIVE' --and program<>'plsqldev.exe'  order by machine;

2. Create a stored procedure and cycle the Data tried above to kill one by one,

create or replace procedure kill_session is  v_sid    varchar2(30);  v_serial varchar2(30);  v_sql    varchar2(1000);  TYPE DyData IS REF CURSOR;  rows DyData;begin  v_sql := 'select * from mysession ';  OPEN rows FOR v_sql;  LOOP    FETCH rows      into v_sid, v_serial;      v_sql := 'alter system kill session ''' || v_sid || ',' || v_serial || '''';    execute immediate v_sql;    --dbms_output.put_line(v_userid || '__' || v_user || '__' || v_pwd);      EXIT WHEN rows%NOTFOUND;  END LOOP;end kill_session;

3. Execute the Stored Procedure

begin  -- Call the procedure  kill_session;end;

Note that sometimes the process may be killed. You only need to modify the view in the first step and exclude yourself.

[Csharp]View plaincopy

[End]