Hi Experts,
How I can Killing locked table session via SQL procedure.
I have got below procedure with which is failing, please advise.
create or replace procedure kill_session AS
DECLARE
CURSOR c IS
SELECT
OBJ.object_name,
OBJ.object_type,
SS.sid,
SS.serial#,
LOBJ.oracle_username,
LOBJ.os_user_name,
LOBJ.locked_mode,
ss.MODULE,
ss.PROGRAM,
ss.PROCESS,
ACTION
FROM
all_objects Obj,
v$locked_object Lobj,
v$session SS
WHERE
OBJ.object_id = LOBJ.object_id and
SS.sid = LOBJ.session_id
and obj.object_name like 'MSC%' -- an MSC table is locked
and ss.module like '%MSCFNSCW%' -- it is locked from the ASCP planner workbench form;
c_row c%ROWTYPE;
l_sql VARCHAR2(100);
BEGIN
OPEN c;
LOOP
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
l_sql := 'alter system kill session '''||c_row.SID||','||c_row.serial#||'''';
EXECUTE IMMEDIATE l_sql;
END LOOP;
CLOSE c;
END;
/
Thanks,
Abdul