Temporary Tablespace in Oracle 10g

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

This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s