Creating Temporary Tablespace

Creating Temporary Tablespace
From Oracle 9i, we can specify a default temporary tablespace when you create a
 database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE
statement.
e.g.
SQL> CREATE DATABASE oracular .....
DEFAULT TEMPORARY TABLESPACE temp_ts .....;

Oracle provides various ways of creating TEMPORARY tablespaces.
Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE ...;
Example:
SQL> CREATE TABLESPACE TEMPTBS DATAFILE '/path/temp.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT
MANAGEMENT DICTIONARY;

Oracle 7.3 & 8.0 - CREATE TABLESPACE temp DATAFILE ... TEMPORARY;
Example:
SQL> CREATE TABLESPACE TEMPTBS DATAFILE '/path/temp.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING DEFAULT NOCOMPRESS ONLINE
TEMPORARY EXTENT MANAGEMENT DICTIONARY;



Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;
Examples:
SQL> CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE '/path/temp.dbf' SIZE 1000M
AUTOEXTEND ON NEXT 8K MAXSIZE 1500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K;

SQL> CREATE TEMPORARY TABLESPACE TEMPTBS2 TEMPFILE '/path/temp2.dbf' SIZE 1000M
AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL BLOCKSIZE 2K;



The MAXSIZE clause will default to UNLIMITED, if no value is specified.
All extents of temporary tablespaces are the same size, so UNIFORM keyword is
optional - if UNIFORM is not defined it will default to 1 MB.


Example using OMF (Oracle Managed Files):
SQL> CREATE TEMPORARY TABLESPACE temp;

Restrictions:
(1) We cannot specify nonstandard block sizes for a temporary tablespace or if you
intend to assign this tablespace as the temporary tablespace for any users.
(2) We cannot specify FORCE LOGGING for an undo or temporary tablespace.
(3) We cannot specify AUTOALLOCATE for a temporary tablespace.

Tempfiles (Temporary Datafiles)
Unlike normal datafiles, tempfiles are not fully allocated. When you create a tempfiles,
Oracle only writes to the header and last block of the file. This is why it is much
quicker to create a tempfiles than to create a normal datafile.

Tempfiles are not recorded in the database's control file. This implies that just
recreate them whenever you restore the database, or after deleting them by accident.
You can have different tempfile configurations between primary and standby databases
in dataguard environment, or configure tempfiles to be local instead of shared in a
RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace.
However, one can remove a tempfile from a database. Look at this example:
SQL> alter database tempfile 'tempfile_name' drop including datafiles;
//If the file was created as tempfile

SQL> alter database datafile 'tempfile_name' drop;
//If the file was created as datafile

Dropping temp tablespace
SQL> drop tablespace temp_tbs;
SQL> drop tablespace temp_tbs including contents and datafiles;

If you remove all tempfiles from a temporary tablespace, you may encounter error:
ORA-25153: Temporary Tablespace is Empty.

Use the following statement to add a tempfile to a temporary tablespace:
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/path/temp01.dbf' SIZE 512m
AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Except for adding a tempfile, you cannot use the ALTER TABLESPACE statement for a
locally managed temporary tablespace (operations like rename, set to read only,
recover, etc. will fail).

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are
similar to ordinary datafiles except:
You cannot create a tempfile with the ALTER DATABASE statement.
You cannot rename a tempfile or set it to read-only.
Tempfiles are always set to NOLOGGING mode.
When you create or resize tempfiles, they are not always guaranteed allocation of
disk space for the file size specified. On certain file systems (like UNIX) disk
blocks are allocated not at file creation or resizing, but before the blocks are
accessed.
Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic
performance view V$TEMPFILE.
Note: This arrangement enables fast tempfile creation and resizing, however, the disk
could run out of space later when the tempfiles are accessed.

Default Temporary Tablespaces
From Oracle 9i, we can define a default temporary tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

By default, the default temporary tablespace is SYSTEM. Each database can be
assigned one and only one default temporary tablespace. Using this feature,
a temporary tablespace is automatically assigned to users.

The following restrictions apply to default temporary tablespaces:
-DEFAULT TEMPORARY TABLESPACE must be of type TEMPORARY.
-DEFAULT TEMPORARY TABLESPACE cannot be taken off-line.
-DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

To see the default temporary tablespace for a database, execute the following query:
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name
like '%TEMP%';

The DBA should assign a temporary tablespace to each user in the database to prevent
them from allocating sort space in the SYSTEM tablespace. This can be done with one
of the following commands:
SQL> CREATE USER scott TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

To change a user account to use a non-default temp tablespace
SQL> ALTER USER user1 SET TEMPORARY TABLESPACE temp_tbs;

Assigning temporary tablespace group as default temporary tablespace:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp;
Assigning temporary tablespace group to a user (same as assigning temporary tablespace
to a user):
SQL> ALTER USER scott TEMPORARY TABLESPACE temp_grp;

All new users that are not explicitly assigned a TEMPORARY TABLESPACE will get the
default temporary tablespace as its TEMPORARY TABLESPACE. Also, when you assign a
TEMPORARY tablespace to a user, Oracle will not change this value next time you change
the default temporary tablespace for the database.

Monitoring Temporary Tablespaces
Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use
V$TEMPFILE and DBA_TEMP_FILES instead.

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';
TABLESPACE_NAME FILE_NAME BYTES
----------------- -------------------------------- --------------
TEMP /../temp01.dbf 11,175,650,000

SQL> select file#, name, round(bytes/(1024*1024),2) "SIZE IN MB's" from v$tempfile;

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE.

DBA_FREE_SPACE does not record free space for temporary tablespaces.
Use DBA_TEMP_FREE_SPACE or V$TEMP_SPACE_HEADER instead.

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMPTBS 4214226944 80740352

From 11g, we can check free temp space in new view DBA_TEMP_FREE_SPACE.
SQL> select * from DBA_TEMP_FREE_SPACE;

Resizing tempfile
SQL> alter database tempfile temp-name resize integer K|M|G|T|P|E;
SQL> alter database tempfile '/path/temp01.dbf' resize 1000M;

Resizing temporary tablespace
SQL> alter tablespace temptbs resize 1000M;

Renaming (temporary) tablespace, this is from Oracle 10g
SQL> alter tablespace temp rename to temp2;


reclaim used space
Several methods existed to reclaim the space used for a larger than normal temporary
tablespace.
(1) Restarting the database, if possible.
(2) The method that exists for all releases of Oracle is, simply drop and recreate the
temporary tablespace back to its original (or another reasonable)size.
(3) If you are using Oracle9i or higher, drop the large tempfile (which will drop the
 tempfile from the data dictionary and the OS file system).



Views:
DBA_TEMP_FILES
DBA_DATA_FILES
DBA_TABLESPACES
DBA_TEMP_FREE_SPACE (Oracle 11g)
V$TEMPFILE
V$TEMP_SPACE_HEADER
V$TEMPORARY_LOBS
V$TEMPSTAT
V$TEMPSEG_USAGE