当使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION 更改用于存储审核数据的默认表空间时,会在新表空间中创建AUDSYS.AUD$UNIFIED表的所有新表分区,lob分区和索引分区。
(旧的分区仍保留在原表空间)
如果未在新表空间中创建LOB和INDEX分区,可能是BUG, 需要补丁。
BUG 27576342:DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION不会移动LOB和索引分区
更改默认表空间示例:
col segment_name for a25
col tablespace_name for a20
select segment_name,ds.tablespace_name from dba_segments ds where owner = 'AUDSYS';
SEGMENT_NAME TABLESPACE_NAME
------------------------- --------------------
AUD$UNIFIED SYSAUX
AUD$UNIFIED SYSAUX
AUD$UNIFIED SYSAUX
AUD$UNIFIED SYSAUX
AUD$UNIFIED SYSAUX
SYS_IL0000018570C00097$$ SYSAUX
SYS_IL0000018570C00031$$ SYSAUX
SYS_IL0000018570C00030$$ SYSAUX
SYS_IL0000018570C00097$$ SYSAUX
SYS_IL0000018570C00031$$ SYSAUX
SYS_IL0000018570C00030$$ SYSAUX
SYS_IL0000018570C00097$$ SYSAUX
SYS_IL0000018570C00031$$ SYSAUX
SYS@ORCLCDB>select table_owner,table_name,tablespace_name,partition_name,partition_position,segment_created,read_only from dba_tab_partitions where table_name like 'AUD%';
TABLE_OWNE TABLE_NAME TABLESPACE_NAME PARTITION_NAME PARTITION_POSITION SEGMENT_ READ_ONL
---------- -------------------- -------------------- -------------------- ------------------ -------- --------
AUDSYS AUD$UNIFIED SYSAUX AUD_UNIFIED_P0 1 NO NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P181 2 YES NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P771 3 YES NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P1420 4 YES NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P1920 5 YES NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P2560 6 YES NO
SQL> create tablespace AUDITTS datafile '<PATH>/auditts.dbf' size 100M autoextend on;
Tablespace created.
#更改审计默认表空间
SQL> exec dbms_audit_mgmt.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, audit_trail_location_value=>'TS_AUDIT');
#更改变天分区
SQL> begin
dbms_audit_mgmt.alter_partition_interval(
interval_number=>1,
interval_frequency=>'DAY');
end;
/
SQL> select owner,segment_name,tablespace_name,partition_name,segment_type,retention from dba_segments where owner='AUDSYS';
OWNER SEGMENT_NAME TABLESPACE_NAME PARTITION_NAME SEGMENT_TYPE RETENTION
---------- ------------------------------------ ------------------------- -------------------- ------------------------ ------------------
AUDSYS AUD$UNIFIED SYSAUX SYS_P8561 TABLE PARTITION
AUDSYS SYS_IL0000017945C00097$$ SYSAUX SYS_IL_P8567 INDEX PARTITION
AUDSYS SYS_IL0000017945C00031$$ SYSAUX SYS_IL_P8565 INDEX PARTITION
AUDSYS SYS_IL0000017945C00030$$ SYSAUX SYS_IL_P8563 INDEX PARTITION
AUDSYS SYS_LOB0000017945C00030$$ SYSAUX SYS_LOB_P8562 LOB PARTITION DEFAULT
AUDSYS SYS_LOB0000017945C00031$$ SYSAUX SYS_LOB_P8564 LOB PARTITION DEFAULT
AUDSYS SYS_LOB0000017945C00097$$ SYSAUX SYS_LOB_P8566 LOB PARTITION DEFAULT
SQL> select table_owner,table_name,tablespace_name,partition_name,partition_position,segment_created from dba_tab_partitions where table_name like 'AUD%';
TABLE_OWNER TABLE_NAME TABLESPACE_NAME PARTITION_NAME PARTITION_POSITION SEGM
----------------- ------------------------- --------------------- --------------------- ---------------------------------- ------
AUDSYS AUD$UNIFIED SYSAUX SYS_P8561 1 YES
SYS@ORCLCDB> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS';
OWNER TABLE_NAME INTERVAL PARTITIONING_TYPE PARTITION_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- -------------------------------------------------- ------------------ --------------- ------------------------------------------------------------
AUDSYS AUD$UNIFIED NUMTODSINTERVAL(1, 'DAY') RANGE 1048575 SYSAUX
col TABLESPACE_NAME for a15
col LOB_INDPART_NAME for a15
col LOB_PARTITION_NAME for a15
col TABLE_OWNER for a10
col LOB_NAME for a30
select table_owner,table_name,tablespace_name,lob_partition_name,lob_name,lob_indpart_name,partition_position,segment_created,in_row,lob_indpart_name from dba_lob_partitions where table_owner='AUDSYS';
TABLE_OWNE TABLE_NAME TABLESPACE_NAME LOB_PARTITION_N LOB_NAME LOB_INDPART_NAM PARTITION_POSITION SEGMEN IN_ROW LOB_INDPART_NAM
---------- -------------------- --------------- --------------- ------------------------------ --------------- ------------------ ------ ------ ---------------
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P139 SYS_LOB0000018570C00030$$ SYS_IL_P140 1 NO YES SYS_IL_P140
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P182 SYS_LOB0000018570C00030$$ SYS_IL_P183 2 YES YES SYS_IL_P183
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P772 SYS_LOB0000018570C00030$$ SYS_IL_P773 3 YES YES SYS_IL_P773
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1421 SYS_LOB0000018570C00030$$ SYS_IL_P1422 4 YES YES SYS_IL_P1422
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1921 SYS_LOB0000018570C00030$$ SYS_IL_P1922 5 YES YES SYS_IL_P1922
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P2561 SYS_LOB0000018570C00030$$ SYS_IL_P2562 6 YES YES SYS_IL_P2562
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P141 SYS_LOB0000018570C00031$$ SYS_IL_P142 1 NO YES SYS_IL_P142
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P184 SYS_LOB0000018570C00031$$ SYS_IL_P185 2 YES YES SYS_IL_P185
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P774 SYS_LOB0000018570C00031$$ SYS_IL_P775 3 YES YES SYS_IL_P775
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1423 SYS_LOB0000018570C00031$$ SYS_IL_P1424 4 YES YES SYS_IL_P1424
SQL> select systimestamp from dual;
# 然后更改系统时间
[root@elk-master ~]$ date
Sun Apr 26 10:12:05 CST 2020
[root@elk-master ~]# date -s 05/26/20 <----跨月后,会新生成分区
Mon Apr 27 00:00:00 CST 2020
[root@elk-master ~]# date
Mon Apr 27 00:00:01 CST 2020
SYS@ORCLCDB>select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,HIGH_VALUE from DBA_tab_partitions where table_name='AUD$UNIFIED';
TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- -------------------- -------------------- --------------------------------------------------------------------------------
AUDSYS AUD$UNIFIED AUD_UNIFIED_P0 TIMESTAMP' 2014-07-01 00:00:00'
AUDSYS AUD$UNIFIED SYS_P1420 TIMESTAMP' 2020-03-01 00:00:00'
AUDSYS AUD$UNIFIED SYS_P1459572 TIMESTAMP' 2020-05-26 00:00:00'
AUDSYS AUD$UNIFIED SYS_P181 TIMESTAMP' 2020-01-01 00:00:00'
AUDSYS AUD$UNIFIED SYS_P1920 TIMESTAMP' 2020-04-01 00:00:00'
AUDSYS AUD$UNIFIED SYS_P2560 TIMESTAMP' 2020-05-01 00:00:00' <---------- 新生成的分区
AUDSYS AUD$UNIFIED SYS_P771 TIMESTAMP' 2020-02-01 00:00:00'
SYS@ORCLCDB>select table_owner,table_name,tablespace_name,partition_name,partition_position,segment_created,read_only from dba_tab_partitions where table_name like 'AUD%' and table_owner='AUDSYS';
TABLE_OWNE TABLE_NAME TABLESPACE_NAME PARTITION_NAME PARTITION_POSITION SEGMENT_ READ_ONL
---------- -------------------- --------------- -------------------- ------------------ -------- --------
AUDSYS AUD$UNIFIED SYSAUX AUD_UNIFIED_P0 1 NO NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P181 2 YES NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P771 3 YES NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P1420 4 YES NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P1920 5 YES NO
AUDSYS AUD$UNIFIED SYSAUX SYS_P2560 6 YES NO
AUDSYS AUD$UNIFIED TS_AUDIT SYS_P1459572 7 YES NO <----------
SYS@ORCLCDB>select table_owner,table_name,tablespace_name,lob_partition_name,lob_name,lob_indpart_name,partition_position,segment_created,in_row,lob_indpart_name from dba_lob_partitions where table_owner='AUDSYS';
TABLE_OWNE TABLE_NAME TABLESPACE_NAME LOB_PARTITION_N LOB_NAME LOB_INDPART_NAM PARTITION_POSITION SEGMEN IN_ROW LOB_INDPART_NAM
---------- -------------------- --------------- --------------- ------------------------------ --------------- -------------- ------ ------ ---------------
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P139 SYS_LOB0000018570C00030$$ SYS_IL_P140 1 NO YES SYS_IL_P140
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P182 SYS_LOB0000018570C00030$$ SYS_IL_P183 2 YES YES SYS_IL_P183
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P772 SYS_LOB0000018570C00030$$ SYS_IL_P773 3 YES YES SYS_IL_P773
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1421 SYS_LOB0000018570C00030$$ SYS_IL_P1422 4 YES YES SYS_IL_P1422
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1921 SYS_LOB0000018570C00030$$ SYS_IL_P1922 5 YES YES SYS_IL_P1922
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P2561 SYS_LOB0000018570C00030$$ SYS_IL_P2562 6 YES YES SYS_IL_P2562
AUDSYS AUD$UNIFIED TS_AUDIT SYS_LOB_P145957 SYS_LOB0000018570C00030$$ SYS_IL_P1459574 7 YES YES SYS_IL_P1459574
3
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P141 SYS_LOB0000018570C00031$$ SYS_IL_P142 1 NO YES SYS_IL_P142
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P184 SYS_LOB0000018570C00031$$ SYS_IL_P185 2 YES YES SYS_IL_P185
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P774 SYS_LOB0000018570C00031$$ SYS_IL_P775 3 YES YES SYS_IL_P775
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1423 SYS_LOB0000018570C00031$$ SYS_IL_P1424 4 YES YES SYS_IL_P1424
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1923 SYS_LOB0000018570C00031$$ SYS_IL_P1924 5 YES YES SYS_IL_P1924
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P2563 SYS_LOB0000018570C00031$$ SYS_IL_P2564 6 YES YES SYS_IL_P2564
AUDSYS AUD$UNIFIED TS_AUDIT SYS_LOB_P145957 SYS_LOB0000018570C00031$$ SYS_IL_P1459576 7 YES YES SYS_IL_P1459576
5
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P143 SYS_LOB0000018570C00097$$ SYS_IL_P144 1 NO YES SYS_IL_P144
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P186 SYS_LOB0000018570C00097$$ SYS_IL_P187 2 YES YES SYS_IL_P187
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P776 SYS_LOB0000018570C00097$$ SYS_IL_P777 3 YES YES SYS_IL_P777
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1425 SYS_LOB0000018570C00097$$ SYS_IL_P1426 4 YES YES SYS_IL_P1426
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P1925 SYS_LOB0000018570C00097$$ SYS_IL_P1926 5 YES YES SYS_IL_P1926
AUDSYS AUD$UNIFIED SYSAUX SYS_LOB_P2565 SYS_LOB0000018570C00097$$ SYS_IL_P2566 6 YES YES SYS_IL_P2566
AUDSYS AUD$UNIFIED TS_AUDIT SYS_LOB_P1459577 SYS_LOB0000018570C00097$$ SYS_IL_P1459578 7 YES YES SYS_IL_P1459578
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。