Database Reference
In-Depth Information
One of the nuances of true temp files is that if the OS permits it, the temporary files will be created sparse—that is,
they will not actually consume disk storage until they need to. You can see that easily in this example (on Oracle Linux):
EODA@ORA12CR1> !df -h /tmp
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 6.5G 41G 14% /
EODA@ORA12CR1> create temporary tablespace temp_huge
2 tempfile '/tmp/temp_huge.dbf' size 2g;
Tablespace created.
EODA@ORA12CR1> !df -h /tmp
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 6.5G 41G 14% /
EODA@ORA12CR1> !ls -l /tmp/temp_huge.dbf
-rw-rw----. 1 ora12cr1 ora12cr1 2147491840 Sep 3 13:28 /tmp/temp_huge.dbf
■
the UNiX/linux command
df
shows “disk free” space. this command showed that i have 41GB free in the file
system containing
/tmp
before i added a 2GB temp file to the database. after i added that file, i
still
had 41GB free in
the file system.
Note
Apparently it didn't take much storage to hold that file. If we look at the
ls
output, it appears to be a normal 2GB
file, but it is, in fact, consuming only a few kilobytes of storage currently. So we could actually create hundreds of these
2GB temporary files, even though we have roughly 41GB of disk space free. Sounds great—free storage for all! The
problem is, as we start to use these temp files and they start expanding out, we would rapidly hit errors stating “no
more space.” Since the space is allocated or physically assigned to the file as needed by the OS, we stand a definite
chance of running out of room (especially if after we create the temp files, someone else fills up the file system with
other stuff ).
How to solve this differs from OS to OS. On UNIX/Linux, you can use
dd
to fill the file with data, causing the OS to
physically assign disk storage to the file, or use
cp
to create a nonsparse file, for example:
EODA@ORA12CR1> !cp --sparse=never /tmp/temp_huge.dbf /tmp/temp_huge_not_sparse.dbf
EODA@ORA12CR1> !df -h /tmp
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
50G 8.5G 39G 19% /
EODA@ORA12CR1> drop tablespace temp_huge including contents and datafiles;
Tablespace dropped.
