[20230305]AUDSYS.AUD$UNIFIED ORA-46385.txt

[20230305]AUDSYS.AUD$UNIFIED ORA-46385.txt --//这段时间一直在学习UNIFIED audit，检索发现链接http://www.itpub.net/thread-2121998-1-1.html, --//可以看出对于表AUDSYS.AUD$UNIFIED,用户无法执行DML,报ora-46385错误，当时自己工作并没有接触19c。 --//现在有机会测试看看。 1.环境: SYS@test01p> @ ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0 2.测试: SYS@test01p> delete from audsys.AUD$UNIFIED ; delete from audsys.AUD$UNIFIED                    * ERROR at line 1: ORA-46385: DML and DDL operations are not allowed on table "AUDSYS"."AUD$UNIFIED". d:\>oerr ora 46385 46385, 00000, "DML and DDL operations are not allowed on table \"%s\".\"%s\"." // *Cause: A DML or DDL operation was attempted on a unified auditing //         internal table. // *Action: No action required. Only Oracle is allowed to perform such //          operations on a unified auditing internal table. --//Only Oracle is allowed to perform such operations on a unified auditing internal table. --//怎么意思？ --//以前的测试执行如下： --//exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,USE_LAST_ARCH_TIMESTAMP => TRUE); --//检索跟踪文件发现如下： $ egrep -i 'drop |delete' aa.trc | grep -i 'AUDSYS.AUD\$UNIFIED' CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P18918') ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P18918 delete from audsys.aud$unified where event_timestamp < :1 and  (dbid = :2 or dbid = 0) --//可以大致猜测执行步骤,根据设置的时间点,如果可以删除分区直接drop分区.然后剩下的选择直接从表audsys.aud$unified删除. --//测试使用CALL DBMS_PDB_EXEC_SQL('...') SYS@test01p> @ o2 audsys.AUD$UNIFIED owner  object_name object_type     SUBOBJECT_NAME status   OID D_OID CREATED             LAST_DDL_TIME ------ ----------- --------------- -------------- ------ ----- ----- ------------------- ------------------- AUDSYS AUD$UNIFIED TABLE                          VALID  17884       2018-10-06 21:57:46 2023-03-01 21:10:18 AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P6188      VALID  29875 29875 2023-03-01 20:57:53 2023-03-01 20:57:53 AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P6208      VALID  29885 29885 2023-03-02 21:04:16 2023-03-02 21:04:16 AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P6108      VALID  29815 29870 2023-02-16 22:01:12 2023-02-24 22:47:08 SYS@test01p> CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P6108'); CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P6108')      * ERROR at line 1: ORA-46385: DML and DDL operations are not allowed on table "AUDSYS"."AUD$UNIFIED". ORA-06512: at "SYS.DBMS_PDB", line 33 ORA-06512: at "SYS.DBMS_PDB_EXEC_SQL", line 4 --//还是不行.oracle是如何实现这样奇葩的控制的呢  跟踪看看. SYS@test01p> @ 10046on 12 Session altered. SYS@test01p> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,USE_LAST_ARCH_TIMESTAMP => false); PL/SQL procedure successfully completed. SYS@test01p> @ 10046off Session altered. d:\>egrep -i "drop |delete" D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_8732.trc | grep -i "AUDSYS.AUD\$UNIFIED" CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P6108') ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P6108 CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P6188') ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P6188 CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P6208') --//看了半天也不知道oracle如何实现的,也许是代码写死了,检索发现如下链接： --//https://www.dbi-services.com/blog/purging-unified-audit-trail-in-12cr2/ --//https://www.dbi-services.com/blog/12c-unified-auditing-and-audit_traildb-in-mixed-mode/ --//https://positivemh.tistory.com/497 --//http://www.itpub.net/thread-2121998-1-1.html --//要想执行dml，可以选择upgrade模式下执行DML，测试如下： SYS@test01p> shutdown immediate Pluggable Database closed. SYS@test01p> startup upgrade; Pluggable Database opened. SYS@test01p> truncate table AUDSYS.AUD$UNIFIED; Table truncated. SYS@test01p> shutdown immediate Pluggable Database closed. SYS@test01p> startup upgrade; Pluggable Database opened. SYS@test01p> delete from audsys.AUD$UNIFIED ; 1 row deleted. SYS@test01p> commit ; Commit complete.