create or replace procedure KILL_DEADLOCK IS
N NUMBER;
Begin
for UK_LOOP IN (SELECT c.owner,
c.object_name,
c.object_type,
fu.user_name locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time,
vs.module,
vs.machine,
vs.osuser,
vlocked.oracle_username,
vs.sid,
vp.pid,
vp.spid AS os_process,
vs.serial#,
vs.status,
vs.saddr,
vs.audsid,
vs.process,
f.full_name,
'alter system kill session ' || '''' || vs.SID || ',' ||
vs.serial# || '''' kill_id
FROM apps.fnd_logins fl,
apps.fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c,
apps.per_people_f f
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
and fu.user_name = f.employee_number(+)
AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status, 'XX') != 'KILLED'
and c.object_name like 'PRA%'
and vs.sid in (select sid from v$lock where block <> 0) --It is dead lock
) LOOP
N:=N+1;
EXECUTE IMMEDIATE UK_LOOP.kill_id;
DBMS_OUTPUT.PUT_LINE('Total '||n||' lines unlocked');
END LOOP;
END;
/
N NUMBER;
Begin
for UK_LOOP IN (SELECT c.owner,
c.object_name,
c.object_type,
fu.user_name locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time,
vs.module,
vs.machine,
vs.osuser,
vlocked.oracle_username,
vs.sid,
vp.pid,
vp.spid AS os_process,
vs.serial#,
vs.status,
vs.saddr,
vs.audsid,
vs.process,
f.full_name,
'alter system kill session ' || '''' || vs.SID || ',' ||
vs.serial# || '''' kill_id
FROM apps.fnd_logins fl,
apps.fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c,
apps.per_people_f f
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
and fu.user_name = f.employee_number(+)
AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status, 'XX') != 'KILLED'
and c.object_name like 'PRA%'
and vs.sid in (select sid from v$lock where block <> 0) --It is dead lock
) LOOP
N:=N+1;
EXECUTE IMMEDIATE UK_LOOP.kill_id;
DBMS_OUTPUT.PUT_LINE('Total '||n||' lines unlocked');
END LOOP;
END;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/269025/viewspace-1264979/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/269025/viewspace-1264979/