Audit Trails 的相關table遷移(AUD$, FGA_LOG$).

參考文件

How To Move The DB Audit Trails To A New Tablespace Using DBMS_AUDIT_MGMT? (Doc ID 1328239.1)

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=qw7rpkst1_9&_afrLoop=235363811991857

目標

將AUD$、FGA_LOG$ 兩表格遷移到其他Tablespace。

作法

1 . 確認 Audit trail tables 存放的Tablespace

CONN / AS SYSDBA SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; 2 TABLE_NAME                     TABLESPACE_NAME ------------------------------ ------------------------------ AUD$                           SYSAUX FGA_LOG$                       SYSAUX

2 . 確認 Audit trail tables 存放的Size

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$                 12 FGA_LOG$             .0625

3 . 建立新的Tablespace並確保足夠的空間

SQL>create tablespace audit_tbs datafile '$ORACLE_BASE/oradata/$ORACLE_SID/audit_tbs-01.dbf' size 100M autoextend on; Tablespace created.

4 . 使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION 進行AUD$ FGA_LOG$

--## 搬移 AUD$  SQL> noaudit connect; SQL> BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDIT_TBS'); END; / PL/SQL procedure successfully completed. --## 搬移 FGA_LOG$  SQL> BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'AUDIT_TBS'); END; / PL/SQL procedure successfully completed. SQL> audit connect;

5 . 確認AUD$ , FGA_LOG$ 兩表格搬移成功。

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$                           AUDIT_TBS FGA_LOG$                       AUDIT_TBS