10.2.0.3 compatibility mode
I generally prefer to move the aud$ table out of the system tablespace. However, a different company who I personally have NO contact with manages production. All contact is through my managers. I need to make a case to my managers who will then make a case to the operations team to move the aud$ table out of the system tablespace. I need to cover my bases. My managers are not DBAs and like many think they know more than they do. Audit rules and monitoring the aud$ table is strictly the responsibility of operations and the client. Note that the operations management from the client is under a different management chain (its a huge company). So our client managers dont have alot of control over over this.
First off, we have NO say over auditing. It is exclusively set up by the client. They did not even tell us they were turning it on. I just know because I was checking production (I get select access).
AUD$ table is 16.3 gbs. It has grown by 4 GBs in the last 4 months. They are currently auditing all ddl, grants, logins and logoffs. 90% of the audits are things unrelated to our application (oracle agent schema, and a couple of utility schemas that operations maintains. I have NO say over this).
They have created 3 indexes on AUD$. So the total space usage is over 30 GBs in the system tablespace. I doubt they have any purge requirements. so the data will stay forever. Again, I have no say over this.
Give the growth, I would think that its best to start a conversation (and guys it takes a LONG time to get anything done here. think months upon months, if not a year), to get the aud$ table move out of the system tablespace. Operations basically just makes sure the DB is online and handle backups. They don't really provide any performance support or monitoring.
However, the only reason I have to move the aud$ table out of the system tablespace is that if it fills up the system tablespace we go down. The response from my management will be 'it is there job to watch for space, this is not our problem' and if it went to them it would be 'we are capable of monitoring space usage'. We have never run out space before.
This concerns me because as this grows it will become less and less practical to move this tablespace out of the system tablespace. As far as concurrency issues, my limited access, tells me we just have a basic dump of files on a SAN and nothing is spread out over the SAN. So I don't think making a new tablespace and moving it there would reduce risk of performance.
Is there anything else I can do to make a case to move the aud$ table out? I know to most of you, this is plenty, but I'll need more than this. This is not 'urgent', but I think its wise to bring attention to it now. I'm just trying to stay ahead of any issues. If production goes down, I may not be able to fix it, but Ill get woken up at 3 AM and be expected to come to work (we dont get access to production from home). So part of this is self interest...
On 11.2 the dbms_audit_mgmt package contains a procedure perform moving the audit trail: SET_AUDIT_TRAIL_LOCATION which according to the manual, "This procedure moves the audit trail tables from their current tablespace to a user-specified tablespace."
HTH -- Mark D Powell --
PS - My view would be just make the SYSTEM tablespace big enough to hold the audit trail or set up a task to extract and delete older audit data so the sys.aud$ table is limited in size.