Moving AUD$ table to another tablespace in 11gR2

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$$
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s