Thursday, March 8, 2018

How to clear/kill all locks held on one database package in Oracle


In scenarios where users are not able to access a package due to locks held on it,below pl/sql will help clear those sessions


declare
cursor c1 is
SELECT a.osuser,a.username,a.action, a.sid, a.serial# sl,b.spid, a.status,a.last_call_et,to_char(logon_time,'DD-MON HH24: MI:SS') logon_time, a.machine,
a.program FROM v$session a,v$process b WHERE a.paddr=b.addr and sid in (select session_id from dba_dml_locks where name ='Package Name')
and osuser not in ('list of excluded_users')
and sid = 123;

    L_QRY       varchar2(3000);
    L_ID          varchar2(3000);
begin
    for i in c1
    loop
        L_ID := i.sid||','||i.sl;
        dbms_output.put_line('Loop starts for :'||L_ID);
        L_QRY := 'alter system disconnect session '''||L_ID||' immediate''''';
        dbms_output.put_line('L_QRY :'||L_QRY);
    
        execute immediate L_QRY;
    end loop;
exception
    when others then
    dbms_output.put_line('ERRM:'||SQLERRM);
end;

No comments: