Moving Audit Trail Table

  • 11
    Giu

Moving Audit Trail Table

twittergoogle_pluslinkedin

Reading the previous article posted on June 3 about enabling auditing in an Oracle Database, I’d like to add some other useful information.
Oracle stores all the audit results in the SYS.AUD$ table, as stated in the previous article. But the bad news are that the table is by default in the SYSTEM tablespace:

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FORM DBA_TABLES
WHERE TABLE_NAME = ‘AUD$’;

OWNER  TABLE_NAME      TABLESPACE_NAME
—— ————— ——————————
SYS    AUD$            SYSTEM

So the risk of having a full SYSTEM tablespace is very high if you plan to enable the audit feature, leading to possible unpredictable errors.
The solution is to move the audit table before enabling the auditing, and luckily Oracle Database 11g R2 offers a specific store procedure to do it.
Firstly you have to choose in which tablespace you want to put your AUD$ table: one choice could be the AUX tablespace, for example, but it could be better to create a dedicated tablespace:

CREATE TABLESPACE AUDIT_TBS
DATAFILE ‘/u01/data/audit_tbs.dbf’ 100M AUTOEXTEND ON NEXT 10M;

Then you can call the following procedure to move the AUD$ table on the new tablespace:

EXECUTE DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,’AUDIT_TBS’)

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FORM DBA_TABLES
WHERE TABLE_NAME = ‘AUD$’;

OWNER  TABLE_NAME      TABLESPACE_NAME
—— ————— ——————————
SYS    AUD$            AUDIT_TBS

The DBMS_AUDIT_MGMT standard package gives you other useful procedures in order to help you managing the your auditing system, so I advice to explore the other procedures you can find in it, and use them!

AN IMPORTANT NOTICE: if you have previous versions of the Oracle Database (11g R1 or before), you could move the SYS.AUD$ in a tablespace other than SYSTEM (using the standard ALTER TABLE … MOVE TBLESPACE command), but unfortunatelly this is NOT officially supported by Oracle…