|
Re: Truncate aud$ table [message #515302 is a reply to message #515301] |
Sun, 10 July 2011 06:29 |
John Watson
Messages: 8964 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You need to read up on DBMS_AUDIT_MGMT, it has procedures for moving the audit train out of the system tablesapce, and also for setting up routines to purge the audit trail regularly.
The package is actually part of the Audit Vault, but you can use it free of charge in 11.2 Enterprise Edition (I don't know about Standard Edition.)
|
|
|
|
Re: Truncate aud$ table [message #515306 is a reply to message #515305] |
Sun, 10 July 2011 07:11 |
John Watson
Messages: 8964 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:You need to read up on DBMS_AUDIT_MGMT
And, by the way, "core" is not an SI unit: many people (including me) have no idea what it means.
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Truncate aud$ table [message #559534 is a reply to message #516608] |
Tue, 03 July 2012 20:20 |
|
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
After truncating the sys.aud$ table, you can "alter system set audit_trail=FALSE scope=spfile sid='*';" and shutdown/startup oracle to stop auditing or you can schedule a job with the following to save only 7 days in sys.aud$.
SYS AS SYSDBA> -- You must commit when you are finished to add the line to dba_jobs.
SYS AS SYSDBA> -- You must run this as sys to get the job to have the right path and privs.
SYS AS SYSDBA> variable jobno number;
SYS AS SYSDBA> variable instno number;
SYS AS SYSDBA> begin
2 select max(job)+1 into :jobno from dba_jobs;
3 select instance_number into :instno from v$instance;
4 dbms_job.submit(:jobno,
5 '
6 begin
7 delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -7);
8 end;
9 ',
10 trunc(sysdate)+1+1/24/60, 'trunc(sysdate)+1+1/24/60',
11 TRUE, :instno);
12 end;
13 /
PL/SQL procedure successfully completed.
SYS AS SYSDBA> commit;
Commit complete.
SYS AS SYSDBA> select job,to_char(next_date,'DD-MON-RR HH24:MI') next_date,
2 what,interval,SCHEMA_USER,instance,broken from dba_jobs
3 where upper(what) like '%AUD$%';
JOB NEXT_DATE WHAT
----- --------------- --------------------------------------------------
INTERVAL
---------------------------------------------------------------------------
SCHEMA_USER INSTANCE B
--------------- -------- -
4 05-JUL-12 00:01
begin
delete from sys.aud$ where trunc(NTIMESTAMP# ) < t
runc(sysdate -7);
end;
trunc(sysdate)+1+1/24/60
SYS 1 N
|
|
|
Re: Truncate aud$ table [message #559535 is a reply to message #559534] |
Tue, 03 July 2012 20:28 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -7);
IIRC, doing DML directly against any SYS objects void warranty/support for the DB.
BTW, we tend to discourage folks from randomly resurrecting old & idle zombie threads.
|
|
|