Disk Space of Temporary Tablespace (Sparse files)

A temporary tablespace contains schema objects only for the duration of a session. Temporary data files (temp files) in Oracle are a special type of data file. Oracle will use temporary files to store the intermediate results of a large sort operation, hash operations, global temporary table data, or result set, when there is insufficient memory to hold it all in RAM. Temp files are always set to NOLOGGING mode, which means that they never have redo generated for them. Media recovery does not recognize temp files.


When you create or resize temp files, they are not always guaranteed allocation of disk space for the file size specified. On file systems such as Linux and UNIX, temp files are created as sparse files. In this case, disk blocks are allocated not at file creation or resizing, but as the blocks are accessed for the first time.

When you check the space on operating system level you see that commands like 'du' and 'df' do not show a significant change of allocated file. free space on the one hand. On the other hand a 'ls -l <path and file>' shows a filesize as specified in the 'create' or 'alter' command.

It is possible that a file created with 2G initially allocates only 1M on disk.

SQL> set timing on

SQL> alter tablespace TEMP01 add tempfile '/u01/app/oracle/oradata/ORCL1/tempfiles/temp02.dbf' size 2G;

Tablespace altered.

Elapsed: 00:00:00.52

SQL> select file_name,ceil(bytes / 1024 / 1024) "size MB" from   dba_temp_files where  tablespace_name = 'TEMP01';

FILE_NAME                                         size MB

------------------------------------------------------------ ------------

/u01/app/oracle/oradata/ORCL1/tempfiles/temp02.dbf              2,048

[oracle@oggOra1 tempfiles]$ ls -lth

total 1.1M

-rw-r----- 1 oracle oinstall 2.1G Jul 11 01:45 temp02.dbf

Sparse files have different apparent file sizes (the maximum size to which they may expand) and actual file sizes (how much space is allocated for data on disk). To check the file's apparent size, just run:

[oracle@oggOra1 tempfiles]$ du -h --apparent-size temp02.dbf

2.1G  temp02.dbf

And, to check the actual size of the file on disk:

[oracle@oggOra1 tempfiles]$ du -h temp02.dbf

1.1M  temp02.dbf

As you can see, although the apparent size of the file is 2G , its "actual" size is really 1M—that's because due to the nature and beauty of sparse files, it will "expand" arbitrarily to minimize the space required to store its contents.

Sparse file

In computer science, a sparse file is a type of computer file that attempts to use file system space more efficiently when blocks allocated to the file are mostly empty. This is achieved by writing brief information (metadata) representing the empty blocks to disk instead of the actual "empty" space which makes up the block, using less disk space. The full block size is written to disk as the actual size only when the block contains "real" (non-empty) data.

When reading sparse files, the file system transparently converts metadata representing empty blocks into "real" blocks filled with zero bytes at runtime. The application is unaware of this conversion.

Advantages

The advantage of sparse files is that storage is only allocated when actually needed: disk space is saved, and large files can be created even if there is insufficient free space on the file system.

Disadvantages

Disadvantages are that sparse files may become fragmented; file system free space reports may be misleading; filling up file systems containing sparse files can have unexpected effects; and copying a sparse file with a program that does not explicitly support them may copy the entire, uncompressed size of the file, including the sparse, mostly zero sections which are not on disk -- losing the benefits of the sparse property in the file. 

Caution: Sparse files enable fast temp file creation and resizing; however, the disk could run out of space later when the temp files are accessed.

Usage of sparse files is not a bug. Therefore there is no possibility to tell Oracle not to use sparse files for the temporary tablespace if the operating system offers sparse file functionality.

If you want to avoid that files do allocate less space on disk than they are defined:

After copying the sparse 2G file to newtemp02.dbf and creating the temporary tablespace using that tempfile with the REUSE option.  Assured that tempfile has allocated all of its file system space and our database actually has 2G of temporary space to work with.

SQL> Drop tablespace TEMP01;

Tablespace dropped.

[oracle@oggOra1 tempfiles]$ cp --sparse=never temp02.dbf newtemp02.dbf

[oracle@oggOra1 tempfiles]$ ls -ltrh

total 2.1G

-rw-r----- 1 oracle oinstall 2.1G Jul 11 01:45 temp02.dbf

-rw-r----- 1 oracle oinstall 2.1G Jul 11 02:18 newtemp02.dbf

[oracle@oggOra1 tempfiles]$ du -h temp0*

2.1G  newtemp02.dbf

1.1M  temp02.dbf

SQL> Create temporary tablespace TEMP01 tempfile '/u01/app/oracle/oradata/ORCL1/tempfiles/newtemp02.dbf' reuse;

Thanks :)

Ahmed Fathi