Locks and Blocking sessions

1.DB Locks:
  ---------
set lines 200
set pages 200
spool DB_Locks.lst
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
spool off

SQL> /

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS                                                       SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------------------------------------------------------- ---------------
             543       3604      44225 Application                                                                13168
            1637       5027      34229 Application                                                                 4629
            2347       1637      34589 Concurrency                                                                  790
            2347       1772      30581 Concurrency                                                                  712
            2347       2133      30613 Concurrency                                                                  113
            2347        427      51691 Concurrency                                                                  790
            2347       6552       9531 Concurrency                                                                   73
            2347        283      32075 Concurrency                                                                  791
            2347       6336      43573 Concurrency                                                                  791
            2347       6226       6863 Concurrency                                                                  781
            2347       2598       9991 Concurrency                                                                  553
            2347       2884       6587 Concurrency                                                                  790
            2347       2908      19783 Concurrency                                                                  790
            2347       3095      15317 Concurrency                                                                  791
            2347       3301      11573 Concurrency                                                                  556
            2347       3839      28959 Concurrency                                                                  790
            2347       3958       5749 Concurrency                                                                  790
            2347       4044      13291 Concurrency                                                                  766
            2347       4279       5453 Concurrency                                                                  789
            2347       4370      36515 Concurrency                                                                  790
            2347       4813         87 Concurrency                                                                  789
            2347       4962      15327 Concurrency                                                                  556
            2347       5202      63227 Concurrency                                                                  790
            2347       5375      16397 Concurrency                                                                  790
            2347       5994      21109 Concurrency                                                                  790
            3604        111      19897 Application                                                                13165
            4692       2347      25165 Concurrency                                                                  791
            5202        543      54389 Application                                                                18975

28 rows selected.

2.SQL> select sql_id from v$session where sid=543;

SQL_ID
-------------
7xsrcn3c3ssfy

3.
SQL> select sql_text from v$sql where sql_id='7xsrcn3c3ssfy';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1



scenario 1
==========


first query
===========

Enter the request which is running long time,
Example 1:request Id=11263341


SQL> SELECT request_id,oracle_process_id,
DECODE(phase_code,'R','Running','C','Completed','P','Inactive',phase_code) Phase,
DECODE(status_code,'R','Normal','C','Normal','E','Error','Q','No Manager','T',
'Terminating','X','Terminated','G','Warning','Canceled','D',status_code) Status
FROM apps.fnd_concurrent_requests
where request_id=11263341;

REQUEST_ID ORACLE_PROCESS_ID              PHASE     STATUS
---------- ------------------------------ --------- -----------
  11263341 7694                           Running   Normal

O/p Gives u the Oracle Process ID:7694


Second query
=============

Enter the request which is running long time,
Example 1:request Id=11263341

Also gives u the Oracle Process ID...

SQL> select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',  'Warning','T','Terminating')"Status_code",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id';
Enter value for enter_conn_req_id: 11263341
old   4: from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
new   4: from apps.fnd_concurrent_requests where request_id='11263341'

ORACLE_PROCESS_ID              Status_code P Login Time
------------------------------ ----------- - ---------------------------
7694                           Running     R 07-OCT-2010=>09:36:09

select oracle_process_id ,request_id,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',  'Warning','T','Terminating')"Status_code",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where oracle_process_id='&a';

Third Query
===========

With that oracle Process ID We can find the SID and Serial# and waht it is doing ..
Oralce Procedd ID = 7694

select s.sid,s.serial#,module,s.status from v$session s,v$process p where s.paddr=p.addr and p.spid=&oracle_process_id;
Enter value for oracle_process_id: 7694
old   3: and p.spid=&oracle_process_id
new   3: and p.spid=7694

       SID    SERIAL# MODULE                                           STATUS
---------- ---------- ------------------------------------------------ --------
      3342       5626 ALTMFGINVFA                                      ACTIVE

SQL>


Fourth Query
=============

To find the Text we can find out what it is running..
USING SID=3342


SELECT sql_text FROM V$sql WHERE address IN ( SELECT sql_address FROM v$session WHERE sid =3342);

SQL> SELECT sql_text FROM V$sql WHERE address IN ( SELECT sql_address FROM v$session WHERE sid =3342);


SQL_TEXT
--------------------------------------------------------------------------------
SELECT NVL(CIC.ITEM_COST, NVL(CIC_DEF.ITEM_COST, 0)), NVL(CIC.BASED_ON_ROLLUP_FL
AG,NVL(CIC_DEF.BASED_ON_ROLLUP_FLAG, 2)), NVL(CIC.SHRINKAGE_RATE, NVL(CIC_DEF.SH
RINKAGE_RATE, 0)), (BET1.EXTENDED_QUANTITY * NVL(BET.COMPONENT_QUANTITY, 0) * (N
VL(BET.PLANNING_FACTOR, 100) / 100) ) / (DECODE(:B7 , 2, 1, DECODE(NVL(BET.COMPO
NENT_YIELD_FACTOR, 0), 0, 1, NVL(BET.COMPONENT_YIELD_FACTOR, 0) ) ) * (1 - NVL(B
ET1.SHRINKAGE_RATE, 0)) ), DECODE(BET1.EXTEND_COST_FLAG, 2, 2, DECODE(BET1.INVEN
TORY_ASSET_FLAG, 2, 2, DECODE(BET1.BASED_ON_ROLLUP_FLAG, 2, 2, DECODE(BET.INCLUD
E_IN_ROLLUP_FLAG, 1, 1, 2)))), NVL(CIC.COST_TYPE_ID, NVL(CIC_DEF.COST_TYPE_ID, B
ET1.ACTUAL_COST_TYPE_ID)), NVL(CIC.INVENTORY_ASSET_FLAG, NVL(CIC_DEF.INVENTORY_A
SSET_FLAG, 2)), BET.TOP_BILL_SEQUENCE_ID, BET.ROWID FROM CST_ITEM_COSTS CIC, CST
_ITEM_COSTS CIC_DEF, BOM_EXPLOSION_TEMP BET_MUST_HAVE, BOM_EXPLOSION_TEMP BET1,

SQL_TEXT
--------------------------------------------------------------------------------
BOM_EXPLOSION_TEMP BET WHERE BET1.GROUP_ID = :B2 AND BET1.TOP_BILL_SEQUENCE_ID =
 BET.TOP_BILL_SEQUENCE_ID AND BET1.SORT_




SQL> select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session where blocking_session is not null;

PROCESS                         SID BLOCKING_SESSION BLOCKING_INSTANCE
------------------------ ---------- ---------------- -----------------
115740                         1267             5265                 2
20969                          5265             7261                 2
21000                          6003             5265                 2
20993                          6754             5265                 2
93890                          7275             7261                 2
20131                           772             7261                 2

SQL> select sid,serial#,p2,program,module from gv$session where sid=5265;