Solved

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

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

Accepted Solution

by:
Steve Wales earned 300 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 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 100 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 100 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
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
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 22

Assisted Solution

by:Steve Wales
Steve Wales earned 300 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export BLOB data from Oracle 10g 4 35
oracle report printing 2 pages in one page 2 58
return result by latest date - oracle query 21 69
How to free up undo space? 3 27
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

910 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

24 Experts available now in Live!

Get 1:1 Help Now