[20230403]学习UNIFIED audit--验证清理AUDSYS.AUD$UNIFIED.txt

[20230403]学习UNIFIED audit--验证清理AUDSYS.AUD$UNIFIED.txt --//前一阵子写了定期清理AUDSYS.AUD$UNIFIED的schedule.链接 --//http://blog.itpub.net/267265/viewspace-2937975/=>[20230303]学习UNIFIED audit--定期清理AUDSYS.AUD$UNIFIED.txt BEGIN   SYS.DBMS_SCHEDULER.CREATE_JOB     (        job_name        => 'SYS.JOB_PURGE_AUDIT_RECORDS'       ,start_date      => TO_TIMESTAMP_TZ('2023/03/03 09:07:24.000000 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzr')       ,repeat_interval => 'freq=monthly;bydate=0101,0201,0301,0401,0501,0601,0701,0801,0901,1001,1101,1201;byhour=8; byminute=50; bysecond=0;'       ,end_date        => NULL       ,job_class       => 'DEFAULT_JOB_CLASS'       ,job_type        => 'PLSQL_BLOCK'       ,job_action      => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,add_months(trunc(sysdate,''mm''),-2)); DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,USE_LAST_ARCH_TIMESTAMP=>TRUE); END;'       ,comments        => 'Update last_archive_timestamp and clean unified audit trail'     ); END; / --//今天看看效果: SYS@192.168.100.141:1521/dyhis> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. SYS@192.168.100.141:1521/dyhis> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS; AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS                                                             DATABASE_ID CONTAINER_GUID -------------------- ------------ --------------------------------------------------------------------------- ----------- --------------------------------- UNIFIED AUDIT TRAIL             0 2023-02-01 00:00:00.000000 +00:00                                            4090373436 B60D258AC2D9EF54E0532A63A8C09F1F SYS@192.168.100.141:1521/dyhis> select add_months(trunc(sysdate,'mm'),1),add_months(trunc(sysdate,'mm'),-2) from dual ; ADD_MONTHS(TRUNC(SY ADD_MONTHS(TRUNC(SY ------------------- ------------------- 2023-05-01 00:00:00 2023-02-01 00:00:00 --//设置LAST_ARCHIVE_TS已经是2023-02-01 00:00:00. SYS@192.168.100.141:1521/dyhis> select * from DBA_SCHEDULER_JOB_log where JOB_NAME='JOB_PURGE_AUDIT_RECORDS'   2  @ pr ============================== LOG_ID                        : 249070 LOG_DATE                      : 2023-04-01 08:50:07.083490 +08:00 OWNER                         : SYS JOB_NAME                      : JOB_PURGE_AUDIT_RECORDS JOB_SUBNAME                   : JOB_CLASS                     : DEFAULT_JOB_CLASS OPERATION                     : RUN STATUS                        : SUCCEEDED USER_NAME                     : CLIENT_ID                     : GLOBAL_UID                    : CREDENTIAL_OWNER              : CREDENTIAL_NAME               : DESTINATION_OWNER             : DESTINATION                   : ADDITIONAL_INFO               : PL/SQL procedure successfully completed. SYS@192.168.100.141:1521/dyhis> @ o2 AUDSYS.AUD$UNIFIED owner  object_name object_type          SEG_PART_NAME        status           OID      D_OID CREATED             LAST_DDL_TIME ------ ----------- -------------------- -------------------- --------- ---------- ---------- ------------------- ------------------- AUDSYS AUD$UNIFIED TABLE                                     VALID          18580            2020-10-20 10:28:13 2023-03-01 11:21:46 AUDSYS AUD$UNIFIED TABLE PARTITION      SYS_P19622           VALID         171804     171804 2023-01-01 08:00:04 2023-01-01 08:00:04 AUDSYS AUD$UNIFIED TABLE PARTITION      SYS_P20283           VALID         174234     174234 2023-02-01 08:00:00 2023-02-01 08:00:00 AUDSYS AUD$UNIFIED TABLE PARTITION      SYS_P20923           VALID         176513     176513 2023-03-01 08:00:05 2023-03-01 08:00:05 AUDSYS AUD$UNIFIED TABLE PARTITION      SYS_P21663           VALID         179544     179544 2023-04-01 08:00:01 2023-04-01 08:00:01 SYS@192.168.100.141:1521/dyhis> @ seg2 AUDSYS.AUD$UNIFIED SEG_MB OWNER  SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE         SEG_TABLESPACE_NAME     BLOCKS     HDRFIL     HDRBLK ------ ------ ------------ ------------- -------------------- ------------------- ---------- ---------- ----------    175 AUDSYS AUD$UNIFIED  SYS_P20923    TABLE PARTITION      SYSAUX                   22400          3     145690     16 AUDSYS AUD$UNIFIED  SYS_P21663    TABLE PARTITION      USERS                     2048          2      48657 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~        144 AUDSYS AUD$UNIFIED  SYS_P19622    TABLE PARTITION      SYSAUX                   18432          3     656234     88 AUDSYS AUD$UNIFIED  SYS_P20283    TABLE PARTITION      SYSAUX                   11264          3     550906 --//新建立的分区已经在users表空间. SYS@192.168.100.141:1521/dyhis> select trunc(EVENT_TIMESTAMP,'mm'),count(*) from audsys.AUD$UNIFIED group by trunc(EVENT_TIMESTAMP,'mm'); TRUNC(EVENT_TIMESTA   COUNT(*) ------------------- ---------- 2023-04-01 00:00:00      29666 2023-03-01 00:00:00     473349 2023-02-01 00:00:00     261572 --//可以发现2023-02-01 00:00:00日期之前的记录已经删除. --//验证了前面写的SCHEDULER JOB没有问题.