[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表空间,除非事前移动到别的表空间.