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 ;
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 ;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-84982/，如需转载，请注明出处，否则将追究法律责任。