We all know that temporary tablespaces use sparse files (specified using the TEMPFILE keyword in the ALTER/ADD TABLESPACE command) meaning that the actual file created on disk is initially very small and grows to the specified size only as data is written to the file.
However, I was was working on a system that was low on disk space and needed to add 20GB of space to the database’s default temporary tablespace. In this situation I wanted to make sure that the entire 20GB was pre-allocated on disk and assigned to the temporary tablespace immediately — but how to do it?
There isn’t any way of specifying to Oracle that the tempfile should be pre-allocated, so the solution I came up with was to create a normal datafile (via a transitory tablespace) and then add it to the temporary tablespace using the REUSE clause as follows:
create tablespace MY_TBLSP datafile '/oracle/oradata/DEV/temp.dbf004' size 20480m; drop tablespace MY_TBLSP; alter tablespace TEMP add tempfile '/oracle/oradata/DEV/temp.dbf004' reuse;