1.查看AUD$和FGA_LOG$所在表空间
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

2.查看AUD$和FGA_LOG$数据量
select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');

3.创建audit_tbs表空间
create tablespace audit_tbs datafile '/u01/app/oracle/oradata/prod/audit_tbs01.dbf' size 100M autoextend on;

4.move AUD$和FGA_LOG$
SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_TBS');
END;
/

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_TBS');
END;
/

5.查看move后的AUD$和FGA_LOG$所在表空间
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

ITPub博客

Move aud$和fga_log$数据到其它Tablespace

最新文章