關於Oracle10g中tempfile 空間分配的問題：temp file will not actually allocate disk space

CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'C:ORACLEORADATAORA10TEMP01.DBF' SIZE 2G ；


Temporary tablespaces are created using temp files instead of datafiles. Temp files are allocated slightly differently than datafiles. Although atafiles are completely allocated and initialized at creation time, temp files are not always guaranteed to allocate the disk space specified. [@more@]

This means that on some Unix systems a temp file will not actually allocate disk space until a sorting operation requires it. Although this delayed allocation approach allows rapid file creation, it can cause problems down the road if you ave not reserved the space that may be needed at runtime.




可以透過一個方法改變：


Workaround for Deferred Temp File Disk Space Allocations

A workaround for allocating temp file space at runtime is to preallocate it, just like you do with a datafile. In fact, you first allocate it as a datafile and then drop the tablespace, leaving the file. Finally, you create your temp tablespace reusing the old datafile as a temp file.


-- first create it as a permanent tablespace
-- to force the disk space to be allocated
CREATE TABLESPACE temp
DATAFILE '/ORADATA/PROD/TEMP01.DBF' SIZE 2G;

-- dropping the tablespace does not remove
-- the file from the file system
DROP TABLESPACE temp;

-- the keyword REUSE is needed to use the existing
-- file created in the previous steps
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/ORADATA/PROD/TEMP01.DBF' SIZE 2G REUSE

原來在建立Oracle10g資料庫的時候，temp表空間雖然分配了2G大小的一個檔案TEMP01.DBF , 但是沒有Sorting等動作之前，temp01.dbf 檔案是不佔用磁碟空間的 。只有當sorting, create index , hash join 等用到temp動作的時候才會真正開始佔用分配磁碟空間 。佔用磁碟空間之後不會釋放(當然裡面的資料會釋放) 。