如何收集回看sql语句中传入的绑定变量值。

我们知道，当sql语句中有绑定变量的时候，如果需要回看传入的绑定变量值，可以
通过 v$sql_bind_capture 视图。但是，v$sql_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

这样绑定变量值就被记录下来了。