Skip to content
Home » Oracle Database » How Max a Tablespace Can Be

How Max a Tablespace Can Be

Add DataFile in a Tablespace

Tablespace Max Size

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. Now the question is, How many datafiles can a tablespace have?

According to Oracle, we can have at most 1022 datafiles in a SMALLFILE tablespace, whereas only 1 datafile 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

Maximum Size Limit of a SMALLFILE Tablespace
Block Size (KB)Data File Max Size (GB)Max Data FilesTablespace Max Size (TB)
416102215.97
832102231.94
1664102263.87
321281022127.75

BIGFILE Tablespace Maxsize

Maximum Size Limit of a BIGFILE Tablespace
Block Size (KB)Data File Max Size (TB)Max Data FilesTablespace Max Size (TB)
416116
832132
1664164
321281128

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.

Leave a Reply

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