How to Create Non-Sparse TEMP files

Temp Files

Temporary data files in Oracle are a special type of data file. Oracle will use temporary files to store the intermdiate results of a large sort operation, and hash operations, as well as to store global temporary table data, or result data when there is insufficient memory to hold it all in RAM. Temp files also never have redo generated for them, though they can generate undo. Temporary data files never need to have backups done and doing so is a waste of time since upon recovery they need to be recreated.

Once of the nuances with temp files is that if the OS permits, the temporary files will be created sparse, i.e. they will not actually consume disk storage until they need to. This is mostly the case from my experience on UNIX/Linux systems and can be seen by comparing the output of df anddu or ls for the file system on which the temp files were created.

This results in temp files being created almost instantneously since no storage is allocated, however, since you can create files larger than the space you actually have available, you will quickly run into errors such as 'no more space' when that space is then being requested.

Creating non-sparse TEMP files

There are a few ways to create non-sparse files, I'm just showing the way I've used and know work.

1. Create the OS file:

$> dd if=/dev/zero of=/u02/oradata/ORCL/temp1_01.dbf bs=1024k count=1024

2. Create the TEMP tablespace and/or temp file:

SQL> CREATE TEMPORARY TABLESPACE temp1 

TEMPFILE '/u02/oradata/ORCL/temp1_01.dbf' REUSE;