Oracle Scratchpad

June 7, 2011

Audit Excess

Filed under: audit,Bugs,Infrastructure,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 6:18 pm BST Jun 7,2011

So you’ve decided you want to audit a particular table in your database, and think that Oracle’s built in audit command will do what you want. You discover two options that seem to be relevant:

audit all on t1 by access;
audit all on t1 by session;

To check the audit state of anything in your schema you can then run a simple query – with a few SQL*Plus formatting commands – to see something like the following:


June 1, 2011


Filed under: audit,Oracle,Troubleshooting,Upgrades — Jonathan Lewis @ 5:42 pm BST Jun 1,2011

Here’s an example of how the passing of time can allow a problem to creep up on you.

A recent client had a pair of logon/logoff database triggers to capture some information that wasn’t available in the normal audit trail, and they had been using these triggers successfully for many years, but one day they realised that the amount of redo generated per hour had become rather large, and had actually been quite bad and getting worse over the last few months for no apparent reason. (You’ve heard this one before … “honest, guv, nothing has changed”).

May 27, 2011

Audit Ouch!

Filed under: audit,Bugs,Infrastructure,Oracle,redo — Jonathan Lewis @ 5:37 pm BST May 27,2011

A few days ago I was rehearsing a presentation about how to investigate how Oracle works, and came across something surprising. Here’s a simple bit of code:

May 25, 2011


Filed under: audit,Bugs,Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 6:41 pm BST May 25,2011

Here’s one of those funny little details that can cause confusion:

SQL> select * from user_audit_object;

no rows selected
SQL> audit select on indjoin by session whenever successful;

Audit succeeded.

SQL> select
  2     count(*)
  3  from
  4     indjoin         ij
  5  where
  6     id between 100 and 200
  7  and        val between 50 and 150
  8  ;


1 row selected.

SQL> select * from user_audit_object where obj_name = 'INDJOIN';

no rows selected


April 5, 2010

Failed Login

Filed under: audit,Infrastructure,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 7:59 pm BST Apr 5,2010

Here’s a piece of code I found recently running every half hour on a client site:

SQL_ID = 2trtpvb5jtr53
        TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0", :"SYS_B_1") AS curr_timestamp,
        COUNT(username) AS failed_count
        returncode != :"SYS_B_2"
AND     TO_CHAR(timestamp, :"SYS_B_3") >= TO_CHAR(current_timestamp - TO_DSINTERVAL(:"SYS_B_4"), :"SYS_B_5")


September 19, 2009


Filed under: audit,Infrastructure,Oracle — Jonathan Lewis @ 12:18 pm BST Sep 19,2009

Just a quick pointer to a comment about deleting from aud$ that came up recently on the OTN Database Forum.

I know the answer’s in the manuals, but sometimes you just don’t spot little details, or remember where you think you read them, so it’s nice to capture the comment when you can.  (And there’s a nice little follow-up from Mark Powell that extends the topic to fga_log$ – the “fine-grained audit” table)

Update Feb 2012

Here’s another OTN link to capture a reference on how to clean out the audit trail (aud$, fga_log$, or file system text of XML logs) “officially” and automatically in 11g using the dbms_audit_mgmt package. (The link currently points to Chapter 27 of the 11.2 PL/SQL Packages reference guide).

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 6,550 other followers