Temporary Tablespace

Temporary Tablespace

Each database should have one temporary tablespace that is created when the database is created. Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for hash joins in SQL. For SQL with millions of rows returned, the sort operation is too large for the RAM area and must occur on disk.

The only other difference is that a temporary tablespace uses temporary files (also called tempfiles) rather than regular datafiles. Thus, instead of using the datafiles keyword you use the tempfiles keyword when issuing a create, drop or alter tablespace command.

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/u03/oravis/VIS/data/temp01.dbf

/u03/oravis/VIS/data/temp02.dbf

/u03/oravis/VIS/data/temp0001.dbf

SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME                CONTENTS

------------------------------ ---------

SYSTEM                         PERMANENT

SYSAUX                         PERMANENT

TEMP                             TEMPORARY

XXTRAINEE                    PERMANENT

ASSOCIATES                  PERMANENT

UNDO                            UNDO

27 rows selected.

SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME

--------------------------------------------------------------------------------

PROPERTY_VALUE

--------------------------------------------------------------------------------

DEFAULT_TEMP_TABLESPACE

TEMP

Create Temp TS:

SQL> create temporary tablespace temp2 tempfile '/u03/oravis/VIS/data/temp1.dbf' size 50m;

Tablespace created.

Default Temp TS:

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME

--------------------------------------------------------------------------------

PROPERTY_VALUE

--------------------------------------------------------------------------------

DEFAULT_TEMP_TABLESPACE

TEMP2

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/u03/oravis/VIS/data/temp01.dbf

/u03/oravis/VIS/data/temp02.dbf

/u03/oravis/VIS/data/temp0001.dbf

/u03/oravis/VIS/data/temp1.dbf

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME

--------------------------------------------------------------------------------

PROPERTY_VALUE

--------------------------------------------------------------------------------

DEFAULT_TEMP_TABLESPACE

TEMP

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME

------------------------------

/u03/oravis/VIS/data/temp01.dbf

TEMP

/u03/oravis/VIS/data/temp02.dbf

TEMP

/u03/oravis/VIS/data/temp0001.dbf

TEMP

FILE_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME

------------------------------

/u03/oravis/VIS/data/temp1.dbf

TEMP2

drop tablespace temp TS
SQL> drop tablespace temp including contents and datafiles;

SQL> drop tablespace temp;

drop datafile from temp TS

SQL> alter database tempfile '/u03/oravis/VIS/data/temp0001.dbf' drop including contents and datafiles;