Sunday 24 November 2013

Oracle database tuning: DBMS_SQLTUNE

1- Create tuning task:
DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sql_text := 'SELECT /*+ ORDERED */ * '                      ||
                'FROM employees e, locations l, departments d ' ||
                'WHERE e.department_id = d.department_id AND '  ||
                     'l.location_id = d.location_id AND '      ||
                     'e.employee_id < :bnd';
 
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sql_text,
         bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => 'USERADV',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'my_sql_tuning_task01',
         description => 'Task to use for tuning a query');
END;
2- Execute tuning task:
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task01' );
END;

3- Report tuning task:
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task01')
  FROM DUAL;

Oracle database hang new connections not allowed

what is sqlplus -prelim : This is the backdoor entry method to access database (only SGA) in a hung situation 
 where no other methods are allowing to create new sessions in database.
 This will not create any session in DB rather connect to SGA directly ...



#lsnrctl status  >> Fine

#ps -ef | grep ora_   >> Fine


# sqlplus / as sysdba  >> database not accepting

# sqlplus /nolog
SQL> set _prelim on
SQL> conn / as sysdba
Prelim connection established
SQL> oradebug setmypid
SQL> oradebug hanganalyze 12

SQL> oradebug TRACEFILE_NAME
/oracle/diag/rdbms/dbtalip/TALIPDB/trace/TALIPDB_ora_32239.trc


cat /oracle/diag/rdbms/dbtalip/TALIPDB/trace/TALIPDB_ora_32239.trc 
*** 2012-06-11 12:14:02.870
================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): dbtalip.talipdb
  oradebug_node_dump_level: 12
  analysis initiated by oradebug
=================================================

Search inside the trace file you will find the process number which hanged the database:
os id: 1981process id: 129, oracle@fmsdb (TNS V1-V3)
session id: 174
session serial #: 147681


# kill -9 1981

Done .....................

Friday 15 November 2013

Oracle databse: What is rowid

Every row in every table has a physical address. The address of a row is determined from a combination of
the following:
• Datafile number
• Block number
• Location of the row within the block
• Object number

You can display the address of a row in a table by querying the ROWID pseudo-column—for example:
SQL> select rowid, emp_id from emp;
Here’s some sample output:
ROWID EMP_ID
------------------ ----------
AAAFWXAAFAAAAlWAAA 1

The ROWID pseudo-column value isn’t physically stored in the database. Oracle calculates its value when you query it. The ROWID contents are displayed as base-64 values that can contain the characters A–Z, a–z, 0–9, +, and /. You can translate the ROWID value into meaningful information via the DBMS_ROWID package. For example, to display the file number, block number, and row number in which a row is stored, issue this statement:

select
emp_id
,dbms_rowid.rowid_relative_fno(rowid) file_num
,dbms_rowid.rowid_block_number(rowid) block_num
,dbms_rowid.rowid_row_number(rowid) row_num
from emp;

Here’s some sample output:
EMP_ID FILE_NUM BLOCK_NUM ROW_NUM
---------- ---------- ---------- ----------
2960       4        144 1     26
2961       4        144 1     27
You can use the ROWID value in the SELECT and WHERE clauses of a SQL statement. In most cases, the
ROWID uniquely identifies a row. However, it’s possible to have rows in different tables that are stored in
the same cluster and so contain rows with the same ROWID.

Wednesday 13 November 2013

unix.sql

set echo off
set serveroutput on size 200000
set verify off
set feedback off
accept uxproc prompt 'Enter Unix process id: '
DECLARE
  v_sid number;
  vs_cnt number;
  s sys.v_$session%ROWTYPE;
  p sys.v_$process%ROWTYPE;
  cursor cur_c1 is select sid from sys.v_$process p, sys.v_$session s  where  p.addr  = s.paddr and  (p.spid =  &uxproc or s.process = '&uxproc');
BEGIN
    dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.v_$process p, sys.v_$session s  where  p.addr  = s.paddr and  (p.spid =  &uxproc or s.process = '&uxproc');
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');
dbms_output.put_line('=====================================================================');
open cur_c1;
LOOP  
      FETCH cur_c1 INTO v_sid;  
   EXIT WHEN (cur_c1%NOTFOUND);  
select * into s from sys.v_$session where sid  = v_sid;
  select * into p from sys.v_$process where addr = s.paddr;
dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);
  dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.program);
  dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);
  dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);
  dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);
  dbms_output.put_line('Ora User    : '|| s.username);
dbms_output.put_line('Details     : '|| s.action||' - '||s.module);
  dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
  dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
  dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
  dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99990.0') || ' min');
  dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
  dbms_output.put_line('Latch Spin  : '|| nvl(p.latchspin, 'NONE'));
  dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext  where HASH_VALUE = s.sql_hash_value order by piece)
loop
    dbms_output.put_line(chr(9)||c1.sql_text);
  end loop;
dbms_output.put_line('Previous SQL statement:');
  for c1 in ( select * from sys.v_$sqltext  where HASH_VALUE = s.prev_hash_value order by piece)
loop
    dbms_output.put_line(chr(9)||c1.sql_text);
  end loop;
dbms_output.put_line('Session Waits:');
  for c1 in ( select * from sys.v_$session_wait where sid = s.sid)
loop
    dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
  end loop;
--  dbms_output.put_line('Connect Info:');
--  for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
--    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
--  end loop;
  dbms_output.put_line('Locks:');
  for c1 in ( select  /*+ RULE */ decode(l.type,
          -- Long locks
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks
                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH  ',
                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',
                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',
                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',
                      'TYPE='||l.type) type,
         decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                       4, 'S',    5, 'RSX',  6, 'X',
                       to_char(l.lmode) ) lmode,
          decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                         4, 'S', 5, 'RSX', 6, 'X',
                         to_char(l.request) ) lrequest,
        decode(l.type, 'MR', o.name,
                      'TD', o.name,
                      'TM', o.name,
                      'RW', 'FILE#='||substr(l.id1,1,3)||
                            ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
                      'WL', 'REDO LOG FILE#='||l.id1,
                      'RT', 'THREAD='||l.id1,
                      'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
                      'ID1='||l.id1||' ID2='||l.id2) objname
        from  sys.v_$lock l, sys.obj$ o
        where sid   = s.sid
          and l.id1 = o.obj#(+) )
loop
  dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
  end loop;
dbms_output.put_line('=====================================================================');
END LOOP;
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');
dbms_output.put_line('Please scroll up to see details of all the sessions.');
dbms_output.put_line('=====================================================================');
  close cur_c1;
exception
    when no_data_found then
      dbms_output.put_line('Unable to find process id &&uxproc!!!');
 dbms_output.put_line('=====================================================================');
      return;
    when others then
      dbms_output.put_line(sqlerrm);
      return;
END;
/
undef uxproc
set heading on
set verify on
set feedback on
set echo on

Oracle FNDLOAD Scripts

1. Lookups
-- -------------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

2. Concurrent Program
-- ----------------------------- 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

3. Profile
-- --------- 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

4. Request Set and Link
-- ------------------------------ 

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

5. FND Message
-- ---------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

6. D2K FORMS
-- ------------------ 

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME" 
       
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

7. Form Function
-- --------------------- 

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

8. Alerts
-- --------- 

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

9. Value Set
-- -------------- 

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

10. Data Definition and Associated Template
--- ---------------------------------------------------------- 

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

11. DATA_TEMPLATE (Data Source .xml file)
--- ---------------------------------------------------------- 

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

12. RTF TEMPLATE (Report Layout .rtf file)
--- ------------------------------------------------------- 

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME


java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

Monday 4 November 2013

All About Blocking Locks in Oracle

A  very  nice  post  has  been  written  by  Oracle  Ace Director  Arup Nanda  about the  Blocking  Locks . It  is  so simple  and  brilliantly explained . Check  the  below  link  and  enjoy  reading  about  basic  concepts behind locks . Before going to this link , read my previous post on ITL ( Interested Transaction List)  for better understading of this post .
http://arup.blogspot.in/2011/01/how-oracle-locking-works.html

Another  Brilliant  demo  on  Blocking  lock is  explained  by  "Natalka Roshak" .  She  has so wonderfully  explained .  Enjoy reading this link too. 

Here,   added  few  more query on  locks along  with  it's output . For  demo purpose , he has created  a  table "blck_tab"  and  locked this  table manually  and accessing this locked table from other sessions .Let's have a look .

SQL> create table blck_tab (id number , name varchar(22));
Table created.

SQL> insert into blck_tab values(1,'abc');
1 row created.

SQL> insert into blck_tab values(2,'xyz');
1 row created.

SQL> insert into blck_tab values(3,'pqr');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from blck_tab  for update ;             ----- Table Locked
        ID               NAME
----------    ----------------------
         1                   abc
         2                   xyz
         3                   pqr

Session 1 : 

SQL>conn test1/test1
SQL> update test.blck_tab set name='qwert' where id=3;
--->>  Waiter   <---- nbsp="" span="">

Session 2 : 

SQL> conn hr/hr
SQL> delete test.blck_tab where id=1;
--->>  Waiter   <---- nbsp="" span="">


Query to check locks :
(For Non-RAC)
SQL> SELECT  SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
  2  DECODE(REQUEST, 0, 'NO','YES' ) WAITER
  3  FROM   V$LOCK
  4  WHERE  REQUEST > 0 OR BLOCK > 0
  5  ORDER BY block DESC;
   SID     BLOCKER      WAITER
------     ------------       ----------
    37            YES                 NO
    34             NO                 YES
    31             NO                  YES
or 

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from gv$lock l1, gv$lock l2
  3  where l1.block >0 and l2.request > 0
  4  and l1.id1=l2.id1
  5  and l1.id2=l2.id2;
       SID    'ISBLOCKING'         SID
----------  ------------------      --------
        37      IS BLOCKING          31
        37      IS BLOCKING          34

For RAC 
SQL>select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK>0 and l2.request > 0and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;

BLOCKING_STATUS
------------------------------------------------------------------------------------------------------------
TEST@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=37 )  is blocking TEST1@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=34 )

TEST@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=37 )  is blocking HR@WORKGROUP\NEERAJ-7D69D37B ( INST=1 SID=31 )


Expanded Lock  Query
===============
SQL> SELECT vs.username,  vs.osuser,   vh.sid locking_sid,  vs.status status,
 vs.module module,   vs.program program_holding,   jrh.job_name,   vsw.username,
 vsw.osuser,   vw.sid waiter_sid,   vsw.program program_waiting,  jrw.job_name,
 'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command"
FROM  v$lock vh,  v$lock vw,  v$session vs,  v$session vsw,
 dba_scheduler_running_jobs jrh,
 dba_scheduler_running_jobs jrw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid
 AND vh.sid = jrh.session_id(+)
 AND vw.sid = jrw.session_id(+);




Blocking locks with Sid and SQl 

SQL> set lines 200
column pu format a8 heading 'O/S|login|ID' justify left
column su format a15 heading 'Oracle/User ID' justify left
column prog format a15 heading 'Program' justify left
column machine format a15 heading 'machine' justify left
column stat format a8 heading 'Session|Status' justify left
column sser format 999999 heading 'Oracle|Serial|No' justify right
column txt format a28 word heading 'SQL TEXT' 
column RUNT format a15 word heading 'Run Time' 
set pagesize 1000
select
 s.username su,
 s.program prog,
 s.sid sid,
 lpad(p.spid,7) pid,
 substr(sa.sql_text,1,2000) txt,
 ltrim(to_char(floor(s.last_call_et/3600),'00009')) ||':'
 || ltrim(to_char(floor(mod(s.last_call_et,3600)/60),'09')) ||':'
 || ltrim(to_char(mod(s.last_call_et,60),'09')) RUNT
from v$process p,
 v$session s,
 v$sqlarea sa
where p.addr = s.paddr
 and s.username is not null
 and s.sql_address=sa.address(+)
 and s.sql_hash_value=sa.hash_value(+)
 and s.sid in (SELECT   SID FROM     V$LOCK WHERE    REQUEST > 0 OR BLOCK > 0)
order by 1,2
/
Oracle/User ID  Program           SID     PID          SQL TEXT                                  Run Time
--------------- ---------------          ------- -------   ----------------------------                ---------------
HR              sqlplus.exe             31        2812    delete test.blck_tab where         00001:32:11
                                                                                 id=1

TEST            sqlplus.exe             37    1928                                                          00000:06:35
TEST1           sqlplus.exe             34    1880       update test.blck_tab set            00002:11:34
                                                                            name='qwert' where id=3


Blocker/Waiter object details

SQL> col object_name for a28
SQL> col owner for a15
SQL> select do.owner,do.object_name , row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
  2  from
  3  v$session s,
  4  dba_objects do
  5  where sid in (select l2.sid blocking 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) and
  6  s.ROW_WAIT_OBJ# = do.OBJECT_ID;

OWNER           OBJECT_NAME    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- -------------- ------------- -------------- --------------- ------------- ------------------
TEST            BLCK_TAB        74697              4             524             2          AAASPJAAEAAAAIMAAC

TEST            BLCK_TAB        74697              4             524             0          AAASPJAAEAAAAIMAAA


Note for RAC  use  gv$ instead of v$ .

Number of Visitors