Temporary Tablespace in Oracle
Oracle introduced temporary tablespaces in Oracle 7.3
Temporary tablespaces are used to manage space for database sort and joining operations and for storing global temporary tables. For joining two large tables or sorting a bigger result set, Oracle cannot do in memory by using SORT_AREA_SIZE in PGA (Programmable Global Area). Space will be allocated in a temporary tablespace for doing these types of operations. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.
Note that a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up. A temporary tablespace contains schema objects only for the duration of a session.
Creating Temporary Tablespace in Oracle
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, temporary files 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 temporary 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;
SQL> ALTER TABLESPACE temp_ts ADD TEMPFILE '+DATA' SIZE 512m AUTOEXTEND ON NEXT 500m MAXSIZE UNLIMITED;
alter tablespace temptbs rename to temptbs2;
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).
To resize temporary files (in Oracle database):
alter database tempfile temp_file_name resize nK|M|G|T|P|E;
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:
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;
