Moving AUD$ Table to Another Tablespace

Start been supported on 11GR2, DBMS_AUDIT_MGMT comoes by default alow manage all trails (database,XML,OS).

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> 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$ .4631
FGA_LOG$ .0634
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

So easy, move the AUD$ table to the tablespace with AUTO segment space management and shrink it.
To do so:

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘USERS’);
END;
/

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$ .615
FGA_LOG$ .0634

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 190 72 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

Comment

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