How to delete data which is older than 30 days from audit tablespace?

i wanted to check if you would be able to tell me how to delete data which is older than 30 days from audit tablespace?
we store audit data/index in tsaudit_data/tsaudit_ind. how can i find and delete data which is older than 30 days?
Who is Participating?
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Is it your own audit data or do you have sys.aud$ in the tsaudit_data ?

If it's sys.aud$ I believe this will do it:

delete from sys.aud$ where timestamp# < sysdate-30;

Deleting table rows automatically takes index info with it, so no real clean up of indexes needed.

If it's custom audit data, will need some specifics about what your tables are.

Modifications to sys.aud$ are always audited and those rows will never be deleted, just FYI.

HuaMinChenConnect With a Mentor Business AnalystCommented:


There should be one date column in the table, which is indicating when the record is created.

2. Here to one example condition to check if it is older than 30 days


Open in new window

SurranoConnect With a Mentor System EngineerCommented:
How many records are we talking about? If you have to delete, say, 200 days of data and keep 30 days (waaay less to keep) then it would be best to create a new table along with a new index. But only if you can omit using the table for some seconds (only for the renaming / exchange part).

-- 1. create temp table. use the date field of your choice, 
-- same options e.g. tablespace, extent sizes etc as original
create table temp_tsaudit_data as
select * from tsaudit_data where mydatefield > sysdate-30
tablespace ... ; 
-- 2. create index with same definition 
-- only if you choose step 3a below.
create index temp_tsaudit_ind on temp_tsaudit_data (...) 
tablespace ...;
-- 3.a: drop original and rename temp
drop table tsaudit_data;
rename temp_tsaudit_data to tsaudit_data;
rename temp_tsaudit_ind to tsaudit_ind; 
-- 3.b: truncate original and reload from temp:
truncate table tsaudit_data;
insert into tsaudit_data select * from temp_tsaudit_data;
drop table temp_tsaudit_data;

Open in new window

The advantage of both of these approaches is that it is likely to be faster, unused space will be deallocated and index will be rebuilt as well.
As a future convenience measure, consider converting your audit tables to partitioned ones. That way, deleting old data is as simple as truncating a partition (takes up to 10 sec regardless of data amount)
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Rao_SAuthor Commented:
hi sjwales, how do check if i have sys.aud$ is in ts_audit_data? i ran the 'check free space' script..
Tablespace      Used MB      Free MB      Total MB      Pct. Free
TS_AUDIT_DATA      432      68      500      14
TS_AUDIT_IND      0      200      200      100

i ran this:
SELECT COUNT(*) FROM aud$;  --> i got the following:

i ran this :
select count(*) from FGA_LOG$;   --> i got the following:

so do you think as you suggested a simple,
delete from sys.aud$ where timestamp# < sysdate-30;
will work?

hi HuaMinChen,
i ran the following:
select * from sys.aud$ where timestamp# < sysdate-30;
i got no rows, so i ran the following:
select * from sys.aud$ where ntimestamp# < sysdate-30;
i ran this and 75 rows..
select * from sys.aud$ where sysdate-to_date('131215','rrmmdd')>30;

hi Surrano,
i did think of move data into another tablespace etc... but for that i will need to create a service ticket and wait till it gets approved, because i will be creating new structures...i was trying to just delete data so that i dont have to create new structures..

i got 75 rows..
Rao_SAuthor Commented:
sorry for the wrong count...,
when i ran the following, i got 1,031,114 rows...

select * from sys.aud$ where ntimestamp# < sysdate-30;
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
You said you needed to delete "audit data" but you didn't specify what audit data so I assumed you were talking about.

You could run this:

select owner, table_name from dba_tables where tablespace_name = 'TS_AUDIT';

This will tell you what tables are in that tablespace.

If the table you are trying to cleanup is SYS.AUD$ there are some good notes at addressing this (if you have a support contract with Oracle, if you can't, I can't replicate the contents of those notes here, since that's a violation of the support site licensing):

73408.1 - How to Truncate, Delete or Purge rows from the audit trail table aud$
1299033.1 - Master Note for Oracle Database Auditing
166301.1 - How to Reorganize SYS.AUD$ Table

There is also a builtin package to manage your audit trail data if you're on 11gR2: DBMS_AUDIT_MGMT (docs here:
Rao_SAuthor Commented:
thank you all,
a simple delete worked well...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.