Pre allocate space for Oracle temp files

Temporary tablespaces use sparse files  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.

Let us say, the file system size is 100G and day shift DBA added 30G temp file on the file system.  But we all know that, temp file will create with very small size initially and  it will grow only if oracle started using the temp segment. 

Again, night shift DBA joined the duty and he saw that, the file system free space shows 99G free space. He added 3 data files each 30G size and he filled up 90G on the file system. A few hours later, oracle started using the temp segment and gradually growing the temp space and exceeding the file system size which puts Database under risk. We are cheated to believe that there is more than enough free disk space.

It is always best practice to use dedicated file system for temp files.  Especially, Data Guard, it is highly important to use dedicated file system. 

How do we resolve this issue?  How do we create non sparse temp files at the beginning of creating temp file?

Option 1

Step 1 Create the sparse temp file, say temp01.dbf

alter tablespace temp add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf' ;

Step 2 Execute the Cartesian join query as below to fill up the temp file

select * from dba_source a, dba_source b, dba_source c, dba_source d order by 1,2,3,4

Wait for ORA-01652:unable to extend temp segment by 128 in tablespace TEMP

Step 3 Copy the non-parsed temp file to another file called temp02.dbf

cp temp01.dbf temp02.dbf

Step 4 create the another temp file

Alter tablespace temp add tempfile ‘temp02.dbf’ reuse

Now we have no-parse temp files for both temp01.dbf and temp02.dbf


Option 2

Step 1 Create the sparse temp file, say temp01.dbf
alter tablespace temp add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf' ;

Step 2 copy the non-parsed temp file to another file called temp02.dbf
cp --sparse=never temp01.dbf temp02.dbf

Step 3 create the another temp file
Alter tablespace temp add tempfile ‘temp02.dbf’ reuse

Step 4 Drop the parsed temp file temp01.dbf
alter database tempfile ‘temp01.dbf’ drop including datafiles;


Repeat the step for adding another non-parsed temp files

Option 3

Create a normal datafile (via a transitory tablespace) and then add it to the temporary tablespace using the REUSE clause as follows:

create tablespace test_tbs  datafile '/oratemp/oradata/chfsdb20/temp01.dbf'  size 20480m;


Drop the tablespace without removing data files from file system

drop tablespace test_tbs;

alter tablespace TEMP add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf'   reuse;