Chuyển audit AUD$, FGA_LOG$ ra khỏi tablespace SYSTEM

Mục đích: Chuyển bảng AUD$ và FGA_LOG$ về tablespace SYSAUX hoặc DATA_LOG (tạo mới) hoặc AUD (tạo mới). Trên Production, chạy câu lệnh sau vào giờ thấp điểm cho an toàn.

set pagesize 120
set linesize 180
SELECT T.TABLE_NAME, TS.SEGMENT_SPACE_MANAGEMENT
FROM DBA_TABLES T, DBA_TABLESPACES TS
WHERE TS.TABLESPACE_NAME = T.TABLESPACE_NAME
AND T.TABLE_NAME = 'AUD$';

TABLE_NAME SEGMEN
---------- ------
AUD$       MANUAL

select TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT 
  from DBA_TABLESPACES 
 where TABLESPACE_NAME='SYSAUX'; 

TABLESPACE_NAME  SEGMEN 
---------------- ------ 
SYSAUX           AUTO

select TABLE_NAME,TABLESPACE_NAME,SEGMENT_NAME
  from dba_lobs
 where OWNER='SYS' and TABLE_NAME='AUD$';
      
TABLE_NAME TABLESPACE_NAME SEGMENT_NAME
---------- --------------- -------------------------
AUD$       SYSTEM          SYS_LOB0000000384C00040$$
AUD$       SYSTEM          SYS_LOB0000000384C00041$$

select SEGMENT_NAME,segment_type,tablespace_name
  from dba_segments 
 where segment_name in (
       select INDEX_NAME 
         from dba_indexes 
        where OWNER='SYS'
          and TABLE_NAME='AUD$');

SEGMENT_NAME             SEGMENT_TYPE TABLESPACE_NAME
------------------------ ------------ ---------------
SYS_IL0000000384C00041$$ LOBINDEX      SYSTEM
SYS_IL0000000384C00040$$ LOBINDEX      SYSTEM

Move AUD$ table:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE 
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');

PL/SQL procedure successfully completed.

Move FGA_LOG$ table if needed:

SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE 
=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
   AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');

PL/SQL procedure successfully completed.

Check the results.

select TABLE_NAME,TABLESPACE_NAME,SEGMENT_NAME
from dba_lobs
where OWNER='SYS' and TABLE_NAME='AUD$';

TABLE_NAME TABLESPACE_NAME SEGMENT_NAME
---------- -------------- --------------------------
AUD$       SYSAUX          SYS_LOB0000000384C00040$$
AUD$       SYSAUX          SYS_LOB0000000384C00041$$

select SEGMENT_NAME,segment_type,tablespace_name
  from dba_segments 
  where segment_name in (
        select INDEX_NAME 
          from dba_indexes 
         where OWNER='SYS'
         and TABLE_NAME='AUD$');

SEGMENT_NAME             SEGMENT_TYPE TABLESPACE_NAME
------------------------ ------------ ---------------
SYS_IL0000000384C00041$$ LOBINDEX     SYSAUX
SYS_IL0000000384C00040$$ LOBINDEX     SYSAUX

select count(*) from dba_objects where status!='VALID';

COUNT(*)
--------
0

Hy vọng hữu ích cho bạn.