Sunday 26 July 2015

Clearing Locks in Database as an APPS DBA

--LOCKS--
select inst_id,sid,to_char(logon_time,'dd-mon-yyyy hh24:mi:ss') from gv$session where lockwait is not null;

--- BLOCKING STATUS ---
SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL
  

  --SID DETAILS--
 select inst_id,sid,serial#,program,module,action,status,blocking_session,command,to_char(logon_time,'dd-mon-yyyy hh24:mi:ss') from gv$session where sid in(288, 1775)

 --BACKGROUND RUNNING CONCURRENT ---
 SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.FND_CONCURRENT_PROGRAMS_TL c,
    apps.fnd_user d
WHERE   a.concurrent_program_id=b.concurrent_program_id AND
    b.concurrent_program_id=c.concurrent_program_id AND
    a.requested_by=d.user_id AND
    status_code='R' order by Process_time desc;

 ---SID FROM REQ ID ---
SELECT sid
FROM v$session
WHERE paddr LIKE
(SELECT addr
FROM v$process
WHERE spid =
(SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER(18825704)
)
);

---DETAILS FROM USER ---


SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        130        620

--- KILL THE SESSION ---

STEP 1:

SQL> exec system.killsession(1508);

PL/SQL procedure successfully completed.   

STEP 2:
   
SQL>  alter system kill session '130,620,@1';

System altered.

-- CHECK FINALLY --

SQL> select sid from v$session where lockwait is not null;

no rows selected
  
  

No comments:

Post a Comment