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 (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. 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.
The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.
Note that a temporary tablespace cannot contain permanent objects and therefore doesn’t need to be backed up.
Unlike normal data files, 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.
TEMPFILEs are not recorded in the database’s control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, 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 his example:
SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING 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 '/oradata/temp03.dbf' SIZE 100M;
Except for adding a tempfile, as illustrated in the above example, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace (operations like rename, set to read only, recover, etc. will fail).
SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.
To see the default temporary tablespace for a database, execute the following query: SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead. 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 V$TEMP_SPACE_HEADER instead:
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER; TABLESPACE_NAME BYTES_USED BYTES_FREE ------------------------------ ---------- ---------- TEMP 52428800 52428800
Oracle 9i does not release allocated TEMP segments until you shutdown the database. While the TEMP segment is allocated, it does not mean that it is unavailable for use. When a user requests a disk sort, Oracle will allocate a TEMP segment. Once that sort is done, Oracle releases this TEMP segment for future use, but does not deallocate it. When the next user requests a disk sort, Oracle does not have to allocate a new TEMP segment. It uses the same one that no user is currently using. Oracle manages this for you in 9i. And there is really only one TEMP segment in the TEMP tablespace. Multiple users can utilitize this one segment.
There have been problems in 9i where users running a TEMPORARY TEMP tablespace with TEMPFILES that is Locally Managed where Oracle does not release the sort space once it is no longer being used. This is a known bug. The workaround has been to revert back to Dictionary Managed tablespace for TEMP.
Tekst pochodzi ze strony: http://www.orafaq.com/node/2