重庆思庄Oracle、Redhat认证学习论坛

标题: 迁移AUD$表至单独表空间 [打印本页]

作者: denglj 时间: 2022-11-24 17:39
标题: 迁移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$');

迁移前确认

> 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

> col SEGMENT_NAME for a32

> 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

迁移后确认

> 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

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

> col SEGMENT_NAME for a32

> 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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)