1.Launched my application which connected to database

2.Logged into database using sqlplus as sys

3.Got the Process ID of the my application's session using the following query.


SELECT 	p.spid,	
	s.osuser, 
	s.machine, 
	s.program, 
	s.logon_time, 
	s.status 
FROM 	v$session s, 
	v$process p 
WHERE 	s.paddr = p.addr 
AND 	s.username = <app User Name>;



4.Then Enabled tracing using following commands:
oradebug setospid <p.spid value from above query>
oradebug event 10046 trace name context forever, level 12

5.Did some operations in my application. These operations inserted bunch of records to the database. One of the insert statement inserted multiple records to one table. I need to debug these records at oracle level.

6.Executed the following command in sqlplus to get the trace file name.
oradebug tracefile name

7.While looking at the trace file, I found the insert query. But it showed the bind variable values for only one record.

8.Please look at the following trace file content:

PARSING IN CURSOR #1 len=98 dep=0 uid=105 oct=2 lid=105 tim=9725620482 hv=2397566571 ad='31cc8f68'
INSERT INTO MYTABLE (Col1,Col2,Col3,Col4,Col5) VALUES (:1,:2,:3,:4,:5)
END OF STMT
PARSE #1:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9725620478
BINDS #1:
kkscoacd
Bind#0
oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=136 off=0
kxsbbbfp=07fdcf30 bln=32 avl=14 flg=05
value="Col1Val"
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=32
kxsbbbfp=07fdcf50 bln=22 avl=03 flg=01
value=23
Bind#2
oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=56
kxsbbbfp=07fdcf68 bln=32 avl=14 flg=01
value="Col3Val"
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=88
kxsbbbfp=07fdcf88 bln=22 avl=03 flg=01
value=291
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=112
kxsbbbfp=07fdcfa0 bln=22 avl=02 flg=01
value=1
WAIT #1: nam='db file sequential read' ela= 3522 file#=8 block#=21856 blocks=1 obj#=230252 tim=9725624584
WAIT #1: nam='db file sequential read' ela= 9077 file#=8 block#=21240 blocks=1 obj#=230252 tim=9725633739
WAIT #1: nam='db file scattered read' ela= 7181 file#=8 block#=21849 blocks=7 obj#=230252 tim=9725641051
WAIT #1: nam='db file sequential read' ela= 2913 file#=8 block#=22445 blocks=1 obj#=230253 tim=9725644034
WAIT #1: nam='db file scattered read' ela= 3993 file#=8 block#=22461 blocks=4 obj#=230253 tim=9725648134
WAIT #1: nam='db file sequential read' ela= 9021 file#=8 block#=17237 blocks=1 obj#=230253 tim=9725657237
WAIT #1: nam='db file sequential read' ela= 10025 file#=8 block#=17279 blocks=1 obj#=230253 tim=9725667366
EXEC #1:c=0,e=46858,p=16,cr=1,cu=21,mis=0,r=4,dep=0,og=1,tim=9725667439
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=230253 tim=9725667503
WAIT #1: nam='SQL*Net message from client' ela= 489 driver id=1413697536 #bytes=1 p3=0 obj#=230253 tim=9725668029
=====================


From the EXEC #1 line, we can see that number of rows processed are 4 (r=4). But the trace file does not have the remaining row details.

Oracle only gives you the bind variables at the time of parse, when it reuses the same sql entry in memory it does not parse the statement and so does not record the bind variables.

If you want all values you have to add a trigger on your table or enter to fine-grained auditing world.

Regards
Michel