Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation.
Each database should have one temporary tablespace that is created when the database is created. A temporary tablespace uses temporary files (also called tempfiles) rather than regular datafiles.
TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.
An example to create Temporary Tablespace:
CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/ora01/oracle/oradata/booktst_temp_01.dbf’ SIZE 50m;
DROP TEMPORARY TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
To view Temporary Tablespace details (including TEMFILES):
select * from v$tempfile