Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-02-05
7
Medium Priority
?
11,322 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

577 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