Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-02-05
7
Medium Priority
?
10,945 Views
Last Modified: 2014-02-09
hello,
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?
0
Comment
Question by:Rao_S
7 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 1200 total points
ID: 39837484
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.

See: http://docs.oracle.com/cd/E11882_01/network.112/e10574/auditing.htm#DBSEG456
0
 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 400 total points
ID: 39837787

1.

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


sysdate-to_date('131215','rrmmdd')>30

Open in new window

0
 
LVL 8

Assisted Solution

by:Surrano
Surrano earned 400 total points
ID: 39838219
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;
commit;
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)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Rao_S
ID: 39838736
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:
COUNT(*)
-------------
1626712

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

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..
0
 

Author Comment

by:Rao_S
ID: 39838741
sorry for the wrong count...,
when i ran the following, i got 1,031,114 rows...

select * from sys.aud$ where ntimestamp# < sysdate-30;
0
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1200 total points
ID: 39839444
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 support.oracle.com 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: http://docs.oracle.com/cd/E11062_01/admin.1023/e11059/avadm_app_d_audit_mgmt.htm#BABBAGGI)
0
 

Author Closing Comment

by:Rao_S
ID: 39846047
thank you all,
a simple delete worked well...
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question