Solved

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

Posted on 2014-02-05
7
7,522 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 22

Accepted Solution

by:
Steve Wales earned 300 total points
Comment Utility
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 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 100 total points
Comment Utility

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 100 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Rao_S
Comment Utility
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
Comment Utility
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 22

Assisted Solution

by:Steve Wales
Steve Wales earned 300 total points
Comment Utility
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
Comment Utility
thank you all,
a simple delete worked well...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now