Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Managing AUD$ without DBMS_AUDIT_MGMT

User_CZT8PSep 9 2019 — edited Sep 20 2019

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

Comments

Post Details

Added on Sep 9 2019
7 comments
1,084 views