Title: Identifying Locks Based on the Session's SQL Addresses

Author: Bobby Akbari, a DBA for Miskelly Funiture, in Jackson, Mississippi.

My script reports all of the sessions waiting for locks to be released by another session, and is based on SQL addresses.

-- This script reports on all sessions waiting (lockee) on
-- locks held by another session (locker) to be released, based
-- on the SQL address on the SGA.
-- This script will identify the lockee and the locker only
-- when the sql statement of the locker is the same as the sql
-- statement of the lockee.
-- When a different sql statement is run to query the row that is
-- being locked by the locker then this script won't do the
-- association between the two sessions; locker and lockee;
-- because the value of the sql addresses will not be the same if
-- the statements on the SGA's library cach are not the same,
-- therefor they won't have the same addresses.
-- If locker is locking a row and another user ( the lockee )
-- is trying to update the same row then on V$SESSION the
-- SQL_ADDRESS of the session lockee has the same value
-- as  PREV_SQL_ADDR of session locker.
-- And on V_$LOCKED_OBJECT the OBJECT_ID of the
-- session locker has the same value than ROW_WAIT_OBJ# -- on V$SESSION of session
-- lockee, meaning the object containing the row that is being
-- locked by locker and that the lockee is waiting for.

-- First we will create the package that will be used by the script.
-- The package is called pkg_vsession.
-- This package will focus on v$session.
-- pkg_vsession.sql
-- 29-JAN-99
-- BOBBY AKBARI.
--
create or replace package pkg_vsession as
-- Returns PREV_SQL_ADDR for a given SID.
        function f_prev_sql_addr(a_sid IN v$session.sid%type)
        return raw;
        PRAGMA RESTRICT_REFERENCES(f_prev_sql_addr,WNDS,WNPS,RNPS);
-- Returns SQL_ADDRESS for a given SID.
        function f_sql_address(a_sid IN v$session.sid%type)
        return raw;
        PRAGMA RESTRICT_REFERENCES(f_sql_address,WNDS,WNPS,RNPS);
end pkg_vsession;
/
create or replace package body pkg_vsession as
        function f_prev_sql_addr(a_sid IN v$session.sid%type)
        return raw is
        r_prev_sql_addr         raw(4):=00;
        cursor c_pkg_vsession is
                select prev_sql_addr
                from v$session
                where sid=a_sid;
        v_pkg_vsession          c_pkg_vsession%rowtype;
        begin
                open c_pkg_vsession;
                fetch c_pkg_vsession into v_pkg_vsession;
                if ( c_pkg_vsession%found ) then
                        r_prev_sql_addr:=v_pkg_vsession.prev_sql_addr;
                end if;
                close c_pkg_vsession;
                return r_prev_sql_addr;
                exception
                when others then return 'EXCEPTION f_prev_sql_addr';
        end f_prev_sql_addr;
--
        function f_sql_address(a_sid IN v$session.sid%type)
        return raw is
        r_sql_address         raw(4):=00;
        cursor c_pkg_vsession is
                select sql_address
                from v$session
                where sid=a_sid;
        v_pkg_vsession          c_pkg_vsession%rowtype;
        begin
                open c_pkg_vsession;
                fetch c_pkg_vsession into v_pkg_vsession;
                if ( c_pkg_vsession%found ) then
                        r_sql_address:=v_pkg_vsession.sql_address;
                end if;
                close c_pkg_vsession;
                return r_sql_address;
                exception
                when others then return 'EXCEPTION f_sql_address';
        end f_sql_address;
end pkg_vsession;
/

-- The following is the script itself, called m_lock_waiters:
-- m_lock_waiters.sql
-- 29-JAN-99
-- BOBBY AKBARI.
--
set linesize 110
column object_name heading 'OBJECT|NAME' format a11
column object_type heading 'OBJECT|TYPE' format a7
column locker format a7
column lockee format a7
column addr_locked heading 'SQL|ADDR|LOCKED' format a11
column addr_requested heading 'SQL|ADDR|REQUESTED' format a11
column sid_locker heading 'SESSION|ID|LOCKER' format 9999999
column waited_on heading 'WAITING|FOR|OBJ_ID' format 9999999
select l.session_id sid_locker, l.oracle_username locker,s.username lockee,
        s.ROW_WAIT_OBJ# waited_on,
        object_name,object_type,
        substr(pkg_vsession.f_prev_sql_addr(l.session_id),1,20) addr_locked,
        substr(pkg_vsession.f_sql_address(s.sid),1,20) addr_requested
from sys.v_$locked_object l,dba_objects o, v$session s
where l.object_id=o.object_id
and s.ROW_WAIT_OBJ#=l.object_id
and pkg_vsession.f_prev_sql_addr(l.session_id)=
        pkg_vsession.f_sql_address(s.sid)
order by 2
/