FS
Documentation

Oracle TEMPORARY Tablespaces and TEMPFILES¹

This page was last modified 00:22, 19 February 2008.

From Documentation

(Difference between revisions)
Jump to: navigation, search
Revision as of 02:18, 6 August 2007
Moff (Talk | contribs)
(What are Temporary Tablespaces?)
← Previous diff
Current revision
Moff (Talk | contribs)
(Technical Note)
Line 1: Line 1:
 +{| align="right"
 +| __TOC__
 +|}
== Technical Note == == Technical Note ==
Please note that this article recreates Technical Note 213, originally posted May 30, 2005. Please note that this article recreates Technical Note 213, originally posted May 30, 2005.
 +
 +<br>
== What are Temporary Tablespaces? == == What are Temporary Tablespaces? ==
Line 9: Line 14:
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: 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;+:{{code|SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
- SQL> ALTER USER scott TEMPORARY TABLESPACE temp;+SQL> ALTER USER scott TEMPORARY TABLESPACE temp;}}
 +:{{note|A temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.}}
-{{note|A temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.}}+:{{note|Temporary tablespaces are excluded from RMAN backups. This reqires them to be re-added at recovery time.}}
- +
- +
- +
- +
-----+
-;Note: Temporary tablespaces are excluded from RMAN backups. This reqires them to be re-added at recovery time.+
-----+
<br> <br>
Line 32: Line 31:
You cannot remove datafiles from a tablespace until you drop the entire tablespace. However, you can remove a TEMPFILE from a database. Look at this example: You cannot remove datafiles from a tablespace until you drop the entire tablespace. However, you can remove a TEMPFILE from a database. Look at this example:
- SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;+:{{code|SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;}}
If you remove all tempfiles from a temporary tablespace, you may encounter error: <tt>''ORA-25153: Temporary Tablespace is Empty''</tt>. Use the following statement to add a TEMPFILE to a temporary tablespace: If you remove all tempfiles from a temporary tablespace, you may encounter error: <tt>''ORA-25153: Temporary Tablespace is Empty''</tt>. Use the following statement to add a TEMPFILE to a temporary tablespace:
- SQL> ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;+:{{code|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). 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).
<br> <br>
 +
== How do I create Temporary Tablespaces? == == How do I create Temporary Tablespaces? ==
Line 50: Line 50:
Oracle 8i and 9i example: Oracle 8i and 9i example:
- SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;+:{{code|SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;}}
For best performance, the <tt>UNIFORM SIZE</tt> must be a multiple of the SORT_AREA_SIZE parameter. For best performance, the <tt>UNIFORM SIZE</tt> must be a multiple of the SORT_AREA_SIZE parameter.
Line 56: Line 56:
Oracle 9i example using OMF (Oracle Managed Files): Oracle 9i example using OMF (Oracle Managed Files):
- SQL> CREATE TEMPORARY TABLESPACE temp;+:{{code|SQL> CREATE TEMPORARY TABLESPACE temp;}}
<br> <br>
 +
== Default Temporary Tablespaces == == Default Temporary Tablespaces ==
In Oracle 9i and above, you can define a Default Temporary Tablespace at database creation time, or by issuing an <tt>ALTER DATABASE</tt> statement: In Oracle 9i and above, you can define a Default Temporary Tablespace at database creation time, or by issuing an <tt>ALTER DATABASE</tt> statement:
- SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;+:{{code|SQL> 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 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:
Line 72: Line 73:
To see the default temporary tablespace for a database, execute the following query: To see the default temporary tablespace for a database, execute the following query:
- SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';+:{{code|1=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. 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.
<br> <br>
 +
== Other Considerations == == Other Considerations ==
Line 86: Line 88:
<br> <br>
-== How do I find out the parameters needed for re-adding TEMPFILE's? ==+== How do I re-add TEMPFILE's? ==
- SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;+{{code|SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;}}
This will create a "create controlfile" script in the udump directory. You will see at the end of the script instructions to recreate your TEMPFILE's. On recovery the controlfile still has the entry for the tablespace, but you must recreate the tempfile. This will create a "create controlfile" script in the udump directory. You will see at the end of the script instructions to recreate your TEMPFILE's. On recovery the controlfile still has the entry for the tablespace, but you must recreate the tempfile.

Current revision

Contents

Technical Note

Please note that this article recreates Technical Note 213, originally posted May 30, 2005.


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:

code SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;

SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

Note
Note
A temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.
Note
Note
Temporary tablespaces are excluded from RMAN backups. This reqires them to be re-added at recovery time.


What are TEMPFILES?

Unlike normal data files, TEMPFILE's 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.

TEMPFILE's 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 TEMPFILE's to be local instead of shared in a RAC environment.

You cannot remove datafiles from a tablespace until you drop the entire tablespace. However, you can remove a TEMPFILE from a database. Look at this example:

code 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:

code 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 do I create Temporary Tablespaces?

Oracle provides various ways of creating TEMPORARY tablespaces (mainly to provide backward compatibility). You should use the most recent method available:

Oracle 8i and 9i example:

code 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):

code SQL> CREATE TEMPORARY TABLESPACE temp;


Default Temporary Tablespaces

In Oracle 9i and above, you can define a Default Temporary Tablespace at database creation time, or by issuing an ALTER DATABASE statement:

code SQL> 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:

To see the default temporary tablespace for a database, execute the following query:

code 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:


How do I re-add TEMPFILE's?


code SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This will create a "create controlfile" script in the udump directory. You will see at the end of the script instructions to recreate your TEMPFILE's. On recovery the controlfile still has the entry for the tablespace, but you must recreate the tempfile.



¹ This document is based on an article from The Oracle FAQ