Home » RDBMS Server » Security » Truncate aud$ table (oracle 11g rel2 ,Red hat advance server 5.5)
Truncate aud$ table [message #515301] Sun, 10 July 2011 06:16 Go to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Dear all,

AUDIT(DB) is enable Our production database server.Day by day system tablespace increases rapidly . There are 20 core rows already inserted and system tablespace size is more than 34GB. if I delete data day wise about 95 days will need data clean at table "aud$".

can I run truncate command at aud$ table( with 20 core rows at a time).

N.B: I backup table AUD$


please help
Re: Truncate aud$ table [message #515302 is a reply to message #515301] Sun, 10 July 2011 06:29 Go to previous messageGo to next message
John Watson
Messages: 4672
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 #515305 is a reply to message #515302] Sun, 10 July 2011 06:55 Go to previous messageGo to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Dear ,
Thanks a lot.
Our database server disk space not enough, Therefore I could not move aud$ table data another tablespace.

Can I truncate table aud$ with 20 core data without hampering database & performance.


Thanks
Re: Truncate aud$ table [message #515306 is a reply to message #515305] Sun, 10 July 2011 07:11 Go to previous messageGo to next message
John Watson
Messages: 4672
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 #515517 is a reply to message #515306] Tue, 12 July 2011 01:22 Go to previous messageGo to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Dear
Thanks,Last few days i was out of office.
there are 200000000 rows at AUD$ table. today I run following script

1: 1st I check tablespace for table "aud$"

SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM
FGA_LOG$ SYSTEM

2: Then create a tablespace another location and then run following command

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TABAUDIT');
END;
/
PL/SQL procedure successfully completed.

3: Now check table aud$
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
------------------------------ -------------------
AUD$ TABAUDIT
FGA_LOG$ SYSTEM

But how to decrease system tablespace now.


please help me.

Re: Truncate aud$ table [message #515571 is a reply to message #515517] Tue, 12 July 2011 04:26 Go to previous messageGo to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Dear

Can I truncate aud$ table(200000000 rows) at production database.AUD$ table placed at system tablespace.Aud$ data does not need.

Our database server

SGA_MAX_SIZE=15G
SGA_TARGET=6016M
log_buffer=25600000
DB buffer=4G

SGA:
SQL> show sga

Total System Global Area 1.6034E+10 bytes
Fixed Size 2242320 bytes
Variable Size 1.1677E+10 bytes
Database Buffers 4328521728 bytes
Redo Buffers 26505216 bytes

please give me best solution .
Thanks .
Re: Truncate aud$ table [message #515582 is a reply to message #515571] Tue, 12 July 2011 04:51 Go to previous messageGo to next message
Littlefoot
Messages: 19678
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mirasraf wrote on Tue, 12 July 2011 11:26
Can I truncate aud$ table (...) Aud$ data does not need.

As far as I can tell (which doesn't have to be true): if you don't need the data, so truncate the table! But, if you don't need it, why are you auditing the database?

Check this OTN forum discussion; perhaps you'll find it interesting.
Re: Truncate aud$ table [message #515600 is a reply to message #515582] Tue, 12 July 2011 05:43 Go to previous messageGo to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Dear ,
Thanks quick reply.
we does not need AUD$ data
We want to khow if i truncate aud$ table (200000000 rows) ,there are any hampering database & performance issue.

please give me best solution .
Thanks .
Re: Truncate aud$ table [message #515603 is a reply to message #515600] Tue, 12 July 2011 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No performances impact.

Regards
Michel
Re: Truncate aud$ table [message #515606 is a reply to message #515603] Tue, 12 July 2011 06:45 Go to previous messageGo to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Dear ,
Thanks quick reply.

I truncate aud$ (truncate table aud$ under system tablespace ), can we truncate table under system tablespace

please give me best solution .
Thanks .
Re: Truncate aud$ table [message #515607 is a reply to message #515606] Tue, 12 July 2011 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can.

Regards
Michel
Re: Truncate aud$ table [message #516606 is a reply to message #515607] Tue, 19 July 2011 08:56 Go to previous messageGo to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Dear ,

Thanks your advice .
Last Night we truncate aud$ table but we could not fully resize system tablespace.system tablespace resized Only 6 GB space. system tablespace still occupied 27GB space . we want "system tablespace" will use minimum space that system need (suppose 5GB or near). how we resize system tablespace.
We will use purging script for aud$ table.

N.B: a few minitues require truncate a aud$(there more 230000000 rows).


please give me best solution .
Re: Truncate aud$ table [message #516607 is a reply to message #516606] Tue, 19 July 2011 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check what is at the end of tablespace files querying dba_extents.
Depending on the object that are at the end you might or not reduce the size.

Regards
Michel
Re: Truncate aud$ table [message #516608 is a reply to message #516607] Tue, 19 July 2011 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
maxshrink => http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:859827301882
Re: Truncate aud$ table [message #559534 is a reply to message #516608] Tue, 03 July 2012 20:20 Go to previous messageGo to next message
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 Go to previous message
BlackSwan
Messages: 22901
Registered: January 2009
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.
Previous Topic: Oracle wallet with Instant Client
Next Topic: DBMS_SCHEDULER
Goto Forum:
  


Current Time: Mon Oct 20 18:38:12 CDT 2014

Total time taken to generate the page: 0.09001 seconds