Question

我已撰写了以下询问,以在专册数据库中为某届会议提供最后一份已执行的文件。清单案文没有包含约束变量的实际价值。 • 如何使各种变量数值与“公平市价”文本相结合。

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

Answer 1

为了获得您必须使用以下代码的变量,你迫切需要使用追查。

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

Answer 2

我认为,约束性变数值是不存在的。如果不考虑潜在的安全问题(见其他会议的实际工作),储存数据的数量将是巨大的。

如果你想看到具有约束力的变数的价值,那么你就应当启动该届会议的追踪。您将在这届会议上执行以下指令:

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

http://asktom.oracle.com/pls/asktom/f?p=100:11:0:P11_questSTION_ID:2068765100346589007'rel=“noreferer”>Ask Tom: 10046~。

Answer 3

如果是 in子,你可以执行

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

或者,如果你看着别人所处死去,那么你就会看着自己。 ID and child curor #:

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

页: 1

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)

但这当然是在盘问之前必须做的。

Answer 4

下面的提问,将 s-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;
/

Answer 5

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;

友情链接