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:

   last_archive_time => SYSDATE-1,
   rac_instance_number => 1 /* single instance database */),
   container            => DBMS_AUDIT_MGMT.container_current;

use_last_arch_timestamp => TRUE);

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

  • 2
    One of the first things I do on a new database is moving the audit trail to a seperate tablespace (DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION), you could do the same. After that you may still have extents from other segments left at the end of the datafile though. You will need to move those also to be able to shrink the datafile. Commented Nov 15, 2016 at 13:33
  • Done it. Now new tablespace contains AUD$ and FGA_LOG$ objects. But I still can't resize SYSAUX, because Unified Audit objects still in SYSAUX tablespace... Any idea how to move it to user tablespace on Standard Oracle instance?
    – whitebeast
    Commented Nov 15, 2016 at 13:51
  • Was this issue fixed mate? I am having a exactly same issue at the moment.
    – user221803
    Commented Jan 12, 2021 at 14:13

1 Answer 1

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


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;
  • 3
    It might help if you could explain what you are doing in your script by separating the commands with comments. (example: First determine the table name of the AUDSYS schema used for auditing. <Code> Then truncate the table with <Code> here. This could fail because <explain why>. You can work around this by following these quick steps: <code here>.) Then it might help if you could change the error messages to the English equivalents: ORA-55941: DMD and DDL operations are not allowed on table AUDSYS."CLI_....."
    – John K. N.
    Commented Jan 25, 2018 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.