UNlock dead lock

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;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/269025/viewspace-1264979/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/269025/viewspace-1264979/