What are
Temporary Tablespaces:
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. This can be done with one of the following commands:
SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;
Note that a temporary tablespace cannot contain permanent objects and
therefore doesn't need to be backed up.
What are TEMPFILES?
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).
How does one create Temporary
Tablespaces?
Oracle provides various ways of creating TEMPORARY tablespaces
(mainly to provide backward compatibility). One should use the most
recent method available:
- Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE ...;
- Oracle 7.3 & 8.0 - CREATE TABLESPACE temp DATAFILE ... TEMPORARY;
- Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;
Oracle 8i and 9i example:
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.
Oracle 9i example using OMF (Oracle Managed Files):
SQL> CREATE TEMPORARY TABLESPACE temp;
Default Temporary Tablespaces:
In Oracle 9i and above, one can define a Default Temporary Tablespace
at database creation time, or by issuing an "ALTER DATABASE" statement:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE
temp;
The default 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:
- The Default Temporary Tablespace must be of type TEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The 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 * FROM DATABASE_PROPERTIES where
PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
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.
Other Considerations:
Some performance considerations for temporary tablespaces:
- Always use temporary tablespaces instead of permanent content
tablespaces for sorting (no logging and uses one large sort segment to
reduce recursive SQL and ST space management enqueue contention).
- Ensure that you create your temporary tablespaces as locally managed
instead of dictionary managed (Use sort space bitmap instead of sys.fet$
and sys.uet$ for allocating space).
- Always use TEMPFILEs instead of DATAFILEs (reduce backup and recovery
time + other advantages as described above)
- Stripe your temporary tablespaces over multiple disks to alleviate
possible disk contention and to speed-up sorting operations (user
processes can read/write to it directly).
Monitoring Temporary Tablespaces and
Sorting:
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