RE: audit_trail=db Overhead ?

From: Stephen Lee <Stephen.Lee_at_DTAG.Com>
Date: Fri, 21 Mar 2003 10:28:43 -0600
Message-Id: <24738.322715@fatcity.com>

> -----Original Message-----
>
> "It depends."
>
> I would think that it largely depends on what you intend to
> audit

> You may also want to consider moving the AUD$ table out of the SYSTEM
> tablespace. If you've got Metalink, see article 1019377.6.
>

---

I would add:
If you do this, you should monitor the tablespace usage for AUD$ to ensure that it doesn't fill up the tablespace, which could stop anyone from doing anything, depending on what you audit.

Here's a sample set of commands, that I've had laying around for a long time, for you to pick and choose and edit.

CREATE TABLESPACE ORA_AUDIT

DATAFILE '/dir1/dir2/ora_audit_01.dbf' SIZE 500M REUSE              
DEFAULT STORAGE (INITIAL 1M NEXT 1M                                   

MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 1) ONLINE PERMANENT /  

CREATE TABLESPACE ORA_AUDIT_IDX

DATAFILE '/dir1/dir2/ora_audit_idx_01.dbf' SIZE 100M REUSE          
DEFAULT STORAGE (INITIAL 1M NEXT 1M                                   

MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 1) ONLINE PERMANENT /

create table audx tablespace ORA_AUDIT

   storage (initial 1M next 1M pctincrease 0)

      as select * from aud$ where 1 = 2 /
rename AUD$ to AUD$$
/
rename audx to aud$
/
drop index i_aud1
/
create index i_aud1
  on aud$(sessionid, ses$tid)
    tablespace ORA_AUDIT_IDX storage(initial 1M next 1M pctincrease 0) /

NOAUDIT ALL;
NOAUDIT ALL PRIVILEGES;
-- Check for old auditing
select * from sys.dba_stmt_audit_opts order by audit_option;

audit cluster;
audit connect;
audit context;

audit database link;
audit dimension;
audit directory;
audit index;
audit procedure;
audit profile;
audit public database link;
audit public synonym;
audit role;
audit rollback segment;
audit sequence;
audit synonym;
audit system audit;
audit system grant;
audit table;
audit tablespace;
audit trigger;

audit type;
audit user;
audit view;

audit alter sequence;
audit alter table;
audit comment table;
audit grant directory; Received on Fri Mar 21 2003 - 10:28:43 CST