Detect SQL and bind variables with extended auditing (10g)

Oracle introduced a useful auditing capability in Oracle 10g called extended auditing. With this feature enabled, Oracle writes all statement information to the audit row, including the text of the SQL statement and any bind variables used. This is a great help if you’re trying to diagnose a security breach or application problem. To configure extended auditing, set the AUDIT_TRAIL parameter to DB_EXTENDED.


For instance, with a server parameter file, you can change this so it takes effect after restarting the database, like so:
ALTER SYSTEM SET AUDIT_TRAIL = DB_EXTENDED SCOPE=SPFILE;

For example, consider a SQL statement that inserts data into a table named EMP. With extended auditing turned on, configure auditing on the EMP:
AUDIT INSERT on SAMPLE.EMP BY ACCESS;

Now, suppose someone issues the following INSERT statement:
INSERT INTO sample.emp VALUES(:id, :lname, :fname);

If the bind values are 1, 'SMITH', and 'KENNY', this will store both the SQL and bind variables into the audit trail. To see this, issue the following SELECT statement:
SELECT sqltext, sqlbind FROM sys.aud$;

Oracle returns a row containing two values similar to the following:
INSERT INTO sample.emp VALUES(:id, :lname, :fname)
#1(1):1 #2(5):Smith #3(5):Kenny