Oracle9i, 10g 如何抓取繫結變數的值

 

1） 10046論斷事件

         設定方法：

             全域性設定：初始引數檔案中設定 event="10046 trace name context forever,level 12"

             本地session設定：alter session set events '10046 trace name context forever, level 8'

             其它session設定：透過DBMS_SYSTEM.SET_EV系統包實現,需要透過v$session得到sid,serial#

 

           這裡我使用的是本地session設定：

       ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

           select object_name from maggie.obj where object_id=:V1;

           執行，為變數V1賦值28221，執行完成

           然後關閉踉蹌事件alter session set events '10046 trace name context off';

           在user_dump_file引數指定的目錄下找到生成的udump檔案，找到以下結果：

........

*** SESSION ID:(14.27652) 2010-02-11 15:46:33.000

APPNAME mod='TOAD 9.7.2.5' mh=1219114848 act='' ah=4029777240

=====================

PARSING IN CURSOR #3 len=69 dep=0 uid=5 ct=42 lid=5 tim=1039369128939 hv=2562935477 ad='11a9a36c'

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'

END OF STMT

EXEC #3:c=0,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1039369067384

=====================

PARSING IN CURSOR #3 len=55 dep=0 uid=5 ct=3 lid=5 tim=1039372642475 hv=1692422788 ad='1193e4d8'

select object_name from maggie.obj where object_id=:V1

END OF STMT

PARSE #3:c=0,e=176,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1039372642463

BINDS #3:

 bind 0: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 acflg=01 oacfl2=10 size=4000 ffset=0

   bfp=04e9251c bln=4000 avl=05 flg=05

   value="28221"

EXEC #3:c=15625,e=15374,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1039372676465

FETCH #3:c=0,e=58,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1039372680531

FETCH #3:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1039372685688

 

         我們可以看到V1變數的值為28221

 

           補充：10046事件可以設定以下四個級別，如果要獲取繫結變數的值，至少要設定level 4

                   1 - 標準的SQL_TRACE功能，等價於sql_trace

                   4 - Level 1 加上繫結值(bind values)

                   8 - Level 1 + 等待事件

                   12 - Level 1 + Level 4 + Level 8

 

 

      2） 10G裡新增的VIEW：V$SQL_BIND_CAPTURE

          執行SQL語句

SELECT   next_station, wip_d_sn_master.ROWID

  FROM   wip_d_sn_master

 WHERE   (serial_number = :V00001 OR carton_no = :V00002) AND ROWNUM = 1;

 

            輸入變數值CK001L296J021Y，CMI0461000000140000027

          在v$sql中找到剛剛執行的這個SQL的hash_value:

select sql_id, sql_text, bind_data,HASH_VALUE from v$sql

where sql_text Like 'SELECT   next_station, wip_d_sn_master.ROWID%';

 

          得到的值為476601873，代入v$sql_bind_capture中查詢出繫結變數的值

SELECT hash_value,sql_id,NAME,POSITION,DATATYPE_STRING,LAST_CAPTURED,value_string FROM v$sql_bind_capture

WHERE hash_value = 476601873

       

HASH_VALUE SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING 

 

476601873 77y45qnf6hrhj :V00001 1 VARCHAR2(4000) 2010-2-11 0:55:25 CK001L296J021Y

476601873 77y45qnf6hrhj :V00002 2 VARCHAR2(4000) 2010-2-11 0:55:25 CMI0461000000140000027