I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on Oracle DB version 12c (220.127.116.11.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:
CREATE TABLESPACE TS_AUDIT_HISTORY
CREATE USER AUDIT_USER in tablespace TS_AUDIT_HISTORY
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
INSERT INTO AUDIT_USER.AUDIT_HISTORY_TABLE select * from SYS.AUD$; COMMIT;
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 !
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.
I successfully created a new tablespace, new user (with default new tablespace), new table with partitions and compression on it as well as on the LOB's and inserted all the data from SYS.AUD$ to the new table -I had to use parallel hint here and it still took me 6 hours. Afterwards I truncated AUD$. Original AUD$ was 500GB in size and now the new one (with all the records of course) is only 40GB in size. You were right guys, Truncate SYS.AUD$ finished in like 1 or 2 seconds.
So now I saved like 460GB.... but not really. Coz now these 460GB can be used only in AUD$'s tablespace (SYSAUX) and not in other tablespaces :/ I really hoped that TRUNCATE TABLE AUD$ DROP STORAGE; actually releases the allocated storage, but it does not. I can see that SYSAUX is 460GB less in size now, but these 460GB didn't free in ASM storage.
The only 100% way to reallocate the storage to be available as Usable storage in ASM as far as i know is:
Drop a table (which is of course not an option for AUD$)
export / import table (which is not an option since Oracle does not allow that for AUD$ table, already tried to export it with expdp but it didn't work).
So how do I actually make the 'freed' / deallocated storage available to ASM / other tablespaces?