DataFile MaxSize
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
- Bigfile Data File
2^22 -2 = 4194302 blocks.
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
| Block Size (KB) | Max Blocks | Max Size (GB) * |
|---|---|---|
| 4 | 4194302 | 16 |
| 8 | 4194302 | 32 |
| 16 | 4194302 | 64 |
| 32 | 4194302 | 128 |
By default, oracle datafile max size 32gb for db block size with 8KB is the most commonly used practice.
BIGFILE Data File
| Block Size (KB) | Max Blocks | Max Size (TB) * |
|---|---|---|
| 4 | 4294967293 | 16 |
| 8 | 4294967293 | 32 |
| 16 | 4294967293 | 64 |
| 32 | 4294967293 | 128 |
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.
DB_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.
Archive (redo) block size is not the same as the database block size, which is usually the default size and can be customized.
Default Tablespace Type
To know the default tablespace type of your database, you can query DATABASE_PROPERTIES to know it.
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:
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. 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.