[20140605]移动sys.aud$到别的表空间.txt

[20140605]移动sys.aud$到别的表空间.txt

11GR2下，缺省的audit_trail=DB,另外用户的logon与logoff都记录。这样在生产系统记录会很大。

需要把基表移动到别的表空间，避免占用system表空间过大。

SCOTT@test> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
---------- ------------------------------
AUD$       SYSAUX
FGA_LOG$   SYSAUX

手工的移动方法
--alter table sys.aud$ move tablespace tools;
--这样两个blob字段没有移动。
alter table sys.aud$ move tablespace tools lob (SQLBIND,SQLTEXT) store as( tablespace tools);
alter user sys quota unlimited on tools;

11GR2下参考:
http://www.oracle-base.com/articles/11g/AuditingEnchancements_11gR2.php

The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.

    DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'TOOLS');
END;
/

--注意如果sys.aud$很大，不需要可以先truncate，不然移动会很慢。

SYS@test> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
---------- ------------------------------
AUD$       TOOLS
FGA_LOG$   SYSAUX