1

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

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • 1
    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. – Balazs Papp Nov 15 '16 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 Nov 15 '16 at 13:51
0
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;
  • 1
    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_....." – hot2use Jan 25 '18 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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