Maximum Size Limit of a Tablespace
Basically, at least one data file should be specified in the tablespace creation, thereafter we can add more data files to the tablespace.
After knowing how big a data file can be in the previous post, we can continue to calculate how big a tablespace can be. According to Oracle, we can have at most 1022 data files in a SMALLFILE tablespace, whereas only 1 data file for a BIGFILE tablespace. That is to say, the maximum size of a data file depends on the type of tablespace, the block size and the number of data files.
Here are quick reference tables for maximum size limits of SMALLFILE and BIGFILE tablespaces.
SMALLFILE Tablespace Maxsize
| Block Size (KB) | Data File Max Size (GB) | Max Data Files | Tablespace Max Size (TB) |
|---|---|---|---|
| 4 | 16 | 1022 | 15.97 |
| 8 | 32 | 1022 | 31.94 |
| 16 | 64 | 1022 | 63.87 |
| 32 | 128 | 1022 | 127.75 |
BIGFILE Tablespace Maxsize
| Block Size (KB) | Data File Max Size (TB) | Max Data Files | Tablespace Max Size (TB) |
|---|---|---|---|
| 4 | 16 | 1 | 16 |
| 8 | 32 | 1 | 32 |
| 16 | 64 | 1 | 64 |
| 32 | 128 | 1 | 128 |
In other words, both SMALLFILE and BIGFILE tablespaces are in Terabyte (TB) grade.
Next, we can use these values to estimate how big a database can be.