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