RE: aud$ purge script
Date: Fri, 9 Aug 2013 21:26:25 +0000
Message-ID: <7C5A0D167BEADF4C984A9167609E8E0F6060A6C4_at_CITESMBX3.ad.uillinois.edu>
We pull from the sys.aud$ and V$DATABASE on many databases daily, put that data into a logging table and then truncate the sys.aud$ on the individual databases.
Here's the pertinent excerpt from my script which runs daily:
$ORACLE_HOME/bin/sqlplus oracle/$PConword <<! >>$actionlog
set trim on heading off sqlprompt ' '
set lines 280;
insert into oracle.t_master_audit(select name,os_username, timestamp,username, userhost,terminal from V\$DATABASE_at_$1, dba_audit_trail@$1 where username not in ('BAD_LOGIN','OPS\$ORACLE','DBSNMP','ORACLE','STRMDBA'));
commit;
delete from sys.aud\$_at_$1;
commit;
exit
!
echo "$1 done."
shift
done
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Iggy Fernandez
Sent: Friday, August 09, 2013 4:21 PM
To: oracle-l_at_freelists.org
Subject: RE: aud$ purge script
From the documentation on DBMS_AUDIT_MGMT:
The DB_DELETE_BATCH_SIZE property enables you to control the number of audit records that are deleted in one batch. Setting a large value for this parameter requires increased allocation for the undo log space.
Tim Gorman might say that the fastest DELETE is DROP TABLE or TRUNCATE TABLE.
Iggy
> http://www.oradba.ch/2011/02/manage-audit-trails-with-dbms_audit_mgmt/
>> http://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-
>> but-what-about-purging/ It creates a procedure and a scheduled job to
>> purge the audit log. It even
>> I have a task to purge aud$ table and it need to done in automated
>> way on every month. Please help on sharing the purge script to schedule in db
>> scheduler --
http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 09 2013 - 23:26:25 CEST