Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ashif.

Asked: November 14, 2016 - 1:59 pm UTC

Last updated: November 17, 2016 - 2:20 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi ,

I enabled tracing on the particular session in oracle database by using "dbms_system.set_sql_trace_in_session" and i am not enabled to trace back the binding variables associated with insert statements .

Below is the sample statement:

insert into "DATA".TRX (KSP_RSID, KSP_INTERNID, KSP_CONTAINER, POS_DSORUNCONFIG, POS_SOURCETABLE, POS_TYPESYSTEM, KSP_DISCRIMINATOR, KSP_OPID)
values ( :v0 , :v1 , :v2 , :v3 , :v4 , :v5 , 'TABLESOURCESPEC', :v6 )



Can anyone help on this?

I tried with v$sql_bind_capture but the value is null for the column Value_string

and Connor said...

dbms_monitor.session_trace_enable([sid], [serial], waits=>true, binds=>true)

will let you capture binds as well.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ashif Ahamed, November 15, 2016 - 7:48 am UTC

I tried this by using , set_env on the session but that is not bringing the actual data for Value_String column in v$sql_bind_capture ..
Connor McDonald
November 16, 2016 - 4:02 am UTC

v$sql_bind_capture wont get all the info you need, because multiple executions mean multiple bind variable values.

If you want *all* of the bind variables, you need to look in the trace file

dbms_system

Rajeshwaran, Jeyabal, November 16, 2016 - 7:53 am UTC

I tried looking for DBMS_SYSTEM from the product doc - but that is missing.

any reason why this API is not available in product doc? is that got depreciated or de-supported ?
http://docs.oracle.com/database/121/ARPLS/toc.htm


Connor McDonald
November 17, 2016 - 2:20 am UTC

Because you are not meant to use it.

That's why I said use DBMS_MONITOR
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_MONITOR

More on PL/SQL routine DBMS_MONITOR here