Skip to Main Content

Temporary files as optional spare files

sysassysdbaMay 11 2015 — edited Jan 11 2016

SPARSE FILES on unix databases

when you create a tempfile in Oracle that run in unix, the tempfile is created as "Sparse File" ie.

tempfile doesn´t fill all space that it seem to have.

When you resize a tempfile from database, it does not grow in operating system  until you fill file space in database

SQL> alter database tempfile '/u8515/oradata/PRODBD/temp01.dbf' resize 3G;

In s.o:

bash # du -h /u8515/oradata/PRODBD/temp01.dbf;ls -lh /u8515/oradata/PRODBD/temp01.dbf| awk '{print " " $5"   "$9}'

2.0G   /u8515/oradata/PRODBD/temp01.dbf

3.0G   /u8515/oradata/PRODBD/temp01.dbf

Sparse files are good for random access but when you run out of room in your device

you had to take into account that df -h doesn´t show real free space...a lot of times

i have expanded a datafile  filling up the file system and causing problems when tempfile  needs his real database space.

would be possible that  create tempfiles as sparse files, it be  an optional parameter when you created a tempfile in Oracle Rdbms?

Post Details
Added on May 11 2015