[20230111]记录dml语句中的绑定变量.txt

[20230111]记录dml语句中的绑定变量.txt --//利用FGA,可以快速抓取特定对象涉及的sql语句以及里面的绑定变量. https://xanpires.wordpress.com/2012/05/20/how-to-retrieve-dml-statements-text-and-values-from-bind-variables/ --//以前我采用如下方式: alter system set events 'sql_trace [sql:sql_id=6u2vz3tt288kg] bind=true, wait=true'; host sleep 60 alter system set events 'sql_trace off'; 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试: SCOTT@test01p> create table dept1 as select * from dept ; Table created. --//以sys用户执行: begin dbms_FGA.add_policy ( object_schema => 'SCOTT', object_name => 'DEPT1', policy_name => 'DEPT_pol', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types => 'UPDATE, DELETE, SELECT' ); end; / PL/SQL procedure successfully completed. 3. Run the audited statement : var b1 number; var b2 number; exec :b1 := 10; exec :b2 := 20; SCOTT@test01p> select * from dept1 where deptno >= :b1 and deptno<= :b2;     DEPTNO DNAME                LOC ---------- -------------------- -------------         10 ACCOUNTING           NEW YORK         20 RESEARCH             DALLAS SYS@test01p> select * from dba_fga_audit_trail   2  @ prxx_win.sql ============================== SESSION_ID                    : 17691128 TIMESTAMP                     : 2023-01-22 20:52:24 DB_USER                       : SCOTT OS_USER                       : ZWS\Administrator USERHOST                      : WORKGROUP\ZWS CLIENT_ID                     : ECONTEXT_ID                   : EXT_NAME                      : ZWS\Administrator OBJECT_SCHEMA                 : SCOTT OBJECT_NAME                   : DEPT1 POLICY_NAME                   : DEPT_POL SCN                           : 13774270 SQL_TEXT                      : select * from dept1 where deptno >= :b1 and deptno<= :b2 SQL_BIND                      :  #1(2):10 #2(2):20 COMMENT$TEXT                  : STATEMENT_TYPE                : SELECT EXTENDED_TIMESTAMP            : 2023-01-22 20:52:24.632000 PROXY_SESSIONID               : GLOBAL_UID                    : INSTANCE_NUMBER               : 0 OS_PROCESS                    : 7672:7144 TRANSACTIONID                 : STATEMENTID                   : 29 ENTRYID                       : 1 OBJ_EDITION_NAME              : DBID                          : 2923790755 RLS_INFO                      : CURRENT_USER                  : SCOTT PL/SQL procedure successfully completed.          4.收尾: SYS@test01p> @ desc_proc sys dbms_FGA %drop% INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER      PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT    DEFAULTED ---------- ------------ ----------- -------- ------------- --------- --------- ---------- SYS        DBMS_FGA     DROP_POLICY        1 OBJECT_SCHEMA VARCHAR2  IN        Y                                            2 OBJECT_NAME   VARCHAR2  IN        N                                            3 POLICY_NAME   VARCHAR2  IN        N --//以sys用户执行: begin dbms_FGA.drop_policy ( object_schema => 'SCOTT', object_name => 'DEPT1', policy_name => 'DEPT_pol'); end; / --//视图dba_fga_audit_trail的基表对应sys.fga_log$,里面的信息如果不需要也可以删除. --//注意有可能很短的时间就抓取了大量sql语句.导致磁盘消耗很大. --//而且缺省fga_log$在system表空间,除非事前移动到别的表空间.