Data File

How Big a Data File Can Be

DB Blocks Auto Extend in a Data File

Maximum Size Limit of a Data File

According to Oracle Database Physical Database Limits, we can have approximate 222 database blocks for a data file in a smallfile tablespace (default) and 232 database blocks for a data file in a bigfile tablespace.

In fact, the exact allowable database blocks in a data file should be additionally deducted 2 and 3 blocks from 222 and 232 respectively.

  • Smallfile Data File
  • 2^22 -2 = 4194302 blocks.

  • Bigfile Data File
  • 2^32 -3 = 4294967293 blocks.

That is to say, the maximum size of a data file depends on the type of tablespace and the block size below.

Here are quick reference tables for maximum size limits of SMALLFILE and BIGFILE data files.

SMALLFILE Data File

Maximum Size Limit of a SMALLFILE Data File
Block Size (KB)Max BlocksMax Size (GB) *
4419430216
8419430232
16419430264
324194302128

BIGFILE Data File

Maximum Size Limit of a BIGFILE Data File
Block Size (KB)Max BlocksMax Size (TB) *
4429496729316
8429496729332
16429496729364
324294967293128

* The above maximum sizes in GB or TB are all rounded numbers, not exact ones. For real upper limits, you should additionally deduct 2 or 3 db blocks from those values. That is to say, the database allows only 2^22 -2 (4194302) blocks for a smallfile data file and 2^32 -3 (4294967293) blocks for a bigfile data file.

As we can see, SMALLFILE data files stay in Gigabyte (GB) grade whereas BIGFILE data files stay in Terabyte (TB) grade.

Default Block Size

To know the default block size of your database, you can do this:

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

Of course, you can also specify non-default BLOCKSIZE for your tablespaces at creation-time.

Default Tablespace Type

To know the default tablespace type of your database, you can do this:

SQL> select property_value from database_properties where property_name = 'DEFAULT_TBS_TYPE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
SMALLFILE

It’s SMALLFILE.

MAXSIZE Clause Considerations

Question: When should we care about the maximum size of a data file? One of which is to add new data files with MAXSIZE clauses in CREATE TABLESPACE and ALTER TABLESPACE statements. Here are some points that we should consider about when adding a new data file.

MAXSIZE 32G Problem

Theoretically, we can add a data file with maximum size of 32 GB to a tablespace with 8 KB block size. Actually, we can’t.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example02.dbf' size 1G autoextend on next 1G maxsize 32G;
alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example02.dbf' size 1G autoextend on next 1G maxsize 32G
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

Just like we said earlier in this post, 32 GB for a smallfile tablespace with 8 KB db blocks is just an approximate and rounded figure, not the accurate one. The exact number should be additionally deducted two blocks which is 8 * 2 = 16 KB:

32 GB – 16 KB = 33554432 KB – 16 KB = 33554416 KB

That is to say, you should use the exact upper limit or less to define MAXSIZE of the data file.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example02.dbf' size 1G autoextend on next 1G maxsize 33554416K;

Tablespace altered.

My suggestion is that you should use UNLIMITED value to define the data file. It’s safer than specifying a number. I’ll talk about this later.

MAXSIZE 32T Problem

If you did specify a value that exceeded the maximum size of a data file, the database refuses to add the data file.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example03.dbf' size 1G autoextend on next 1G maxsize 32T;
alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example03.dbf' size 1G autoextend on next 1G maxsize 32T
*
ERROR at line 1:
ORA-00740: datafile size of (4294967296) blocks exceeds maximum file size

ORA-00740 prevents us from adding a SMALLFILE data file with wrong MAXSIZE.

If you really found a data file with a maximum size of 32 TB in your database, it should belong to a BIGFILE tablespace. Mostly, someone miscreated it without notifying DBA.

MAXSIZE UNLIMITED

Don’t be scared by MAXSIZE UNLIMITED value when you add a data file to a tablespace, because the data file won’t be really unbounded and uncontrollable. MAXSIZE UNLIMITED means that the tablespace is capable of extending the data file as far as it can, the maximum size listed above is the ceiling. For a tablespace with 8 KB blocksize, the maximum size of the data file can reach is around 32 GB, no more.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example03.dbf' size 1G autoextend on next 1G maxsize unlimited;

Tablespace altered.

Next, we can use these values to estimate how big a tablespace can be.

Leave a Reply

Your email address will not be published. Required fields are marked *