We talked about the effect of Oracle filesystem-based tempfiles. I did a test on dbtest1. Before I created the tempfile: /oradata/oradata001/catdb816>df -k . Filesystem kbytes used avail capacity Mounted on /dev/md/dsk/d3 13393961 7992424 5267598 61% / You would think I was not able to create a file of size 6GB. Watch: SQL> create temporary tablespace temptest tempfile '/oradata/oradata001/catdb816/temptest_001.dbf' size 6000m; Tablespace created. Then on UNIX, ls -l shows: -rw-rw---- 1 oracle dba 6291464192 Jan 14 09:33 temptest_001.dbf but df -k for that filesystem still shows 61% used. This is because the newly created Oracle tempfile is a sparse file, which doesn't have any disk blocks allocated except for its header, and ls -l is cheated because the file inode has the false filesize information in there. The sparse files are created very quickly. But once it's being used, disk blocks are gradually allocated when it's used and you may run out of space. df -k is not fooled and can tell us how much space is actually used. Other than using the file by writing data to it, you can also change a sparse file to a space-allocated file with filesystems utilities such as tar and untar, cp and cp back and so on. But not all these utilities are sparse-file blind; gnu utilities may not be. If they're sparse files aware, this approach won't work. *********** On raw disk partitions **************** On raw partitions, Oracle DOES detect how much available space there is, just like when you create a non-sparse file. Here's a test on the toy database on Linux: SQL> alter database tempfile '/dev/RAC/RACtemp03' resize 4403197k; alter database tempfile '/dev/RAC/RACtemp03' resize 4403197k * ERROR at line 1: ORA-01237: cannot extend datafile 201 ORA-01110: data file 201: '/dev/RAC/RACtemp03' SQL> alter database tempfile '/dev/RAC/RACtemp03' resize 4403196k; Database altered. SQL> show parameter db_block_size NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ db_block_size integer 4096 According to /proc/partitions and raw binding info, the block device that /dev/RAC/RACtemp03 is bound to is 4403200 kb in size. Incidentally, creating a very big tempfile on a raw partition is also very fast. Yong *********** Sparse files on Windows **************** NTFS supports sparse files. If you use Windows XP or 2003, you have the fsutil utility to verify easily: C:\>fsutil fsinfo volumeinfo c:\ ... Supports Sparse files ... (If you use other Windows, find an XP and map the drive remotely to a drive like Z: and type this command on XP: fsutil fsinfo volumeinfo Z:\) However, fsutil doesn't detect Oracle created sparse files. First, let's create a sparse file in Oracle, SQL> create temporary tablespace tmp tempfile 'C:\Oracle\oradata\orcl10g\tmp01.dbf' size 1000m; Tablespace created. That took me about 1 second. od (from unxutils.sourceforge.net e.g.) shows 110 lines only; it took a while to finish running od, though): C:\Oracle\oradata\orcl10g>od -c tmp01.dbf | wc -l 110 But file properties (right click the file | Properties) shows 0.97 GB (1,048,580,096 bytes) for both Size and Size on disk. And C:\Oracle\oradata\orcl10g>fsutil sparse queryflag tmp01.dbf This file is NOT set as sparse C:\Oracle\oradata\orcl10g>fsutil sparse queryrange tmp01.dbf The specified file is NOT sparse Windows sparse files probably have only part of those properties UNIX sparse files have. For instance, you can't create a sparse file larger than the free space of the filesystem you create the file in, while UNIX silently allows you to (although when the file is used you may eventually get in trouble). Here's a test using Oracle 9.2.0.1.0 on XP [Version 5.1.2600]: SQL> sho parameter db_block_size NAME TYPE VALUE ------------------------------------ ----------- ----- db_block_size integer 4096 SQL> host fsutil volume diskfree c: Total # of free bytes : 13349572608 <-- 13036692KB Total # of bytes : 39999987712 Total # of avail free bytes : 13349572608 SQL> create temporary tablespace tmp tempfile 'c:\tmp.dbf' size 13000000k; Tablespace created. SQL> alter database tempfile 'c:\tmp.dbf' resize 13036684k; <-- 8k or 2 blocks less than free disk space Database altered. SQL> alter database tempfile 'c:\tmp.dbf' resize 13036685k; <-- 7k less than free space alter database tempfile 'c:\tmp.dbf' resize 13036685k * ERROR at line 1: ORA-01237: cannot extend datafile 202 ORA-01110: data file 202: 'C:\TMP.DBF' ORA-27059: skgfrsz: could not reduce file size <-- Funny, it always treats "resize" as "reduce". OSD-04005: SetFilePointer() failure, unable to read from file O/S-Error: (OS 112) There is not enough space on the disk. SQL> host fsutil volume diskfree c: Total # of free bytes : 0 Total # of bytes : 39999987712 Total # of avail free bytes : 0 Creating the TMP tablespace took about 2 seconds. After dropping it including the file, fsutil says free space is back to 13349572608. If you want, you can make use of the fact that tempfiles can't be larger than free space to create a checkOSfreespace program inside Oracle running on Windows. See http://groups.google.com/groups?selm=b3cb12d6.0207241428.67e62ee7%40posting.google.com But it only works on Windows for the reason I said above. Windows sparse files are discussed at http://www.microsoft.com/resources/documentation/Windows/XP/all/reskit/en-us/Default.asp?url=/resources/documentation/Windows/XP/all/reskit/en-us/prkc_fil_aixf.asp According to that document, Windows sparse files should have a very small size compared to size on disk. Oracle database tempfiles have equal sizes and sizes on disk.