連結：

標題：

在日常的資料庫維護中，經常出現因為資料庫登入審計的功能啟動,導致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來關閉(需要重啟例項)

關於aud$物件相關處理

相關文章