From 11gR2 moving the AUD$ table to another tablespace using dbms_audit_mgmt, has been officially introduced/supported. DBMS_AUDIT_MGMT enables audit administrators to manage the various audit trail types like database audit trails, operating system (OS) audit trails, and XML audit trails. It is installed by default in Oracle 11.2 and makes thing easier.
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM
FGA_LOG$ SYSTEM
SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');
SEGMENT_NAME SIZE_IN_MEGABYTES
--------------------------------------------------------------------------------- -----------------
AUD$ .5625
FGA_LOG$ .0625
Now, suppose we deleted rows from aud$ and now want to shrink the table. It couldn’t be done as AUD$ is in SYSTEM tablespace whose segment space management is MANUAL.
SQL> alter table sys.aud$ enable row movement; Table altered. SQL> alter table sys.aud$ shrink space cascade; alter table sys.aud$ shrink space cascade * ERROR at line 1: ORA-10635: Invalid segment or tablespace type
In such cases, you can now move the AUD$ table to the tablespace with AUTO segment space management and shrink it.To do so –
SQL> BEGIN
2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
3 audit_trail_location_value => 'USERS');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ USERS
FGA_LOG$ SYSTEM
SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');
SEGMENT_NAME SIZE_IN_MEGABYTES
--------------------------------------------------------------------------------- -----------------
AUD$ .625
FGA_LOG$ .0625
SQL> @table_info
Enter value for table_name: aud$
old 9: WHERE table_name like UPPER('%&table_name%')
new 9: WHERE table_name like UPPER('%aud$%')
TABLE_NAME OWNER TABLESPACE_NAME NUM_ROWS LAST_ANALYZED AVG_ROW_LEN BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ------------------------------ ---------- ----------------- ----------- ---------- ------------
AUD$ SYS USERS 2331 19-01-12 14:06:33 186 68 0
The last_analyed date got changed too, so seems like oracle internally analyzes it. Internally oracle does
PARSING IN CURSOR #880020292 len=94 dep=1 uid=0 oct=1 lid=0 tim=14104773154 hv=269341700 ad='287d2a1c' sqlid='99yczn480vp04' ALTER TABLE "SYS".AUD$ MOVE TABLESPACE USERS LOB(SQLBIND, SQLTEXT) STORE AS (TABLESPACE USERS) END OF STMT
SQL> select owner,table_name,column_name,segment_name,tablespace_name,index_name from dba_lobs where owner='SYS' and table_name='AUD$'; OWNER TABLE_NAME COLUMN_NAM SEGMENT_NAME TABLESPACE_NAME INDEX_NAME ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------ SYS AUD$ SQLBIND SYS_LOB0000000384C00040$$ USERS SYS_IL0000000384C00040$$ SYS AUD$ SQLTEXT SYS_LOB0000000384C00041$$ USERS SYS_IL0000000384C00041$$
Learn. Share. Repeat. 