For Oracle database, Maximum database file size is operating system dependent and limited by maximum operating system file size.
When you add a new data file to a tablespace and see this error, it means the size of data file you specify exceeds the maximum limit.
Then how do you know what the maximum size of data file is you can choose? It is relates to DB_BLOCK_SIZE. The formula is
maximum size = DB_BLOCK_SIZE * 4194303
The following table gives the corresponding maximum size of data file for given DB_BLOCK_SIZE
|DB_BLOCK_SIZE||Maximum data file size|
|2kb||8G – 2kb|
|4kb||16G – 4kb|
|8kb||32G – 8kb|
|16kb||64G – 16kb|
|32kb||128G – 32kb|
Note it’s minus sign (e.g. 8G minus 2kb) in the table. So it’s always one corresponding data block short for a given DB_BLOCK_SIZE.
If you want to go over the limits above, you may consider the Bigfile tablespace introduced in Oracle 10g.
- Oracle 21c Physical Database Limits
- ORA-01144 on Create Tablespace or Resize of Datafile (Doc ID 104280.1)