Hello community,
I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on DB version 12c (12.2.0.1.0).
With time (in 3 years) my AUD$ table grew to 500GB in size, especially because of it's 2 CLOB columns (SQLBIND and SQLTEXT).
I want to shrink storage consumed by AUD$ to minimum, without losing any audit data.
I came up with the following plan:
1. CREATE TABLESPACE TS_AUDIT_HISTORY
2. CREATE USER AUDIT_USER in tablespace TS_AUDIT_HISTORY
3. CREATE partitioned TABLE AUDIT_USER.AUDIT_HISTORY_TABLE in tablespace TS_AUDIT_HISTORY with:
- 3.1. COMPRESS HIGH on LOB's (SQLBIND and SQLTEXT)
- 3.2. COMPRESS HIGH on all partitions
4. INSERT INTO AUDIT_USER.AUDIT_HISTORY_TABLE select * from SYS.AUD$; COMMIT;
5. TRUNCATE TABLE SYS.AUD$
I haven't implemented my plan on any Production environment yet, since almost everyone advices against any other solution but DBMS_AUDIT_MGMT - which I don't want to use because it doesn't compress table's data / CLOB's).
But I have implemented my plan on a Development environment and results were amazing - After compressing I got from 500GB -> 50GB which is like 90% compress ratio !
My questions:
A) How 'smart' is to truncate SYSTEM's table AUD$, can it have any other impact besides the fact that user's won't be able to log in?
B) When I will be TRUNCATEing AUD$, table will be locked, which will prevent users to log in! - any way to 'bypass' that, since users being able to log in to DB is very important?C) What do you think of my plan?
Any input / concern / comment is welcome.
Thanks in advance,
Danijel