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

I have written the following query to get the last executed SQL statement in the or开发者_开发问答acle 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;

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

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

if you are in sqlplus you can execute

select * from table ( dbms_xplan.display_cursor (null,null, 'ADVANCED'));

or if you are looking for SQL executed by someone else just put in their the SQL_ID and child cursor #:

select * from table ( dbms_xplan.display_cursor ('sql_id',child_cursor#, 'ADVANCED'));

as in

select * from table ( dbms_xplan.display_cursor ('a18asdr99x',0, 'ADVANCED'));

This method shows the only shows peeked bind variables. The only dependable way is tracing with bind variables

dbms_monitor.session_trace_enable(session_id => 127, serial_num => 29, waits => FALSE, binds => TRUE)

but of course that has to be done before the query gets executed

Run the below query which takes the sql_id as the input parameter and will give the output with replaced bind variable values.

set serveroutput on;


DECLARE
   v_fulltext   CLOB;
   v_sql_id     VARCHAR2 (100);

   CURSOR c1( v_sql_id varchar2)
   IS
      SELECT decode(substr(NAME,1,4),':SYS',replace(name,':',':"')||'"' ,NAME ) NAME, POSITION, datatype_string,nvl(VALUE_STRING,'NULL') value_string
    FROM v$sql_bind_capture
       WHERE sql_id = v_sql_id;
BEGIN

  v_sql_id:= '&sql_id';

   SELECT sql_fulltext
     INTO v_fulltext
     FROM v$sql
    WHERE sql_id =v_sql_id  AND ROWNUM = 1;

   FOR rec IN c1(v_sql_id)
   LOOP


      IF substr(rec.datatype_string,1,8) = 'VARCHAR2'
      THEN
     SELECT REPLACE (v_fulltext,
             rec.NAME,
             '''' || rec.value_string || ''''
            )
       INTO v_fulltext
       FROM DUAL;
      END IF;

      IF rec.datatype_string = 'NUMBER'
      THEN
     SELECT REPLACE (v_fulltext, rec.NAME, rec.value_string)
       INTO v_fulltext
       FROM DUAL;
      END IF;

   END LOOP;
   DBMS_OUTPUT.PUT_LINE(v_fulltext);

   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN DBMS_OUTPUT.PUT_LINE('NO SQL FOUND FOR THE SQL ID');

END;
/

Looking at BiPin's answer I modified it a bit to suit my needs. I needed to figure out what parameters users were using when running a report in real time. Here's my solution which adds the childnumber to the query from v$sql_bind_capture.

declare
v_sql_id varchar(100);
v_fulltext clob;
v_childnumber number;
begin
v_sql_id := '&sql_id';
v_childnumber := '&childnumber';
SELECT LISTAGG(SQL_text, '') within group (order by piece) 
     INTO v_fulltext
     FROM v$sqltext
    WHERE sql_id =v_sql_id;      
    for I in (select name,VALUE_STRING from v$sql_bind_capture where sql_id = V_SQL_ID and child_number = V_CHILDNUMBER)LOOP
        v_fulltext := regexp_replace(v_fulltext,i.name||' ',i.value_string);
      end LOOP;
      DBMS_OUTPUT.PUT_LINE(v_fulltext);
end;