编者按:
本文作者系流浪的金鱼(花名),甲骨文数据库工程师。个人主页:https://blog.csdn.net/rishairu1,经其本人授权发布。
【免责声明】本号文章仅代表个人观点,与任何公司无关。
来源|SQL和数据库技术(ID:SQLplusDB)
快问快答,不高深,但是知道了就知道了!
快问:
如何收集回看sql语句中传入的绑定变量值?
快答:
我们知道,当sql语句中有绑定变量的时候,如果需要回看传入的绑定变量值,可以通过 vsql_bind_capture 视图。但是,vsql_bind_capture 视图是有限制的。
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQL_BIND_CAPTURE.html#GUID-D353F4BE-5943-4F5B-A99B-BC9505E9579C
One of the bind values used for the bind variable during a past execution of its associated SQL statement. Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT data types) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement.
也就是说只有LONG,LOB,和ADT数据类型以外的,包含where语句或者having语句的sql,才能记录绑定变量值。那么除此以外的sql如何才能会看绑定变量值呢。
可以通过审计功能。
例子
我们先创建一个audit
CREATE AUDIT POLICY all_emp
ACTIONS DELETE on scott.emp,
INSERT on scott.emp,
UPDATE on scott.emp,
ALL on scott.emp;
然后使其生效。
SQL> AUDIT POLICY ALL_EMP;
SQL> set line 900
SQL> select POLICY_NAME,OBJECT_NAME from AUDIT_UNIFIED_POLICIES where OBJECT_SCHEMA = 'SCOTT';
POLICY_NAME OBJECT_NAME
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
ALL_EMP EMP
ALL_EMP EMP
ALL_EMP EMP
ALL_EMP EMP
SQL> select * from audit_unified_enabled_policies where policy_name = 'ALL_EMP';
USER_NAME POLICY_NAME ENABLED ENABLED_OPTION ENTITY_NAME ENTITY_ SUC FAI
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------- --------------- -------------------------------------------------------------------------------------------------------------------------------- ------- --- ---
ALL USERS ALL_EMP BY BY USER ALL USERS USER YES YES
然后,重新打开一个窗口执行一个insert。
SQL> var v_empno number;
var v_ename nvarchar2(8);
exec :v_empno := 700;
exec :v_ename := 'chris';
insert into emp (empno,ename) values (:v_empno,:v_ename);SQL> SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
1 row created.
SQL> select SQL_TEXT,SQL_BINDS from unified_audit_trail order by EVENT_TIMESTAMP;
insert into emp (empno,ename) values (:v_empno,:v_ename)
#1(3):700 #2(10): c h r i s
这样绑定变量值就被记录下来了。