Thursday, August 5, 2010

Oracle tempfile on linux is using sparse file by default

With 12GB free space, and if without sparse file, it's impossible to create a 20GB tempfile.

SQL> ! ls -lh /u01/app/oracle/oradata/orcl/temp01.dbf
-rw-r----- 1 oracle oinstall 201M Aug 5 13:54 /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> ! df -h /u01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
26G 12G 12G 50% /

SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 20G;

Database altered.

Elapsed: 00:00:00.02
SQL> ! ls -lh /u01/app/oracle/oradata/orcl/temp01.dbf
-rw-r----- 1 oracle oinstall 21G Aug 5 13:56 /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> ! df -h /u01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
26G 12G 12G 50% /


It's possible to pre-allocate space to tempfile by copying current tempfile to a new file with "--sparse=never" option.

SQL> ! cp --sparse=never /u01/app/oracle/oradata/orcl/temp01.dbf /u01/app/oracle/oradata/orcl/temp01_nosparse.dbf