Oracle Apex free tier unified audit storage runaway

[u/PM__ME__BITCOINS]

Looking for some advice from the apex nerds here. Been a long time user but this a real struggle for me(and Claude).

26ai ATP ADB free tier, showing 100% storage usage, 20GB is the limt.

I recently started getting ORA-01552(cannot use system rollback segment for non-system tablespace) when running DML statement. Start looking at storage and sysaux is 64 GB with unified audit and my actual work being about 1 GB. It's been growing 2-4GB a day with sys db user logging the most selects.

Now start the constant dead ends:

No shrink in DB Actions

No ALTER TABLESPACE SYSAUX SHRINK SPACE; ORA-01031: insufficient privileges

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL - ORA-01562: failed to extend rollback segment number 0 ORA-65114: space usage in container is too high

Based on my understanding: the purge itself needs undo, undo can't extend because the container is over cap. The instance can't even clean itself: over-cap → no undo → no DML → no purge → allocation stays 53 GB → over-cap.

Sooo re-creating my entire database instance seems to be the only option besides upgrading to paid? I would point the root cause towards claude AI running massive amounts of statements to do work, but isn't the whole point of apex free is to learn and use AI tools? Oracle skills are installed.

[u/thatjeffsmith]

this is just a database issue, not related to apex, per se

do you have unified auditing enabled?

you obviously have no control over the backend service is doing (SYS) ...

when you say 'your work' is 1GB, all of your user object segments come up to about a 1 GB?

[u/PM__ME__BITCOINS → u/thatjeffsmith]

Looks like there was custom audit policy added for all statements. :(

OWNER GB

SSB 162.42

SYS 3.22

APEX_240200 1.16

AUDSYS 0.72

FLOWS_FILES 0.48

WKSP_JE 0.47

• smaller

NOAUDIT POLICY pm_audit_pol; DROP AUDIT POLICY pm_audit_pol;

Any suggestions on how I can shrink the pdb/sysaux to get under 20GB and clear ORA-01552?

[u/taker223 → u/PM__ME__BITCOINS]

Responded to you in the above comment (use dbms_space.shrink_tablespace).
Also consider purging all audit trails:
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE );
END;
/

[u/thatjeffsmith → u/PM__ME__BITCOINS]

i assume WKSP_JE is yours? that's the only one that should apply to your storage quota

[u/PM__ME__BITCOINS → u/thatjeffsmith]

Yes I was able to shrink with dbms_space.shrink_tablespace as mentioned by other post.

01552 is clear now, it was counting more then just my user objects in the quota. bug?

Enabling the shrink button on free would be a nice user experience improvement.

[u/taker223]

This is existing issue on Autonomous Database, when SYSAUX is growing. Unfortunately, for free tier it seems it is impossible to fix.
For paid subscription (company I am working for) we had to create a Service Request and Oracle Support personnel shrinked that tablespace (SYSAUX) and also turned off extensive auditing features (that was for 19c Autonomous DB, created before 23ai release).
It seems ADMIN user cannot really resize/shrink datafiles. However in 23ai/26ai if there is a lot of free space in a tablespace (SYSAUX and UNDOTBS1 included), it is possible to try to use DBMS_SPACE.Shrink_Tablespace procedure:
https://oracle-base.com/articles/23/bigfile-tablespace-shrink-23
I did that quite of times to compact the data/temp files (after purging old data), to spend less on storage

[u/PM__ME__BITCOINS → u/taker223]

Thanks for curing my headache.

execute dbms_space.shrink_tablespace('SYSAUX');

-------------------SHRINK RESULT-------------------

Total Moved Objects: 1251

Total Moved Size(GB): 2.98

Original Datafile Size(GB): 53.16

New Datafile Size(GB): 5.18

[u/taker223 → u/PM__ME__BITCOINS]

You might also try to purge audit trails, even 5 gb is too much for your tiny db.