在日常的数据库维护中，经常出现因为数据库登录审计的功能启动,导致system表空间被用满.从而出现异常,一般建议把aud$相关对象迁移到其他表空间,从而避免system被用完的风险.
人工move aud$相关对象
alter table AUDIT$ move tablespace users; alter table AUDIT_ACTIONS move tablespace users; alter table AUD$ move tablespace users; alter table AUD$ move lob(SQLBIND) store as SYS_IL0000000384C00041$$ (tablespace users); alter table AUD$ move lob(SQLTEXT) store as SYS_IL0000000384C00041$$ (tablespace users); alter index I_AUDIT rebuild online tablespace users; alter index I_AUDIT_ACTIONS rebuild online tablespace users; --可能修改值(index和lob index) SQL> select COLUMN_NAME,index_name from dba_lobs where owner='SYS' and table_name='AUD$'; COLUMN_NAME INDEX_NAME ---------------------------------------- ------------------------------ SQLBIND SYS_IL0000000384C00040$$ SQLTEXT SYS_IL0000000384C00041$$ SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$'; INDEX_NAME ------------------------------ SYS_IL0000000384C00040$$ SYS_IL0000000384C00041$$ SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT$'; INDEX_NAME ------------------------------ I_AUDIT SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT_ACTIONS'; INDEX_NAME ------------------------------ I_AUDIT_ACTIONS
DBMS_AUDIT_MGMT实现迁移
conn / as sysdba BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, audit_trail_location_value => 'USERS'); END; /
验证DBMS_AUDIT_MGMT效果
SQL> select segment_name,tablespace_name from dba_segments where
2 segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
3 'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS SYSTEM
AUDIT$ SYSTEM
AUD$ SYSTEM
SYS_IL0000000384C00041$$ SYSTEM
SYS_IL0000000384C00040$$ SYSTEM
I_AUDIT_ACTIONS SYSTEM
I_AUDIT SYSTEM
SQL> BEGIN
2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
3 audit_trail_location_value => 'USERS');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> select segment_name,tablespace_name from dba_segments where
2 segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
3 'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS SYSTEM
AUDIT$ SYSTEM
AUD$ USERS
SYS_IL0000000384C00041$$ USERS
SYS_IL0000000384C00040$$ USERS
I_AUDIT_ACTIONS SYSTEM
I_AUDIT SYSTEM
通过试验证明DBMS_AUDIT_MGMT就是迁移了AUD$表中相关对象,对于和审计相关的其他几个对象并未迁移到其他表空间
aud$相关说明
1.DBMS_AUDIT_MGMT版本支持情况
It is still not supported (but it works) to use it on 10.2.0.4 and 11.1.0.7 for non-Audit Vault Environment. Using RDBMS with Audit Vault, it is supported for 10.2.0.4.0 and 11.1.0.7.0 as the DBMS_AUDIT_MGMT Package is provided to be used with an Audit Vault Environment.
2.该包可以实现在线迁移，特别是在高业务的系统中,可以实现在线迁移,而人工的move操作不能实现在线处理
3.对于AUD$对象，如果登录审计数据不是非常重要,可以通过truncate来解决一时的问题，在业务高的系统，可能truncate不能马上操作成功，可以尝试使用11gr2的新特性alter session set ddl_lock_timeout = 10;来实现自动ddl尝试
4.如果确定不需要登录审计功能，可以通过设置audit_trail=none来关闭(需要重启实例)
