Step 3: Test the Audit Settings

At this stage, auditing is enabled and any SELECT statements performed on the OE.CUSTOMERS table are written to the to DBA_AUDIT_TRAIL data dictionary view. Now, you are ready to test the audit settings.

To test the audit settings:  

1. Start SQL*Plus, and connect as user sec_admin.

   sqlplus sec_admin
   Enter password: password
   

2. Enter the following SELECT statement to create an alert in the audit trail:

   SELECT COUNT(*) FROM OE.CUSTOMERS;
   

3. Enter the following statement to view the DBA_AUDIT_TRAIL view:

   SELECT USERNAME, SQL_TEXT, TIMESTAMP 
   FROM DBA_AUDIT_TRAIL 
   WHERE SQL_TEXT LIKE 'SELECT %';
   

   For this SELECT statement, enter the text for the SQL_TEXT column ('SELECT %') using the same case that you used when you entered the SELECT statement in Step 2. In other words, if you entered that SELECT statement in lowercase letters, then enter 'select %' when you query the DBA_AUDIT_TRAIL view, not 'SELECT %'.

   Output similar to the following appears:

   USERNAME          SQL_TEXT                                TIMESTAMP
   ----------------- --------------------------------------- ------------------
   SEC_ADMIN         SELECT COUNT(*) FROM OE.CUSTOMERS       31-MAR-10
   

4. Exit SQL*Plus:

   EXIT