[20250108]移动SYS.AUD$,AUDSYS.AUD$UNIFIED表到别的表空间.txt

--//我的测试环境如下：
SYS@book> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@book> SELECT * FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
PARAMETER                      VALUE                              CON_ID
------------------------------ ------------------------------ ----------
Unified Auditing               FALSE                                   0

When you create a new database, by default the database uses mixed mode auditing, which enables both traditional (that
is, the audit facility from releases earlier than Release 12c) and the new audit facilities (unified auditing). This
chapter describes how to use pure unified auditing only.

--//看了一些资料，需要设置alter system set audit_trail=none scope=spfile;
--//关闭数据库，重新编译oracle执行文件。
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

--//目前处于混合模式，两者都有，即使用SYS.AUD$也使用AUDSYS.AUD$UNIFIED。前者在system表空间，后者在sysaux表空间。
--//尝试移动到别的表空间，由于使用pdb环境，在cdb_root上操作一次，还在pdb下操作1次，记录自己的操作过程。
--//理论应该也在PDB$SEED数据库操作1次，这样建立pdb库时，还会出现前面的情况，比较麻烦暂时不做。

1.cdb_root:

--//不经常在这个层面操作，简单一点移动到users表空间。
SYS@book> SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
---------- ------------------------------
AUD$       SYSTEM
FGA_LOG$   SYSTEM

--//以sys用户执行:
SYS@book> ALTER USER SYS QUOTA UNLIMITED ON users;
User altered.

--//移动sys.aud$到users表空间。
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => 'USERS');
END;
/

--//说明:
The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.

SYS@book> SYS@book> SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
---------- ---------------
AUD$       USERS
FGA_LOG$   USERS

--//移动AUDSYS.AUD$UNIFIED到users表空间,先删除一部分历史记录。
SYS@book>  select count(*) from AUDSYS.AUD$UNIFIED;
  COUNT(*)
----------
      1522

SYS@book> exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,LAST_ARCHIVE_TIME => SYSDATE-60);
PL/SQL procedure successfully completed.
--//Unified审计的清除oracle设置特别繁琐,首先要执行DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP,设置一个时间点.

SYS@book> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,USE_LAST_ARCH_TIMESTAMP => TRUE);
PL/SQL procedure successfully completed.
--//如果你不想执行前面的步骤,可以设置USE_LAST_ARCH_TIMESTAMP =>FALSE,不过这样应该是全部清除!!

SYS@book>  select count(*) from AUDSYS.AUD$UNIFIED;
  COUNT(*)
----------
       194

SYS@book>  ALTER USER AUDSYS QUOTA UNLIMITED ON users;
User altered.

--//AUDIT_TRAIL_UNIFIED           CONSTANT NUMBER := 51;
--//可以查看包DBMS_AUDIT_MGMT定义确定.
       
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_location_value => 'users');
END;
/

--//注意旧的在AUDSYS.AUD$UNIFIED的信息并不会移动，以后产生的信息在新的表空间。

--//另外我检查发现，不知道为什么原始的按照EVENT_TIMESTAMP字段1天作为分区：
PARTITION BY RANGE (EVENT_TIMESTAMP)
INTERVAL( INTERVAL '1' DAY)

SYS@book> column PARAMETER_VALUE format a30
SYS@book> select table_name,partition_name,INTERVAL,HIGH_VALUE,HIGH_VALUE_LENGTH from dba_tab_partitions where table_name='AUD$UNIFIED';
TABLE_NAME  PARTITION_NAME INT HIGH_VALUE                       HIGH_VALUE_LENGTH
----------- -------------- --- -------------------------------- -----------------
AUD$UNIFIED SYS_P2629      NO  TIMESTAMP' 2024-11-11 00:00:00'                 31
AUD$UNIFIED SYS_P2672      YES TIMESTAMP' 2024-11-16 00:00:00'                 31
AUD$UNIFIED SYS_P2684      YES TIMESTAMP' 2024-11-18 00:00:00'                 31
AUD$UNIFIED SYS_P2746      YES TIMESTAMP' 2024-11-22 00:00:00'                 31
AUD$UNIFIED SYS_P2786      YES TIMESTAMP' 2024-11-23 00:00:00'                 31
AUD$UNIFIED SYS_P2827      YES TIMESTAMP' 2024-11-24 00:00:00'                 31
AUD$UNIFIED SYS_P2852      YES TIMESTAMP' 2024-11-26 00:00:00'                 31
AUD$UNIFIED SYS_P2879      YES TIMESTAMP' 2024-11-27 00:00:00'                 31
AUD$UNIFIED SYS_P2904      YES TIMESTAMP' 2024-11-28 00:00:00'                 31
AUD$UNIFIED SYS_P2930      YES TIMESTAMP' 2024-11-30 00:00:00'                 31
AUD$UNIFIED SYS_P2981      YES TIMESTAMP' 2024-12-13 00:00:00'                 31
AUD$UNIFIED SYS_P3013      YES TIMESTAMP' 2024-12-14 00:00:00'                 31
AUD$UNIFIED SYS_P3033      YES TIMESTAMP' 2024-12-15 00:00:00'                 31
AUD$UNIFIED SYS_P3050      YES TIMESTAMP' 2024-12-16 00:00:00'                 31
AUD$UNIFIED SYS_P3062      YES TIMESTAMP' 2024-12-17 00:00:00'                 31
AUD$UNIFIED SYS_P3110      YES TIMESTAMP' 2024-12-25 00:00:00'                 31
AUD$UNIFIED SYS_P3183      YES TIMESTAMP' 2024-12-29 00:00:00'                 31
AUD$UNIFIED SYS_P3190      YES TIMESTAMP' 2024-12-30 00:00:00'                 31
AUD$UNIFIED SYS_P3336      YES TIMESTAMP' 2025-01-08 00:00:00'                 31
AUD$UNIFIED SYS_P3353      YES TIMESTAMP' 2025-01-09 00:00:00'                 31
20 rows selected.

SYS@book> column INTERVAL format a30
SYS@book> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS' and table_name='AUD$UNIFIED';
OWNER  TABLE_NAME  INTERVAL         PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
------ ----------- ---------------- --------- --------------- -------------------
AUDSYS AUD$UNIFIED INTERVAL '1' DAY RANGE             1048575 USERS

--//ALTER TABLE AUDSYS.AUD$UNIFIED SET INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))报错，看了文档应该使用包DBMS_AUDIT_MGMT。
--//修改为每个分区保存1个月.
BEGIN
  DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
    interval_number       => 1,
    interval_frequency    => 'MONTH');
  END;
/

SYS@book> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS' and table_name='AUD$UNIFIED';
OWNER  TABLE_NAME  INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
------ ----------- ------------------------------ --------- --------------- -------------------
AUDSYS AUD$UNIFIED NUMTOYMINTERVAL(1, 'MONTH')    RANGE             1048575 USERS

--//补充说明，oracle限制直接操作，必须通过DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL修改，我做了跟踪：
=====================
PARSING IN CURSOR #140149912214192 len=100 dep=1 uid=0 oct=170 lid=0 tim=2630233214 hv=1048733816 ad='6eafaf40' sqlid='aq31rznz84u3s'
CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED SET INTERVAL(NUMTOYMINTERVAL(1, ''MONTH''))')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END OF STMT
PARSE #140149912214192:c=1212,e=1205,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=2630233213
=====================
PARSING IN CURSOR #140149911238440 len=88 dep=3 uid=0 oct=3 lid=0 tim=2630235269 hv=3726831676 ad='61585318' sqlid='8wx4mw3g25w1w'
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 19834 and bitand(FLAGS, 128)=0
END OF STMT
PARSE #140149911238440:c=1567,e=1565,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=4,plh=3600061239,tim=2630235268
EXEC #140149911238440:c=60,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,plh=3600061239,tim=2630235403
FETCH #140149911238440:c=59,e=59,p=0,cr=2,cu=0,mis=0,r=1,dep=3,og=4,plh=3600061239,tim=2630235488
STAT #140149911238440 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2 pr=0 pw=0 str=1 time=74 us)'
STAT #140149911238440 id=2 cnt=0 pid=1 pos=1 obj=1551 op='TABLE ACCESS FULL SYS_FBA_TRACKEDTABLES (cr=2 pr=0 pw=0 str=1 time=62 us cost=2 size=7 card=1)'
CLOSE #140149911238440:c=6,e=7,dep=3,type=0,tim=2630235672
=====================
PARSING IN CURSOR #140149911238440 len=88 dep=3 uid=0 oct=3 lid=0 tim=2630235757 hv=3726831676 ad='61585318' sqlid='8wx4mw3g25w1w'
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 19834 and bitand(FLAGS, 128)=0
END OF STMT
PARSE #140149911238440:c=50,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,plh=3600061239,tim=2630235757
EXEC #140149911238440:c=33,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,plh=3600061239,tim=2630235848
FETCH #140149911238440:c=35,e=35,p=0,cr=2,cu=0,mis=0,r=1,dep=3,og=4,plh=3600061239,tim=2630235908
STAT #140149911238440 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2 pr=0 pw=0 str=1 time=41 us)'
STAT #140149911238440 id=2 cnt=0 pid=1 pos=1 obj=1551 op='TABLE ACCESS FULL SYS_FBA_TRACKEDTABLES (cr=2 pr=0 pw=0 str=1 time=36 us cost=2 size=7 card=1)'
=====================
PARSING IN CURSOR #140149907515296 len=72 dep=2 uid=0 oct=15 lid=0 tim=2630236236 hv=2005098286 ad='66ee4aa0' sqlid='1anhdb1vs6rtf'
ALTER TABLE AUDSYS.AUD$UNIFIED SET INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END OF STMT
PARSE #140149907515296:c=2838,e=2836,p=0,cr=4,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=2630236236
=====================

2.pdb层面，book01p：

--//单独建立表空间tsp_audit。
CREATE TABLESPACE tsp_audit DATAFILE
  '/u01/oradata/BOOK/book01p/tsp_audit01.dbf' SIZE 40M AUTOEXTEND ON NEXT 4M MAXSIZE UNLIMITED
LOGGING
DEFAULT
  NO INMEMORY
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SYS@book01p> SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
---------- ---------------
AUD$       SYSTEM
FGA_LOG$   SYSTEM

--//以sys用户执行:
SYS@book01p> ALTER USER SYS QUOTA UNLIMITED ON TSP_AUDIT;
User altered.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => 'TSP_AUDIT');
END;
/

--//说明:
The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.

DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.

SYS@book01p> SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
TABLE_NAME TABLESPACE_NAME
---------- ---------------
AUD$       TSP_AUDIT
FGA_LOG$   TSP_AUDIT

--//移动AUDSYS.AUD$UNIFIED到TSP_AUDIT表空间,先删除一部分历史记录。
SYS@book01p> select count(*) from AUDSYS.AUD$UNIFIED;

  COUNT(*)
----------
      1445

SYS@book01p> exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,LAST_ARCHIVE_TIME => SYSDATE-60);
PL/SQL procedure successfully completed.
--//Unified审计的清除oracle设置特别繁琐,首先要执行DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP,设置一个时间点.

SYS@book01p> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,USE_LAST_ARCH_TIMESTAMP => TRUE);
PL/SQL procedure successfully completed.
--//如果你不想执行前面的步骤,可以设置USE_LAST_ARCH_TIMESTAMP =>FALSE,不过这样应该是全部清除!!

SYS@book01p> select count(*) from AUDSYS.AUD$UNIFIED;
  COUNT(*)
----------
        94

SYS@book01p> ALTER USER AUDSYS QUOTA UNLIMITED ON tsp_audit;
User altered.
        
--//AUDIT_TRAIL_UNIFIED           CONSTANT NUMBER := 51;
--//可以查看包DBMS_AUDIT_MGMT定义确定.
       
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_location_value => 'tsp_audit');
END;
/

--//注意旧的在AUDSYS.AUD$UNIFIED的信息并不会移动，以后产生的信息在新的表空间。

SYS@book01p> column INTERVAL format a30
SYS@book01p> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS' and table_name='AUD$UNIFIED';
OWNER  TABLE_NAME  INTERVAL         PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
------ ----------- ---------------- --------- --------------- -------------------
AUDSYS AUD$UNIFIED INTERVAL '1' DAY RANGE             1048575 TSP_AUDIT

--//修改为每个分区保存1个月.
BEGIN
  DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
    interval_number       => 1,
    interval_frequency    => 'MONTH');
  END;
/

SYS@book01p> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS' and table_name='AUD$UNIFIED';
OWNER  TABLE_NAME  INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
------ ----------- ------------------------------ --------- --------------- -------------------
AUDSYS AUD$UNIFIED NUMTOYMINTERVAL(1, 'MONTH')    RANGE             1048575 TSP_AUDIT

--//另外写一篇定时清除sys.aud$,AUDSYS.AUD$UNIFIED的blog。