Oracle 11G XE and the 11 gigabyte limit


11g-xe

Hello,
I have been often been asked how the Oracle 11G XE database Storage limit works exactly. So I would just post an example here to clarify.

Here’s an example:

We create a tablespace where the limit exceeds 11 gigabytes and we get an ORA-12953:

SQL> create tablespace appdata datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' size 18G;
 create tablespace appdata datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' size 18G
 *
 ERROR at line 1:
 ORA-12953: The request exceeds the maximum allowed database size of 11 GB

We try to create 2 tablespaces, which exceed the 11 gigabytes and we also get a ORA-12953:

SQL> create tablespace appdata datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' size 10G;
Tablespace created.
SQL> create tablespace appindex datafile '/u01/app/oracle/oradata/XE/appindex01.dbf' size 5G;
 create tablespace appindex datafile '/u01/app/oracle/oradata/XE/appindex01.dbf' size 5G
 *
 ERROR at line 1:
 ORA-12953: The request exceeds the maximum allowed database size of 11 GB

Also, the resize command is monitored here and terminated with an ORA-12953:

SQL> alter database datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' resize 15G;
alter database datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' resize 15G
*
ERROR at line 1:
ORA-12953: The request exceeds the maximum allowed database size of 11 GB

On the other hand (APP Data tablespace with 10G still exists), we enlarge our TEMP tablespace, this does not play in the overall size with:

SQL> alter database tempfile 1 resize 5G;
Database altered.

The same applies to the UNDO tablespace (again, there is the app tablespace with 10 gigabytes).

SQL> select tablespace_name from dba_data_files where file_id = 3;

TABLESPACE_NAME
------------------------------
UNDOTBS1

SQL> alter database datafile 3 resize 5G;
Database altered.
We are able to alter the datafile with the autoextend clause  to 15GB, but when you attempt to create a segment which is large then 11 GB it is prevented with a ORA-12953:
SQL> alter database datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' autoextend on maxsize 15G;

Here is a example whats happen when we create a segment with more than 11G.

insert into test select * from test
*
ERROR at line 1:
ORA-12953: The request exceeds the maximum allowed database size of 11 GB

Conclusion:
The sample should show that it is about the size of all datafiles of the database (except TEMP and UNDO tablespace). It is also possible to create a data file which eg Is 10GB in size and may grow by the autoextend over 11GB. But in this case the Oracle XE limit it to 11GB which I have also show here.

I hope that this provides some clarity.
As always, I welcome comments

Advertisements

4 Gedanken zu “Oracle 11G XE and the 11 gigabyte limit

  1. Hello Peter!

    I have more tables, where I load data (up to 2 GB) and after processing I delete them. But the used segments are not free. So I reached the 11GB limit, althoug I have not 11GB data.
    I’ve found 2 solutions: when the table is emty, truncate frees the space.
    If the table is not empty, shrink space will free the segments.
    But do you have an idea why the segments are not free?
    (I tried tablespace with nologging, table with no logging but still the same behavior)

    • Hello Herbert,
      First thing, nologging can`t help here, because this also reduce your redo generation. From your problem description i think you have a problem with your high water mark. When you insert data into a table oracle maintains a upper boundary of that table which is called high water mark.
      Now when you delete records, oracle does not reset the high water mark immedediatly. The other thing is, when you do a truncate oracle knows that the table is empty and is resetting the high water mark (default truncate). Check out MOS for more information about the high water mark. The next thing you can check is, how did you insert your data. For example using the append hint speeds up your inserts, but will insert your data behind the high water mark which can also a problem is your environment.

      regards
      Peter

  2. I have heard that data can be entered although you have breached 11gb size limit. Want some clarification for it?
    or is it that after 11gb extra size is under non compliant

    • Hello,
      if you try to insert or update data, this works as long the database did not need to extend the datafiles. In the moment the database extends the datafile and the real size exceeds 11G you get a ORA-12953.

      regards
      Peter

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s