oracle oracle中如何获取最后执行的SQL语句并绑定变量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1707291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the last executed SQL statement and bind variable values in oracle
提问by varun
I have written the following query 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.
我编写了以下查询来获取特定会话的 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;
回答by Kirit Chandran
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
http://shaharear.blogspot.com/2009/02/find-bind-variable-value.html
回答by Vincent Malgrat
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
回答by Kyle Hailey
if you are in sqlplus you can execute
如果你在 sqlplus 你可以执行
select * from table
( dbms_xplan.display_cursor (null,null, 'ADVANCED'));
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 #:
或者，如果您正在寻找其他人执行的 SQL，只需输入他们的 SQL_ID 和子游标 #：
select * from table
( dbms_xplan.display_cursor ('sql_id',child_cursor#, 'ADVANCED'));
select * from table
( dbms_xplan.display_cursor ('sql_id',child_cursor#, 'ADVANCED'));
as in
如
select * from table
( dbms_xplan.display_cursor ('a18asdr99x',0, 'ADVANCED'));
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)
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
但当然这必须在查询执行之前完成
回答by Bipin P
Run the below query which takes the sql_id as the input parameter and will give the output with replaced bind variable values.
运行以下查询，该查询将 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;
/
