How to get the last executed SQL statement and bind variable values in oracle.

Question

I have written the following querry to get the last executed SQL statement in the oracle database for a particular session. The SQL text does not contain the actual value of the bind variables. How to get the bind variable values along with the SQL text.

SELECT * FROM v$SQLTEXT_WITH_NEWLINES WHERE address = 
  (SELECT prev_sql_addr FROM v$session WHERE audsid = userenv('SESSIONID'))
ORDER BY piece;

Answer 1

Hi varun,

I don't think the bind variables values are stored by default. Not considering the potential security problems (seeing other sessions actual work), the amount of data to store would be massive.

If you want to see the values of the bind variables, you should activate the trace for that session. You would do this by executing the following command in that session:

alter session set events '10046 trace name context forever, level 12';

More information on AskTom: 10046 tracing

Answer 2

To get the bind variables you will have to use the code below, you dont need to use tracing.

SELECT * FROM v$sql_bind_capture WHERE sql_id='';

or

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING 
FROM v$sql_bind_capture WHERE sql_id='';

http://shaharear.blogspot.com/2009/02/find-bind-variable-value.html