Faça um trace com o evento 10046 e veja no arquivo raw os binds. Não execute
o tkprof, é preciso ver o arquivo raw.
ops$marcio:LX10G> create table t ( x int );
Table created.
ops$marcio:LX10G>
ops$marcio:LX10G> alter session set events '10046 trace name context
forever, level 12';
Session altered.
ops$marcio:LX10G> begin
2 for i in 1 .. 10
3 loop
4 insert into t values ( i );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$marcio:LX10G> exit
Identifique o número do parse do seu cursor.
PARSING IN CURSOR #8 len=28 dep=1 uid=55 oct=2 lid=55 tim=1155776832951654
hv=1180080443 ad='402406d8'
INSERT INTO T VALUES ( :B1 )
END OF STMT
PARSE #8:c=0,e=260,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1155776832951644
e veja os binds mais abaixo
...
BINDS #8:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7162640 bln=22 avl=02 flg=05
value=1
EXEC
#8:c=5999,e=5928,p=0,cr=3,cu=22,mis=1,r=1,dep=1,og=4,tim=1155776832957679
BINDS #8:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7162640 bln=22 avl=02 flg=05
value=2
EXEC #8:c=0,e=146,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=4,tim=1155776832957922
BINDS #8:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7162640 bln=22 avl=02 flg=05
value=3
EXEC #8:c=0,e=117,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=4,tim=1155776832958090
BINDS #8:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7162640 bln=22 avl=02 flg=05
value=4
EXEC #8:c=999,e=130,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=4,tim=1155776832958269
A linha com values representa o bind#0, ou seja, o primeiro bind, no meu
caso o único.
Outro exemplo que deixa mais claro é:
ops$marcio:LX10G> drop table t purge;
Table dropped.
ops$marcio:LX10G>
ops$marcio:LX10G> create table t ( x char(1) );
Table created.
ops$marcio:LX10G>
ops$marcio:LX10G> alter session set events '10046 trace name context
forever, level 12';
Session altered.
ops$marcio:LX10G> begin
2 for i in ( select chr(ascii('A')+rownum-1) c
3 from dual connect by level <= 26 )
4 loop
5 insert into t values ( i.c );
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
less /oracle/admin/lx10g/udump/lx10g_ora_7479.trc
...
...
BINDS #2:
kkscoacd
Bind#0
oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=b717be64 bln=32 avl=01 flg=09
value="A"
EXEC
#2:c=2000,e=2013,p=0,cr=3,cu=22,mis=1,r=1,dep=1,og=4,tim=1155778712032583
BINDS #2:
kkscoacd
Bind#0
oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=b717be44 bln=32 avl=01 flg=09
value="B"
EXEC #2:c=0,e=145,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=4,tim=1155778712032820
BINDS #2:
kkscoacd
Bind#0
oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=b717be24 bln=32 avl=01 flg=09
value="C"
EXEC #2:c=0,e=131,p=0,cr=0,cu=1,mis=0,r=1,dep=1,og=4,tim=1155778712033009
BINDS #2:
kkscoacd
Bind#0
oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=b717bb74 bln=32 avl=01 flg=09
value="D"
On 7/3/07, wilsonteixeira2000 <[EMAIL PROTECTED]> wrote:
>
> Boa noite,
>
> Estamos executando um trace em um sistema e preciso saber o valor que
> está sendo passado para uma bind variable no trace ou se tiver alguma
> view do dicionario de dados onde eu possa obter este valor.
>
> obrigado.
>
>
>
--
Marcio Portes
Material Tecnico em Portugues - http://mportes.blogspot.com
Practical Learning Oracle -
http://mportes.blogspot.com/2006/02/practical-learning-oracle.html
[As partes desta mensagem que não continham texto foram removidas]
