Oracle Database - Can I create a table and reserve some space for it?

Question

Business Case:

We have an application that creates backups of its data. Each time that a user creates a backup, a registry is created in another table (like a historical table) ...

Right now, we working with Oracle XE 18c. As you may know, OracleXE has fixed limit of 12GB of data ... If you try to insert something once you have reached such limit, you will receive an exception like this:

ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

So, we must be able to create our backup, even if the database has reached those 12GB ...

Now, we're in a crossroad: either we refactor the process to avoid adding that registry into the database or we find a more elegant solution. The solution that I was thinking is like creating the historical table but reserving some space for it, allowing adding that extra registry when the 12GB limits has been reached.

According to the Oracle documentation, there is clause clause called STORAGE and other types of configurations ... So, my question is: Will it be possible to do what I thinking? Like reserving space for the table before any record is stored in it (avoiding to refactor the application's process?) and leaving enough space to store a couple of records, even if the database has reached the 12GB limit?

Thanks!

Answer 1

SQL> create table t1(c1 number);

Table created.

SQL> alter table t1 allocate extent (size 1G);

Table altered.

SQL> select bytes/1024/1024 as size_mb from user_segments
     where segment_name = 'T1';

   SIZE_MB
----------
 1024.0625

SQL>