DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: aud$

  1. #1
    Join Date
    Jan 2001
    Posts
    138

    Question

    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.

  2. #2
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    Hmm.. I guess you can do a table export

  3. #3
    Join Date
    Sep 2000
    Posts
    384
    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....

    Radhakrishnan.M

  4. #4
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759

    Talking

    oops, i almost forgot about that factor.

  5. #5
    Join Date
    Apr 2000
    Location
    Bangalore
    Posts
    16
    You can export aud$ table directly using dba or sys account and then truncate it.
    Thanks,
    Bhaskar

  6. #6
    Join Date
    Jan 2001
    Posts
    138

    Would this work?

    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.


  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Sep 2000
    Posts
    384
    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...
    Radhakrishnan.M

  9. #9
    Join Date
    Jan 2001
    Posts
    138

    From Metalink

    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$


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width