迁移AUD$表至单独表空间

文档课题：迁移AUD$表至单独表空间.
应用场景：作为DBA肯定会遇到保留审计记录的需求，那么随时间的增加该表就会变得越来越庞大.而AUD$默认表空间为system,此时为了方便管理，通常是需要将AUD$表放到单独的表空间.
操作过程：
-- 创建表空间
create tablespace AUD_TBS datafile '/u01/app/oracle/oradata/orcl/audit01.dbf' size 100M autoextend on;
-- 迁移AUD$表
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUD_TBS');
END;
/
-- 迁移FGA_LOG$
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUD_TBS');
END;
/
-- 检查确认
set lines 168 pages 999
col segment_name for a30
col table_name for a18
col tablespace_name for a18
SELECT table_name, tablespace_name
  FROM dba_tables
 WHERE table_name IN ('AUD$', 'FGA_LOG$')
 ORDER BY table_name;

col SEGMENT_NAME for a32
select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME
  from dba_lobs
 where table_name in ('AUD$', 'FGA_LOG$');

迁移前确认
sys@ORCL 2022-10-11 20:54:41> SELECT table_name, tablespace_name
  2    FROM dba_tables
  3   WHERE table_name IN ('AUD$', 'FGA_LOG$')
  4   ORDER BY table_name;

TABLE_NAME         TABLESPACE_NAME
------------------ ------------------
AUD$               SYSTEM
FGA_LOG$           SYSTEM
sys@ORCL 2022-10-11 20:54:41> col SEGMENT_NAME for a32
sys@ORCL 2022-10-11 20:56:05> select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME
  2    from dba_lobs
  3   where table_name in ('AUD$', 'FGA_LOG$');

TABLE_NAME         SEGMENT_NAME                     TABLESPACE_NAME
------------------ -------------------------------- ------------------
FGA_LOG$           SYS_LOB0000000417C00013$$        SYSTEM
FGA_LOG$           SYS_LOB0000000417C00028$$        SYSTEM
AUD$                SYS_LOB0000000407C00040$$        SYSTEM
AUD$                SYS_LOB0000000407C00041$$        SYSTEM

迁移后确认
sys@ORCL 2022-10-11 21:01:57> set lines 168 pages 999
sys@ORCL 2022-10-11 21:02:02> col segment_name for a30
sys@ORCL 2022-10-11 21:02:02> col table_name for a18
sys@ORCL 2022-10-11 21:02:02> col tablespace_name for a18
sys@ORCL 2022-10-11 21:02:02> SELECT table_name, tablespace_name
  2    FROM dba_tables
  3   WHERE table_name IN ('AUD$', 'FGA_LOG$')
  4   ORDER BY table_name;

TABLE_NAME         TABLESPACE_NAME
------------------ ------------------
AUD$               AUD_TBS
FGA_LOG$           AUD_TBS
sys@ORCL 2022-10-11 21:02:02> col SEGMENT_NAME for a32
sys@ORCL 2022-10-11 21:02:36> select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME
  2    from dba_lobs
  3   where table_name in ('AUD$', 'FGA_LOG$');

TABLE_NAME         SEGMENT_NAME                     TABLESPACE_NAME
------------------ -------------------------------- ------------------
FGA_LOG$           SYS_LOB0000095317C00013$$        AUD_TBS
FGA_LOG$           SYS_LOB0000095317C00028$$        AUD_TBS
AUD$                SYS_LOB0000000407C00040$$        AUD_TBS
AUD$                SYS_LOB0000000407C00041$$        AUD_TBS

参考网址：http://t.zoukankan.com/binliubiao-p-12505963.html