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:
Post a Comment