Active5 years ago
Jan 11, 2012 Maximum Datafile Size In Oracle Database Each Oracle datafile can contain maximum (2^22) i.e., 4194303 (4 Million) data blocks. So maximum file size is 4194303 multiplied by the database block size. In a database there can have maximum of 65533 data files. In database, dbblocksize can have 2K, 4K, 8K, 16K and 32K.
Currently my Oracle 11g temp TABLESPACE value is 34GB. I need to increase the table space value to a large value (45GB)
I tired the following sql command to increase the temp table space.
The error:
SQL Error: ORA-01144: File size (5536951 blocks) exceeds maximum of 4194303 blocks 01144. 00000 - 'File size (%s blocks) exceeds maximum of %s blocks' *Cause: Specified file size is larger than maximum allowable size value. *Action: Specify a smaller size.
The 'db_block_size' value is 8192
- How do I decide the maximum allowed db_block_size and the corresponding temp TABLESPACE value
- How do I increase the TEMP tablespace?
Ullan
UllanUllan53477 gold badges1515 silver badges3232 bronze badges
1 Answer
The error message is pretty clear, the maximum file size is 4194303 blocks. If you multiply that out
So you're limited to individual data/ temp files of up to 32 GB. You can, however, have thousands of data files in a tablespace. So you could have a 32 GB temp file and another 13 GB temp file or 2 22.5 GB temp files or 9 5 GB temp files.
![Ora 1144 signalled during alter database Ora 1144 signalled during alter database](http://online.anyflip.com/lxos/ilae/files/thumb/1.jpg)
194k1919 gold badges306306 silver badges332332 bronze badges
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.