¨ Creating Temporary Tablespaces

Temporary segments are most commonly generated during sorting operations such as ORDER BY, GROUP BY, and CREATE INDEX. They are also generated during other

operations such as hash joins or inserts into temporary tables.

CREATE TEMPORARY TABLESPACE temp TEMPFILE

‘C:ORACLEORADATAORA10TEMP01.DBF' SIZE 2G ;

Temp files are only available with temporary tablespaces, never need to be backed up, and do not log data changes in the redo logs.

Note: tempfile information is stored in v$tempfile , not in v$datafile ;

¨ Temp files are not always guaranteed to allocate the disk space specified. 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 have 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 檔案Disk Space Allocation 問題

相關文章