aud$

[pcotten]

Our aud$ table has over 3 million records. My manager wants to set up the following to clean out the records. We would like to export it weekly on Sunday nights and then truncate, after export, every three months. How do I accomplish this? Thanks.

[Halo]

Hmm.. I guess you can do a table export

[omegamark]

That is a sys table you cannot export sys tables.

If you want the table create another table with the aud$ table and this is the only sys table you can truncate .not others .

pls goahead and truncate the table and delloacte the unsed space....

[Halo]

oops, i almost forgot about that factor.

[Bhaskara]

You can export aud$ table directly using dba or sys account and then truncate it.
Thanks,
Bhaskar

[pcotten]

Can I export by creating a cron job doing the following every Sunday at a specific time? Anything wrong with the syntax?

export sys/password FILE=aud$.dmp TABLES=(sys.aud$)



Thanks.

[jmodic]

It doesn't matter if the sintax is correct or not - as omegamark already mentioned you can not export tables owned by sys. No matter which user you us eto connect to database at export time, there is no way to export SYS's table!

If you realy want to export a content of a AUD$ and later want to truncate it, you can do the following (it might be "officialy unsuported" by Oracle Corp, however I've found this tip from some Oracle Suppot script):

1. Connect as SYSTEM (or some other DBA user)
2. CREATE TABLE system.aud$ TABLESPACE xy STORAGE (...) AS SELECT * FROM sys.aud$ UNRECOVERABLE;
3. CREATE INDEX i_aud1 ON system.aud$ (sessionid, ses$tid) TABLESPACE xx STORAGE (...) UNRECOVERABLE;
4. GRANT ALL ON systeM.aud$ TO sys;
5. DROP TABLE sys.aud$;
6. CREATE SYNONYM sys.aud$ FOR system.aud$;

That way system will create audit records into SYSTEM's table AUD$ which will behave exatly as the original audit table, and you can export it. Test this before you use it because I'm writing this from teh memory. I've used this in the past, before the 8i this was also the only way to simulate 8i's DDL triggers and logon triggers, because you can create triggers on SYSTEM.AUD$, which you can't on SYS.AUD$.

HTH,

[omegamark]

pcotten ,

You have to purge the records of sys.Aud$ or system.aud$ as the advisor has told u the option once in a while .

There is no point having about 3 million rows as you have said.

So you can very well delete some records .

Remember this is the only table in the sys you can delete .Other tables you should never modify...

[pcotten]

Here is an excerpt from article on Metalink regarding truncating aud$.

If audit trail information must be archived, you can copy the relevant
records to a normal database table before deletion using either:

-- EXPort the audit trail table to an OS file

-- CREATE TABLE <audit archive table>
AS SELECT * from sys.aud$ WHERE 1=2;

-- INSERT INTO <audit archive table>
SELECT <records> FROM sys.aud$