Oracle 9, 10g, 11g - Each active session

Oracle 9, 10g, 11g - Each active session

Last updated 04 September 2008 with Streams monitoring

select STATUS, wait_event, QC_SLAVE, PARALLEL_SESS, QC_SID,
SID, USERNAME, OSUSER, MACHINE, TERMINAL,
PROGRAM, LOGON_TIME, SERVER, SPID, PROCESS, SERIAL#,
TYPE, MODULE, ACTION, SCHEMANAME,
CURRENT_USER, TEMP_MB_USED, locks_blocking, locks_not_blocking, waiting_count, holding_count,
IO_block_gets, IO_consistent_gets, IO_physical_reads, IO_block_changes,
IO_consistent_changes,
to_char(PGA_USED_MEM_MB, 'FM99990.09') PGA_USED_MEM_MB,
to_char(PGA_ALLOC_MEM_MB, 'FM99990.09') PGA_ALLOC_MEM_MB,
to_char(PGA_MAX_MEM_MB, 'FM99990.09') PGA_MAX_MEM_MB,
INACTIVITY_DAYS, OPEN_CURSORS, PARAM_OPEN_CURSORS, Param_sess_cached_cursors,
to_char(PERC_OPEN_CURSORS, 'FM99990.09') PERC_OPEN_CURSORS,
to_char(open_cursors_and_cache_closed, 'FM99990.09') open_cursors_and_cache_closed,
PLAN_VARIATIONS,
SQL_HASH_VALUE, trim(to_char(SQL_ADDRESS||' ')) SQL_ADDRESS,
trim(to_char(saddr||' ')) saddr, KILL_SESSION,
START_TRACE, STOP_TRACE,
KILL_UNIX_PROCESS, undo_records, undo_start_time,
trim(STREAM_INFO || ' ' || STREAM_CAPTURE_INFO || ' ' || STREAM_APPLY_READER_INFO || ' ' || STREAM_APPLY_COORDINATOR) stream,
STREAM_CAPTURE_PROBLEM stream_status from (
select a.*
, (select event from v$session_wait where sid = a.sid and not event in ( --select event from perfstat.stats$idle_event
'AQ Proxy Cleanup Wait',
'ASM background timer',
'DBRM Logical Idle Wait',
'DIAG idle wait',
'EMON idle wait',
'EMON slave idle wait',
'IORM Scheduler Slave Idle Wait',
'KSV master wait',
'LNS ASYNC archive log',
'LNS ASYNC dest activation',
'LNS ASYNC end of log',
'LogMiner: client waiting for transaction',
'LogMiner: generic process sleep',
'LogMiner: reader waiting for more redo',
'LogMiner: slave waiting for activate message',
'LogMiner: waiting for processes to soft detach',
'LogMiner: wakeup event for builder',
'LogMiner: wakeup event for preparer',
'LogMiner: wakeup event for reader',
'MRP redo arrival',
'Null event',
'PING',
'PX Deq Credit: need buffer',
'PX Deq Credit: send blkd',
'PX Deq: Execute Reply',
'PX Deq: Execution Msg',
'PX Deq: Par Recov Execute',
'PX Deq: Signal ACK',
'PX Deq: Table Q Normal',
'PX Deq: Table Q Sample',
'PX Deque wait',
'PX Idle Wait',
'Queue Monitor Shutdown Wait',
'Queue Monitor Slave Wait',
'Queue Monitor Wait',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data from client',
'STREAMS apply coord waiting for slave message',
'STREAMS apply slave idle wait',
'STREAMS apply slave waiting for coord message',
'STREAMS capture process filter callback wait for ruleset',
'STREAMS fetch slave waiting for txns',
'STREAMS waiting for subscribers to catch up',
'Space Manager: slave idle wait',
'Streams AQ: RAC qmn coordinator idle wait',
'Streams AQ: deallocate messages from Streams Pool',
'Streams AQ: delete acknowledged messages',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'Streams capture: waiting for archive log',
'Streams fetch slave: waiting for txns',
'class slave wait',
'client message',
'cmon timer',
'dispatcher timer',
'fbar timer',
'gcs for action',
'gcs remote message',
'ges remote message',
'i/o slave wait',
'jobq slave wait',
'knlqdeq',
'lock manager wait for remote message',
'master wait',
'null event',
'parallel query dequeue',
'parallel recovery coordinator waits for slave cleanup',
'parallel recovery slave idle wait',
'parallel recovery slave next change',
'parallel recovery slave wait for change',
'pipe get',
'pmon timer',
'pool server timer',
'queue messages',
'rdbms ipc message',
'slave wait',
'smon timer',
'virtual circuit status',
'wait for activate message',
'wait for unread message on broadcast channel',
'wakeup event for builder',
'wakeup event for preparer',
'wakeup event for reader',
'wakeup time manager',
'watchdog main loop'
)) wait_event
, (select decode(a.sid, m.sid, '************', '') from v$mystat m where rownum=1) current_user
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', TRUE); end;') start_trace
, ('begin '|| '/*Spid is '||spid||'*/ '||' dbms_system.set_sql_trace_in_session('||sid||', '||serial#||', FALSE); end;') stop_trace
, ('ALTER SYSTEM DISCONNECT SESSION ''' || sid ||','||serial#||''' IMMEDIATE/*spid='||spid||'*/') kill_session
, ('kill -9 '||spid) kill_unix_process
, (select count(*) from v$lock where sid = a.sid and block != 0) locks_blocking
, (select count(*) from v$lock where sid = a.sid and block = 0) locks_not_blocking
, (open_cursors * 100 / param_open_cursors) perc_open_cursors
--Is the session using a query with a variated plan?
, (select count(*) from (
select hash_value, count(*) from (
select hash_value, plan_hash_value from v$sql group by hash_value, plan_hash_value
) group by hash_value having count(*) > 1
) where hash_value = sql_hash_value) plan_variations
-- R O L L B A C K
, (select decode(sort_space_mb, null, 'No temporary used',
sort_space_mb || 'MB, ' || to_char(sort_space_mb * 100 / (sum(bytes_used)/1024/1024), 'FM90.99')||'% of used, '||
to_char(sort_space_mb * 100 / (sum(bytes_used + bytes_free)/1024/1024), 'FM90.99')||'% of ' || (sum(bytes_used + bytes_free)/1024/1024) ||'MB')
from v$temp_space_header) temp_MB_used
from (
select
s.status, s.saddr
, nvl((select decode(px.qcinst_id,NULL, 'Master(QC)', 'Slave') from v$px_session px where px.sid = s.sid), 'Master') QC_Slave
, (select decode(count(*), 0, 0, count(*)-1) from v$px_session px where px.qcsid=(select qcsid from v$px_session where sid =s.sid)) parallel_sess
, (select decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) from v$px_session px where px.sid = s.sid) QC_SID
, (select spid from v$process where addr = paddr) spid
, (select value from v$parameter where name = 'open_cursors') param_open_cursors
, (select value from v$parameter where name = 'session_cached_cursors') Param_sess_cached_cursors
, s.sid, decode(s.username, null, 'Server process', s.username) username
, osuser, machine, terminal, program
, logon_time, to_char((last_call_et / 86400), 'FM99990.09') inactivity_days, server, process, s.serial#, type, s.module, s.action,
resource_consumer_group,
sql_hash_value, sql_address, schemaname
--ORACLE 8 MUST COMMENT ALL begin
, (select pga_used_mem/1024/1024 from v$process where addr = paddr) pga_used_mem_MB
, (select pga_alloc_mem/1024/1024 from v$process where addr = paddr) pga_alloc_mem_MB
, (select pga_max_mem/1024/1024 from v$process where addr = paddr) pga_max_mem_MB
--ORACLE 8 MUST COMMENT All end
, (select sum(V$SORT_USAGE.blocks *
--(select value from v$parameter where upper(name) = 'DB_BLOCK_SIZE')
--ORACLE 8 MUST COMMENT THIS LINE BELOW AND ENABLE THE ABOVE
dba_tablespaces.block_size
)/1024/1024 from V$SORT_USAGE, dba_tablespaces
where dba_tablespaces.tablespace_name = V$SORT_USAGE.tablespace and V$SORT_USAGE.session_addr = s.saddr) sort_space_MB
, (select a.value from v$sesstat a, v$statname b where a.statistic# = b.statistic#
and b.name = 'opened cursors current' and a.sid = s.sid) open_cursors
, (select count(*) from v$open_cursor where sid=s.sid) open_cursors_and_cache_closed
, V$SESS_IO.block_gets IO_block_gets, V$SESS_IO.consistent_gets IO_consistent_gets
, V$SESS_IO.physical_reads IO_physical_reads, V$SESS_IO.block_changes IO_block_changes
, V$SESS_IO.consistent_changes IO_consistent_changes
, (select count(*) from DBA_WAITERS where waiting_session = s.sid) waiting_count
, (select count(*) from DBA_WAITERS where holding_session = s.sid) holding_count
, (select sum(used_urec) from V$TRANSACTION where s.saddr = ses_addr) undo_records
, (select min(start_time) from V$TRANSACTION where s.saddr = ses_addr) undo_start_time
/* stream */
, (select decode(module, 'STREAMS', module||', ' || action, null) from v$session where sid=s.sid) STREAM_INFO
/* stream capture */
, (select state || ', [' || CAPTURE_NAME || ' capt. name], [' || ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) || '/' ||
((SYSDATE - CAPTURE_TIME)*86400 || ' latency sec]') from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_INFO
, (select decode(state, 'CAPTURING CHANGES', 0, 1) from V$STREAMS_CAPTURE where sid=s.sid)
STREAM_CAPTURE_PROBLEM
, (select '[Apply status: ' || DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-enqueued messages','UNKNOWN') || ']' ||
'[Program: ' || SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) || ']'
from V$STREAMS_APPLY_READER, DBA_APPLY ap
where SID = s.SID AND SERIAL# = s.SERIAL# and V$STREAMS_APPLY_READER.APPLY_NAME = ap.APPLY_NAME)
STREAM_APPLY_READER_INFO
, (SELECT 'Apply Coordinator: ' || apply_name || ', ' || c.state || ', process: ' || substr(s.program,instr(s.program,'(')+1,4)
FROM v$streams_apply_coordinator c where c.sid = s.sid and c.serial# = s.serial#)
STREAM_APPLY_COORDINATOR
from
v$session s, V$SESS_IO
--where not username is null --avoid Oracle processes
where V$SESS_IO.sid (+)= s.sid
) a
) b

sessions script 2

See any locking session

select * from dba_locks
order by blocking_others

Use utllockt.sql to see locking sessions

Remember that catblock.sql should have ran, you can verify this by using:
select * from dba_locks

sqlplus (windows)>@?\rdbms\admin\utllockt

sqlplus (unix)>@?/rdbms/admin/utllockt

WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
20 None
9 DML Exclusive Exclusive 54132 0

In this case the session 20 is locking session 9, so kill session 20

Library cache pin locks

SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.v_$session_wait
WHERE event = 'library cache pin'

SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
, 'kill -9 ' || spid
FROM x$kglpn p, v$session s, v$process
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='DA741F24'
and v$process.addr = s.paddr

A session is hang, kill anyone interfering

SELECT *
FROM v$access
WHERE OBJECT IN (SELECT object_name
FROM dba_objects
WHERE object_id IN (SELECT object_id
FROM v$locked_object
WHERE session_id = 32))

select 'ALTER SYSTEM DISCONNECT SESSION ''' || sid || ', ' || v$session.serial# ||
''' immediate/*spid=' || spid || '*/'
from v$session, v$process
where sid in (
select sid from v$access
where object = 'VTCONNECTORSTATE'
group by sid
)
and v$process.addr = v$session.paddr

select * from (
SELECT s.schemaname, p.username, s.sid, p.pid, p.spid, s.username ora,
DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL', 'TS','TEMPORARY SEGMENT ', 'TD','TABLE LOCK',
'TM','ROW LOCK', l2.type ) vlock,
DECODE(l2.type, 'TX','DML LOCK', 'TS','TEMPORARY SEGMENT', 'TD',DECODE(l2.lmode+l2.request,
4,'PARSE ' || u.name || '.' || o.name, 6,'DDL', l2.lmode+l2.request), 'TM','DML ' || u.name ||
'.' || o.name, l2.type ) type,
DECODE(l2.lmode+l2.request, 2,'RS', 3 ,'RX', 4 ,'S', 5 ,'SRX', 6 ,'X', l2.lmode+l2.request ) lmode ,
DECODE(l2.request, 0,NULL, 'WAIT') wait
FROM v$process p, v$_lock l1, v$lock l2, v$resource r, sys.obj$ o, sys.user$ u, v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND l2.type <> 'RT'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
)

Lock simulation

The updates performed in this example does not modify the actual values!

select id, de_VC1 from prova where id in (193556, 196756)
ID DE_VC1
---------- --------------------
193556 mon
196756 mon

session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 193556;
1 row updated.

SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 145

username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 145 Transaction Exclusive None 196615 676 270 Not Blocking

session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 196756;
1 row updated.

SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 156

username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 156 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 270 Not Blocking

session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 196756;
--session hang waiting
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id

username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 883 Not Blocking
ALDO 145 DML Row-X (SX) None 53176 0 883 Not Blocking
ALDO 145 Transaction None Exclusive 262191 680 81 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 349 Blocking
ALDO 156 DML Row-X (SX) None 53176 0 349 Not Blocking

session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 193556;
--session hang waiting
session 1, sid 145>ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
session 2, sid 156>--continue waiting

SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id

username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 1246 Blocking
ALDO 145 DML Row-X (SX) None 53176 0 1246 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 712 Not Blocking
ALDO 156 Transaction None Exclusive 196615 676 9 Not Blocking
ALDO 156 DML Row-X (SX) None 53176 0 712 Not Blocking



Trace generated

Dump file c:\oracle\product\10.2.0\admin\al12\udump\al12_ora_5428.trc
Wed Feb 22 11:27:34 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:985M/2046M, Ph+PgF:2957M/3938M, VA:1713M/2047M
Instance name: al12

Redo thread mounted by this instance: 1

Oracle process number: 19

Windows thread id: 5428, image: ORACLE.EXE (SHAD)


*** 2006-02-22 11:27:34.171
*** ACTION NAME:() 2006-02-22 11:27:34.156
*** MODULE NAME:(SQL*Plus) 2006-02-22 11:27:34.156
*** SERVICE NAME:(al12) 2006-02-22 11:27:34.156
*** SESSION ID:(35.38) 2006-02-22 11:27:34.156
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update prova set de_VC1 = 'sess1' where id = 196756 (the session was waiting, then take ORA-00060)
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00020021-000005aa 19 35 X 20 46 X
TX-0003001f-000005b4 20 46 X 19 35 X
session 35: DID 0001-0013-00000007 session 46: DID 0001-0014-00000005
session 46: DID 0001-0014-00000005 session 35: DID 0001-0013-00000007
Rows waited on:
Session 46: obj - rowid = 0000CFB8 - AAAM+4AAHAAABAaAAB
(dictionary objn - 53176, file - 7, block - 4122, slot - 1)
Session 35: obj - rowid = 0000CFB8 - AAAM+4AAHAAABAaAAE
(dictionary objn - 53176, file - 7, block - 4122, slot - 4)
Information on the OTHER waiting sessions:
Session 46:
pid=20 serial=30 audsid=4264 user: 55/ALDO
O/S info: user: root, term: D2E7B5729B, ospid: 5276:1080, machine: WORKGROUP\D2E7B5729B
program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update prova set de_VC1 = 'sess2' where id = 193556 (the session may continue waiting if sess1 do nothing)
End of information on OTHER waiting sessions.
===================================================

Get all long running queries
SELECT /*+ rule */
SYSDATE tracciamento, a.SID SID, b.serial# serial, b.last_call_et,
a.event wait_event, b.username username, b.machine machine,
b.program programma, sq.sql_text
FROM v$session_wait a, v$session b, v$sql sq
WHERE event NOT IN
('smon timer',
'pmon timer',
'rdbms ipc message',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'SQL*Net message from dblink',
'jobq slave wait'
)
AND a.SID = b.SID
AND b.sql_address = sq.address
AND b.last_call_et > 60
AND b.username NOT IN ('SYS', 'SYSTEM', 'EXPDB')
AND b.SID NOT IN (SELECT SID
FROM dba_jobs_running)
ORDER BY b.last_call_et DESC


Master sessions and parallel spawned sessions

col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for A10
set pages 100

select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from
v$px_session px,
v$session s
where
px.sid=s.sid (+)
and
px.serial#=s.serial#
order by 5 , 1 desc;

This script produces a very detailed report on Current Lock Waits in the database including a list of holding and waiting sessions and the types of locks they are holding.

set serveroutput on size 10000
declare

cursor c1 is
select * from v$lock where request != 0
order by id1, id2;
wid1 number := -999999;
wid2 number := -999999;
wholder_detail varchar2(200);
v_err_msg varchar2(80);
wsid number(5);
wstep number(2);
wtype varchar2(10);
wobject_name varchar2(180);
wobject_name1 varchar2(80);
wlock_type varchar2(50);
w_lastcallet varchar2(11);
h_lastcallet varchar2(11);
begin
for c1_rec in c1 loop
if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
null;
else
wstep := 10;
select sid , type into wsid , wtype
from v$lock
where id1 = c1_rec.id1
and id2 = c1_rec.id2
and request = 0
and lmode != 4;
dbms_output.put_line(' ');
wstep := 20;
select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'LOCALHOST',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= wsid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);

begin
select decode(wtype,'TX', 'Transaction',
'DL', 'DDL Lock',
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Un-Known Type of Lock')
into wlock_type
from dual;
declare
cursor c3 is
select object_id from v$locked_object
where session_id = wsid;
begin
wobject_name := '';
for c3_rec in c3 loop
select object_type||': '||owner||'.'||object_name
into wobject_name
from dba_objects
where object_id = c3_rec.object_id;
wobject_name := wobject_name ||' '||wobject_name1;
end loop;
exception
when others then
wobject_name := wobject_name ||' No Object Found';
end;
dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
exception
when no_data_found then
dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
end;
end if;
wstep := 30;
select '.... Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'LOCALHOST\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= c1_rec.sid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
wid1 := c1_rec.id1;
wid2 := c1_rec.id2;
end loop;
if wid1 = -999999 then
wstep := 40;
dbms_output.put_line('No one requesting locks held by others');
end if;
exception
when others then
v_err_msg := (sqlerrm ||' '|| sqlcode||' step='||to_char(wstep));
DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/