Using FGA to view/retrieve bind variables used in queries

I was responding to a question on Oracle Forums on retrieving bind variables and thought I should post this on the blog as well.

Of many things, fine grained auditing techniques can used to view/retrieve values supplied to bind variables used in a query. I found it useful when user complains that queries are taking longer time in certain conditions and normally you would rely on application developers to provide you with the values supplied to the bind variables.

I have demonstrated an example below to retrieve bind variable values using FGA :-

1. First apply audit policy on the table/s which are involved in SQL e.g. in this example its the "DEPT" table.

begin
dbms_FGA.add_policy (
object_schema => 'SCOTT',
object_name => 'DEPT',
policy_name => 'DEPT_Aud',
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => 'UPDATE, DELETE, INSERT, SELECT' );
end;
/

2. Run your SQL with bind variables e.g. in this example it's an INSERT into DEPT table.

var a number
var b varchar2(20)
var c varchar2(20)

begin
:a:=90;
:b:='NewDept';
:c:='NewLoc';
end;
/

INSERT INTO DEPT VALUES(:a,:b,:c);

3. Query DBA_FGA_AUDIT_TRAIL view to get the SQL and bind variables

select object_name, sql_text, sql_bind from dba_fga_audit_trail;

OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_BIND
--------------------------------------------------------------------------------

DEPT
INSERT INTO DEPT VALUES(:a,:b,:c)
#1(2):90 #2(7):NewDept #3(6):NewLoc

So, happy auditing BUT do remember to turn off the auditing when your work is over.

begin
DBMS_FGA.DROP_POLICY (
object_schema => 'SCOTT',
object_name => 'DEPT',
policy_name => 'DEPT_Aud');
end;
/