How Max a Data File Can Be

DataFile MaxSize

According to Oracle Database Physical Database Limits, we can have approximate 2²² database blocks for a data file in a smallfile tablespace (default) and 2³² 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 2²² and 2³² 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.

Is it always maxsize 32GB for a data file? Here are quick reference tables for maximum size limits of SMALLFILE and BIGFILE data files.

SMALLFILE Data File

By default, oracle datafile max size 32gb for db block size with 8KB is the most commonly used practice.

BIGFILE Data File

As you can see, the bigfile tablespace max size limit for 8KB db blocks is 32 TB.

* 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:

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:

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.

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:

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

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.

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.

Next, we can use these values to estimate how big a tablespace can be. Beside the size limit of a data file, there're some other restrictions on adding a data file. Also, you might be surprised at what we can see in a normal data file.