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

来源：这里教程网时间：2026-03-03 21:17:35 作者：

[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。

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推