FGA and bind values

In a 9i production database, from time to time, we see long blocking/blocked sessions that make the application slow down noticably. We were able to provide the SQLs that the blocking sessions excuted. However, when asked to proivde bind values of those SQLs, we were out of luck. Inspired by Dion's post, I looked at the possibilities of using FGA to find the bind values. At the end of some testing, however, I should say no luck with 9i database still.

In 10g version, it is possible to get bind values in the select, update, delete and insert statements, however, in 9i only select is supported.

My test in 10g is shown as follows. It can be seen that we can obtain the bind values for select, update, insert and delete statement.

(1) Create the table

zxxxx94@DBWRKEV1> create table t
  2  as
  3  select
  4   rownum id
  5  , owner
  6  , object_name
  7  , object_type
  8  , object_id
  9  , created
 10  from all_objects
 11  where rownum <=5000;  Table created.  zxxxx94@DBWRKEV1>
zxxxx94@DBWRKEV1> alter table t add constraint t_pk primary key(id);

Table altered.

zxxxx94@DBWRKEV1>
zxxxx94@DBWRKEV1> begin
  2   dbms_stats.gather_table_stats(user, 'T', cascade=>true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

(2) Add a FGA policy

zxxxx94@DBWRKEV1> begin
  2   dbms_fga.add_policy (
  3       object_schema => user,
  4       object_name   => 'T',
  5       policy_name   => 'T_PLY2',
  6       audit_column  => 'ID',
  7       statement_types=> 'SELECT,UPDATE,DELETE,INSERT');
  8  end;
  9  /

(3) Issue select/update/insert/delete statements with bind variables

zxxxx94@DBWRKEV1>
zxxxx94@DBWRKEV1> variable id number
zxxxx94@DBWRKEV1> variable obj_name varchar2(20)
zxxxx94@DBWRKEV1> execute :id := 200

PL/SQL procedure successfully completed.

zxxxx94@DBWRKEV1> execute :obj_name := 'DENIS'

PL/SQL procedure successfully completed.

zxxxx94@DBWRKEV1>
zxxxx94@DBWRKEV1> select object_name from t where id = :id;

OBJECT_NAME
------------------------------
REFCON$

zxxxx94@DBWRKEV1>
zxxxx94@DBWRKEV1> execute :id := 100

PL/SQL procedure successfully completed.

zxxxx94@DBWRKEV1> update t set object_name=lower(object_name) where id = :id;

1 row updated.

zxxxx94@DBWRKEV1> delete from t where id = :id;

1 row deleted.

zxxxx94@DBWRKEV1>
zxxxx94@DBWRKEV1> execute :id := 5001

PL/SQL procedure successfully completed.

zxxxx94@DBWRKEV1> insert into t values( :id, 'OWNER', :obj_name,'TABLE',12345,sysdate);

1 row created.

zxxxx94@DBWRKEV1> commit;

Commit complete.

(4) Query DBA_FGA_AUDIT_TRAIL

zxxxx94@DBWRKEV1>
zxxxx94@DBWRKEV1> exec print_table( 'select * from dba_fga_audit_trail' );

(... cut ...)
-----------------
SESSION_ID                    : 2430
TIMESTAMP                     : 20-may-2009 12:01:50
DB_USER                       : Zxxxx94
OS_USER                       : Yu
USERHOST                      : MSHOME\NTBK
CLIENT_ID                     :
ECONTEXT_ID                   :
EXT_NAME                      :
OBJECT_SCHEMA                 : Zxxxx94
OBJECT_NAME                   : T
POLICY_NAME                   : T_PLY2
SCN                           : 167297644
SQL_TEXT                      : select object_name from t where id = :id
SQL_BIND                      :  #1(3):200
COMMENT$TEXT                  :
STATEMENT_TYPE                : SELECT
EXTENDED_TIMESTAMP            : 20-MAY-09 12.01.49.563831 PM -04:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 9035
TRANSACTIONID                 :
STATEMENTID                   : 156
ENTRYID                       : 2
-----------------
SESSION_ID                    : 2430
TIMESTAMP                     : 20-may-2009 12:01:52
DB_USER                       : Zxxxx94
OS_USER                       : Yu
USERHOST                      : MSHOME\NTBK
CLIENT_ID                     :
ECONTEXT_ID                   :
EXT_NAME                      :
OBJECT_SCHEMA                 : Zxxxx94
OBJECT_NAME                   : T
POLICY_NAME                   : T_PLY2
SCN                           : 167297646
SQL_TEXT                      : update t set object_name=lower(object_name) where id = :id
SQL_BIND                      :  #1(3):100
COMMENT$TEXT                  :
STATEMENT_TYPE                : UPDATE
EXTENDED_TIMESTAMP            : 20-MAY-09 12.01.52.465334 PM -04:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 9035
TRANSACTIONID                 : 0006000B000252F3
STATEMENTID                   : 160
ENTRYID                       : 3
-----------------
SESSION_ID                    : 2430
TIMESTAMP                     : 20-may-2009 12:01:53
DB_USER                       : Zxxxx94
OS_USER                       : Yu
USERHOST                      : MSHOME\NTBK
CLIENT_ID                     :
ECONTEXT_ID                   :
EXT_NAME                      :
OBJECT_SCHEMA                 : Zxxxx94
OBJECT_NAME                   : T
POLICY_NAME                   : T_PLY2
SCN                           : 167297649
SQL_TEXT                      : delete from t where id = :id
SQL_BIND                      :  #1(3):100
COMMENT$TEXT                  :
STATEMENT_TYPE                : DELETE
EXTENDED_TIMESTAMP            : 20-MAY-09 12.01.52.992022 PM -04:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 9035
TRANSACTIONID                 : 0006000B000252F3
STATEMENTID                   : 162
ENTRYID                       : 4
-----------------
SESSION_ID                    : 2430
TIMESTAMP                     : 20-may-2009 12:01:54
DB_USER                       : Zxxxx94
OS_USER                       : Yu
USERHOST                      : MSHOME\NTBK
CLIENT_ID                     :
ECONTEXT_ID                   :
EXT_NAME                      :
OBJECT_SCHEMA                 : Zxxxx94
OBJECT_NAME                   : T
POLICY_NAME                   : T_PLY2
SCN                           : 0
SQL_TEXT                      : insert into t values( :id, 'OWNER', :obj_name,'TABLE',12345,sysdate)
SQL_BIND                      :  #1(4):5001 #2(5):DENIS
COMMENT$TEXT                  :
STATEMENT_TYPE                : INSERT
EXTENDED_TIMESTAMP            : 20-MAY-09 12.01.53.973038 PM -04:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 9035
TRANSACTIONID                 : 0006000B000252F3
STATEMENTID                   : 166
ENTRYID                       : 5
-----------------

PL/SQL procedure successfully completed.