Skip to Main Content
  • Questions

  • Oracle Database XE 18c User Data Limitations: how to get user data size to check a limit?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alexander.

Asked: July 13, 2020 - 1:20 pm UTC

Last updated: May 23, 2022 - 3:43 am UTC

Version: 18c

Viewed 1000+ times

You Asked

Hello!

As it's said https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinl/oracle-database-xe-user-data-limitations.html:
"user data in an Oracle Database XE database cannot exceed 12 gigabytes".

Would You explain how to get exactly that user data size by means of SQL?
Would You also clarify should we add here separate APEX tablespace's size?

Thank You.

and Connor said...

I don't know the exact algorithm but my understanding is we are basically assume an approximate upper limit on what the base dictionary should be, and taking that into account as to "not counting" toward your limit. We don't base it on what schema/tablespace you use. For example, even if I create objects under the SYS schema, then eventually:

SQL> create table t as select d.* from dba_objects d, ( select 1 from dual connect by level <= 300 );
create table t5 as select d.* from dba_objects d, ( select 1 from dual connect by level <= 300 )
                                   *
ERROR at line 1:
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.


and I got this error when my SYSTEM tablespace got to 13.1G and my entire database was

SQL> select sum(bytes)/1024/1024/1024
  2  from cdb_data_files;

SUM(BYTES)/1024/1024/1024
-------------------------
               14.5166016


So there's some leeway. My APEX 20.1 install consumes around 350MB so even if it counts toward your total, I really don't think you'd want to be in a spot where 350MB is make or break for your app - that's running too close to the wire.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alexander, July 24, 2020 - 11:52 am UTC

Appreciate for answer.

My mistake was a hope to get formally correct answer from Oracle Corp. itself here )))
I guess, if fact, nobody cares about DB Express sizes, so even AskTom have just close to truth opinion.

I do beleive that Your answer is the closest to truth one.

Disk Usage on XE

Amin Adatia, May 19, 2022 - 11:58 am UTC

You could use ADVANCED COMPRESSION

CREATE BIGFILE TABLESPACE DATABF01
DATAFILE '/opt/oracle/oradata/XE/XEPDB1/databf01.dbf'
SIZE 1G
AUTOEXTEND ON NEXT 1K MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO
-- DEFAULT ROW STORE COMPRESS ADVANCED
-- DEFAULT INDEX COMPRESS ADVANCED HIGH
LOGGING
/

CREATE BIGFILE TABLESPACE DATABF02
DATAFILE '/opt/oracle/oradata/XE/XEPDB1/databf02.dbf'
SIZE 1G
AUTOEXTEND ON NEXT 1K MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT ROW STORE COMPRESS ADVANCED
-- DEFAULT INDEX COMPRESS ADVANCED HIGH
LOGGING
/

CREATE BIGFILE TABLESPACE DATABF03
DATAFILE '/opt/oracle/oradata/XE/XEPDB1/databf03.dbf'
SIZE 1G
AUTOEXTEND ON NEXT 1K MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO
-- DEFAULT ROW STORE COMPRESS ADVANCED
DEFAULT INDEX COMPRESS ADVANCED HIGH
LOGGING
/

Connor McDonald
May 23, 2022 - 3:43 am UTC

agreed. You get (almost) *all* the EE features on XE, so it makes sense to exploit them

Disk Usage on XE

Amin Adatia, May 19, 2022 - 11:59 am UTC

Would apply on XE21c
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database