RE: aud$ purge script
Date: Fri, 9 Aug 2013 10:20:58 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F71512CD8BE0_at_EIHQEXVM2.ei.local>
I sent you some code as attachments. The first half is almost all comments. It uses ACL to mail. If you don't get them, let me know. It will work on all your databases (>9i), whether the audit_history table exists or not. If it exists, it will keep the retention there, and shorter retention in aud$, else the retention is kept in aud$ like normal.
The goal is to eliminate the full table scans that occur in AUD$ with a year or two of data. This package bypasses the DBMS_AUDIT_MGNT package which does move AUD$ out of SYSTEM tablespace -- which is good, but the table is still large and full table scans continue to exist.
Moving most of the data to the history table shortens the scans on AUD$, and no program knows about the history table. The data is there for auditors etc.
It is owned by an oracle Admin account, but is run as SYS using curr_authid, so the auto auditing of deletions in aud$ can also be deleted.
Ie parameters 550, 35, true, email, would indicate keeping 1.5 years of data in audit_history and 35 in sys.aud$ if audit history exists. Otherwise keep 1.5 years in sys.aud$ only. TRUE means email the results, use the email address or the default. Defaults are 550, 550, true, myemailgroup. (in case you accidently execute without a parameter).
Joel Patterson
Database Administrator
904 928-2790
--
Joel Patterson
Sr. Database Administrator | Enterprise Integration
Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/
http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT
This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1]
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nagaraj S
Sent: Friday, August 09, 2013 12:54 AM
To: oracle-l
Subject: aud$ purge script
Hello Gurus,
I have a task to purge aud$ table and it need to done in automated way on every month. Please help on sharing the purge script to schedule in db scheduler
-Naga
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 09 2013 - 16:20:58 CEST