can we find the exact sql_text in that session

kangminghui

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Login to Oracle as schema: TEST

Session One:SID=58
SQL> desc test
Name              Null?    Type
----------------- -------- ------------
ID                         NUMBER(38)
SQL> select * from test;

        ID
----------
         3
         3
SQL> update test set ID=3 where ID=2;
=================
Open another Session Two:SID=61
SQL> update test set ID=3 where ID=2;
=================
Then back to session One: SID=58
Since session one is not blocked(but forgot to commit), so do other operation like:
select * from test;
update example set name='jack' where rownum=1;#update other table
select sid from v$mystat where rownum=1
=================
Now the question is:
Is there anyway we can find the exact sql text in Session One(sid=58) that blocked the Session Two(sid=61)?

Steps checked as follows:
1.Checked the locker and holder.
SQL> SELECT INST_ID,DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type,ctime FROM gV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type from gV$LOCK ORDER BY id1, request;

   INST_ID SESS                                                    ID1        ID2      LMODE    REQUEST TY      CTIME
---------- ------------------------------------------------ ---------- ---------- ---------- ---------- -- ----------
         1 Holder: 58                                           196612       1847          6          0 TX     200363
         1 Waiter: 61                                           196612       1847          0          6 TX     200356
2.Check the sqltext in session,can not find the blocking sql_text in Session 58
select dbms_lob.substr(sql_fulltext) from v$sql where sql_id in (select sql_id from v$session where sid=58) or sql_id in (select prev_sql_id from v$session where sid=58);
DBMS_LOB.SUBSTR(SQL_FULLTEXT)
--------------------------------------------------------------------
select sid from v$mystat where rownum=1

3.Check the sql_text in v$open_cursor,can not find the blocking sql_text in Session 58
SQL> select * from v$open_cursor where sid=58 and user_name != 'SYS';

SADDR                   SID USER_NAME                      ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT
_TYPE
---------------- ---------- ------------------------------ ---------------- ---------- ------------- -------------------------
00000003B8F55208         58 TEST                           00000003B097E798 2977474493 bt2mb8usrj9xx select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 77
N CURSOR CACHED
00000003B8F55208         58 TEST                           00000003AE8C1C08 3842168405 5x08fpgkh5pkp select sid from v$mystat where rownum=1
00000003B8F55208         58 TEST                           00000003B1E22560  914163366 4vs91dcv7u1p6 insert into sys.aud$( sessionid,entryid,statement,ntimestamp
ECURSIVE

So now customer who login with Session 61 complaint for the block, he asked us to find the exact sqltext in Session 58 which block his,is there anyway to find?

Maclean Liu(刘相兵

"百分之百"找到精确的 block sql几乎是不可能的，这个已经讨论过很多次了。

详见这个maillist里的讨论 http://www.freelists.org/post/or ... -SQL-locking-others,22

kangminghui

好像是没有什么办法，谢谢刘大

psufnxk2000

通过logmnr 挖出来 。。 然后 筛选。