How to increase the TEMP TABLE Space value in Oracle?

Question

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.

    ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp01.dbf' SIZE  45G

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.

    SELECT value FROM v$parameter WHERE name = 'db_block_size';

The "db_block_size" value is 8192

1. How do I decide the maximum allowed db_block_size and the corresponding temp TABLESPACE value
2. How do I increase the TEMP tablespace?

Answer 1

The error message is pretty clear, the maximum file size is 4194303 blocks. If you multiply that out

4194303 blocks * 8192 bytes/ block / 1024^3 = 32 GB

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.