RE: Large sys.aud$

I just did the simple thing and select from sys.aud$ and insert into another table in another tablespace. Also, delete from the copy table based on date, then delete from sys.aud$ based on the same date criteria as the first step.

You could combine the first insert step with adding the host name and database name into additional columns. Later, if you have a central database, it would be easy to reach into it via dblink and extract/combine all databases.

Using dbms_metadata (or similar function in Toad etc), try to extract all the views on sys.aud$ and then modify them for the new repository with the additional columns and new view names.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Terrian, Thomas J Mr CTR DLA J6DIB
Sent: Monday, December 08, 2008 7:33 AM
To: oracle-l_at_freelists.org
Subject: Large sys.aud$

All, we have a requirement to keep 1 year worth of audit records. I am kicking around the idea of hourly moving all of the sys.aud$ records from each production database to a central database. That way it would keep sys.aud$ small for each database.

However, this will have its own set of complications......I would have to build new dba_audit views on the central database (in order to have a database name field), what happens when the structure of sys.aud$ changes between database versions, etc.

Has anyone tackled something like this already?

Another option would be to forget about the repository database idea .....instead hold 1 months worth of data online for each database and storing the other 11 months offline somewhere (maybe use RMAN for this?).

Again, any ideas?........

Thanks,
Tom Terrian

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 09 2008 - 17:53:50 CST