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