I'm writing a short blog post today as part of the Joel Kallman Day 2025.
Oracle Database Express Edition (XE) is the free edition of the Oracle Database. And some time ago, Oracle started calling it the "Free" database instead of the "XE" database, but it's basically the same thing.
It's free to develop with, and free to deploy to production. It has limitations, and there is no support except through the community, but otherwise it's a great database for many use cases.
One of the limitations is disk space. Your own user data (usually in the USERS tablespace) plus some system data (in the SYSAUX tablespace) cannot exceed 12 GB.
A potential problem is that since Oracle XE comes with a lot of the same bells and whistles as the Enterprise Edition (EE), there are many features that generate a lot of "unnecessary" data in the SYSAUX tablespace, at least in the context of a typical XE environment.
Connor McDonald has several blog posts, including this one, which go into this in great detail, so I won't repeat everything here.
The point of this blog post is just to let you know that in order to avoid unrestricted SYSAUX growth in your Oracle XE/Free Database, you need to change several default settings and turn features off. In addition to what Connor has listed in his blog post (see above), I have also found another component (the "Auto STS Capture Task") that needs to be disabled to avoid filling up the SYSAUX tablespace.
Here, then, is the script I use right after installing Oracle XE:
-- NOTE: this should be done in the pluggable database (XEPDB1 or FREEPDB1), not in the container database (CDB$ROOT)
alter session set container = xepdb1;
-- create a separate tablespace for audit info
create tablespace audit_tbs1 datafile '/opt/oracle/oradata/XE/XEPDB1/audit01.dbf' size 20m autoextend on next 2m;
begin
dbms_audit_mgmt.set_audit_trail_location (audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std, audit_trail_location_value=>'AUDIT_TBS1');
dbms_audit_mgmt.set_audit_trail_location (audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std, audit_trail_location_value=>'AUDIT_TBS1');
dbms_audit_mgmt.set_audit_trail_location (audit_trail_type => dbms_audit_mgmt.audit_trail_db_std, audit_trail_location_value=>'AUDIT_TBS1');
dbms_audit_mgmt.set_audit_trail_location (audit_trail_type => dbms_audit_mgmt.audit_trail_unified, audit_trail_location_value=>'AUDIT_TBS1');
end;
/
begin
-- lower retention periods for various components
dbms_audit_mgmt.set_last_archive_timestamp (audit_trail_type=>dbms_audit_mgmt.audit_trail_unified, last_archive_time=>sysdate-7);
dbms_workload_repository.modify_baseline_window_size (window_size =>7);
dbms_workload_repository.modify_snapshot_settings (retention=>7*1440);
dbms_stats.alter_stats_history_retention (7);
dbms_scheduler.set_scheduler_attribute ('log_history',7);
end;
/
-- disable Stats Advisor
begin
dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
end;
/
-- disable capture of SQL workloads (only used for Automatic Indexing which is turned off by default)
begin
dbms_auto_task_admin.disable (client_name => 'Auto STS Capture Task', operation => NULL, window_name => NULL);
end;
/
And if you use APEX (and who doesn't?!?!), you should also install APEX into its own tablespace:
-- NOTE: this should be done in the pluggable database (XEPDB1 or FREEPDB1), not in the container database (CDB$ROOT)
alter session set container = xepdb1;
-- create a separate tablespace for APEX (version 22.2 in this case)
create tablespace apex22_tbs1 datafile '/opt/oracle/oradata/XE/XEPDB1/apex22.dbf' size 20m autoextend on next 20m;
-- when you install APEX, specify the new tablespace
@apxins.sql APEX22_TBS1 APEX22_TBS1 TEMP /i/
Hopefully this will be useful to someone. Happy Joel Kallman Day! :-)
