Database Reference
In-Depth Information
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.
SQL> SELECT userenv('sid') FROM dual;
USERENV('SID')
--------------
134
SQL> SET ARRAYSIZE 1
SQL> SET PAGESIZE 1
SQL> SET TIME ON
23:41:04 SQL> SET PAUSE "Hit return to continue"
23:41:04 SQL> SET PAUSE ON
23:41:04 SQL> SELECT tab.msgid
FROM "NDEBES"."POST_OFFICE_QUEUE_TABLE" tab
WHERE q_name=:q_name and (state=:state)
FOR UPDATE;
Hit return to continue
Session 1 has retrieved one row up to this point, even though SQL*Plus does not yet
display that row. The SQL trace output, which contains a
FETCH
call with
r=1
(
r
is short for
rows), proves it.
$ tail -f ten_ora_1724.trc
=====================
PARSING IN CURSOR #2 len=112 dep=0 uid=30 oct=3 lid=30 tim=78932802402 hv=2531579934
ad='6792e910'
SELECT tab.msgid
FROM "NDEBES"."POST_OFFICE_QUEUE_TABLE" tab
WHERE q_name=:q_name and (state=:state)
FOR UPDATE
END OF STMT
PARSE #2:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=78932802391
EXEC #2:c=0,e=1290,p=0,cr=16,cu=9,mis=0,r=0,dep=0,og=1,tim=78932838870
FETCH
#2:c=0,e=459,p=0,cr=12,cu=0,mis=0,
r=1
,dep=0,og=1,tim=78932843524
In theory, this leaves six rows worth of messages with
STATE=0
for session 2. Let's see what
happens in session 2.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
SQL> SELECT userenv('sid') FROM dual;
USERENV('SID')
--------------
158
SQL> SET ARRAYSIZE 1
SQL> SET PAGESIZE 1
SQL> SET TIME ON
