How to reduce sysaux tablespace size with Audit Trail Unified enabled

oracletablespaces

I have Oracle 12c Standard instance with dev database with enabled Audit Trail Unified.

And now SYSAUX tablespace has size 32Gb, but all data is only 9Gb.
When I tried to reduce size of SYSAUX I received an error message:

ORA-03297: file contains used data beyond requested RESIZE value

Then I saw that some big sized objects in this tablespace:
enter image description here

I've executed the following scripts:

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   last_archive_time => SYSDATE-1,
   rac_instance_number => 1 /* single instance database */),
   container            => DBMS_AUDIT_MGMT.container_current;
END;
/      


BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;   
/

After this in AUDSYS."CLI_SWPXXXXXXX" table I don't see any rows.
But it still have the same size .

Also any attempts to shrink this table or move it to another tablespace give me an error message:
ORA-55941: DML and DDL operations are not allowed on table "AUDSYS"."CLI_SWPXXXXXXX"

So, my question is: can I make size of SYSAUX tablespace smaller or move this audit tables to another tablespace?

Update: by this link: click I've found info that "Moving CLI.SWP possible only for Enterprise edition, for standard impossible due partitioning." Is that true? It seems like my instance is Standard…

Best Answer

SQL> select owner||'."'||table_name||'"' t from dba_tables where table_name like 'CLI_SWP$%';

T
-----------------------------
AUDSYS."CLI_SWP$18c7c2a9$1$1"

SQL> truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1";
truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1"
*
FEHLER in Zeile 1:
ORA-55941: DML- und DDL-Vorgänge sind auf Tabelle
"AUDSYS"."CLI_SWP$18c7c2a9$1$1" nicht zulässig

SQL> shutdown immediate;
…
ORACLE-Instanz heruntergefahren.
SQL> startup upgrade;
…
Datenbank geöffnet.
SQL> truncate table AUDSYS."CLI_SWP$18c7c2a9$1$1";

Tabelle mit TRUNCATE geleert.

SQL> shutdown immediate;
SQL> startup;
Related Question