Oracle에서 마지막으로 실행된 SQL 문을 가져오고 변수 값을 바인딩하는 방법

Oracle에서 마지막으로 실행된 SQL 문을 가져오고 변수 값을 바인딩하는 방법

특정 세션에 대해 Oracle 데이터베이스에서 마지막으로 실행된 SQL 문을 가져오기 위해 다음 쿼리를 작성했습니다.SQL 텍스트에 바인딩 변수의 실제 값이 없습니다.SQL 텍스트와 함께 바인딩 변수 값을 가져오는 방법.

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

바인딩 변수를 얻으려면 아래 코드를 사용해야 합니다. 추적을 사용할 필요가 없습니다.

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

또는

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

바인딩 변수 값은 기본적으로 저장되지 않는 것 같습니다.잠재적인 보안 문제(다른 세션의 실제 작업 확인)를 고려하지 않을 경우 저장할 데이터의 양은 방대합니다.

바인딩 변수의 값을 보려면 해당 세션에 대한 추적을 활성화해야 합니다.이 작업은 해당 세션에서 다음 명령을 실행하여 수행할 수 있습니다.

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

AskTom에 대한 추가 정보: 10046 추적

만약 당신이 sqlplus에 있다면 당신은 실행할 수 있습니다.

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

또는 다른 사용자가 실행한 SQL을 찾고 있는 경우 SQL_ID와 자식 커서 #:

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

에 있어서와 같이

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

이 메서드는 피킹된 바인딩 변수만 표시합니다.신뢰할 수 있는 유일한 방법은 바인딩 변수를 사용하여 추적하는 것입니다.

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

하지만 당연히 그것은 쿼리가 실행되기 전에 완료되어야 합니다.

sql_id를 입력 매개 변수로 사용하고 대체된 바인딩 변수 값으로 출력을 제공하는 아래 쿼리를 실행합니다.

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;
/

BiPin의 답변을 보고 필요에 맞게 조금 수정했습니다.사용자가 보고서를 실시간으로 실행할 때 사용하는 매개 변수를 파악해야 했습니다.여기 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;

언급URL : https://stackoverflow.com/questions/1707291/how-to-get-the-last-executed-sql-statement-and-bind-variable-values-in-oracle