Find Locks or Identifying sessions holding or acquiring the lock

Finding Locked Objects : (Identifier for session holding or acquiring the lock)

For more info regarding locks : search in google :v$lock.

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

     id1, id2, lmode, request, type

     FROM V$LOCK

     WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)

     ORDER BY id1, request;

SESS                                                    ID1        ID2

------------------------------------------------ ---------- ----------

     LMODE    REQUEST TY

---------- ---------- --

Holder: 336                                         1376264      27170

         6          0 TX

Waiter: 32                                          1376264      27170

         0          6 TX

Find the serial number of sid from v$session; then kill the session  and release the lock ?

Select serial# from v$session where sid=336;


O/p
Serial#

121

Now Kill the session

alter system kill session '336,121' immediate;

Which Sid is blocking which Sid ?

select l1.sid,'IS BLOCKING',l2.sid

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2;

 SID 'ISBLOCKING        SID

---------- ----------- ----------

336 IS BLOCKING         32

Show sid blocking plus machine name also ?

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;

BLOCKING_STATUS

--------------------------------------------------------------------------------

APPS@maxindia-9a85e7 ( SID=336 )  is blocking APPS@erpdev ( SID=32 )

Running RDA Diagnostics on E-Business R12

Sometimes, when you have a Service Request open with Oracle Support,

they can ask you to run Oracle Diagnostics Tool for some products on your R12 Application.

Two Methods of Running RDA :

1^st Method

Here is a simple example of running for GME and JL products:

########################################

cd  $IZU_TOP/bin

./rda.sh -vdCRP -e APPL_SHORT='GME' ACT

./rda.sh -vdCRP -e APPL_SHORT='AR' ACT

########################################

Upload the zip file to Oracle Support.

Location of Zip file: $IZU_TOP/bin/output

2nd Method

E.g Running RDA for GME

Steps :

Login to EBS as sysadmin or with responsibility as Application Diagnostics

Click on Diagnose.

From Diagnostic Tests click on Select Application

From Select Application , Select GME.

Now click on RDA and click Execute Button

Now Input Responsibility id,Application shortname ,apps username and apps password.

And click on Submit Button.

RDA will get executed and will take approx 5 mins or so.