3.2. Creating a New Temporary Tablespace
A temporary tablespace is responsible for various database sorting operations. A temporary tablespace is part of the physical database files, which the Oracle control file will expect to exist under normal operations. Because the temporary tablespace doesn't have any permanent objects stored within it, there is no change in the system change number (SCN) from the checkpoint process in the control file or file header. The database will continue to function normally, with the exception of creating temporary segments, which occurs when creating indexes or performing certain select statements. Because the temporary tablespace only has temporary data stored inside, this tablespace can be re-created and reassigned in the event of datafile loss.
For example, in a media failure such as a disk failure or controller failure, the temporary datafile could become unavailable. This would cause sorting operations such as creating indexes or select statements to fail. The remedy for this situation is to drop the tablespace—including its contents if they exist—which will remove the datafile and tablespace reference from the data dictionary. Then you simply create the temporary tablespace.
Let's see an example of creating a new temporary tablespace with the database up and running using a tempfile or locally managed extents:
SQL> create temporary tablespace temp2 tempfile
'C:oracleoradataora101t emp2_01.dbf' size 100m
extent management local uniform size 128k;NOTE
You should use the TEMPFILE keyword and create the temporary tablespace as TEMPORARY TABLESPACE so that no permanent objects can be stored in the tablespace.
As you can see, being able to create a new temporary tablespace is an essential non-critical recovery task. Because there are no permanent objects in the temporary tablespace, you can easily re-create a new temporary tablespace. In the next sections, we will work with additional temporary tablespace issues. Specifically, you will learn how to deal with missing tempfiles and how to reassign the temporary tablespace.
3.2.1. Starting the Database with a Missing Tempfile
Starting a database with a missing tempfile is another non-critical recovery technique that you need to understand. A tempfile is a type of tablespace where management occurs locally or in the tablespace, as opposed to in the data dictionary. These types of tablespaces were first introduced in Oracle 8i and have significant improvements for sorting and space management. The temp-file can be used with all tablespaces as of Oracle9i. They are most commonly used with temporary tablespaces and are the default for the Database Configuration Assistant (DBCA) temporary tablespace creation.
The steps to start a database with a missing tempfile are fairly straightforward:
Start and mount the database if it is not already running:
C:Documents and Settingsdstuns>sqlplus /nolog SQL*Plus: Release 10.1.0.2.0 - Production on Sat Aug 21 19:08:48 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> startup mount ORACLE instance started. Total System Global Area 88080384 bytes Fixed Size 787588 bytes Variable Size 78642044 bytes Database Buffers 8388608 bytes Redo Buffers 262144 bytes Database mounted. SQL>
Next, drop the tablespace:
SQL> drop tablespace temp including contents;
Finally, re-create the temporary tablespace using the following command. In the Windows environment, use C:directory to specify the file; in the Unix environment, use /directory to specify the file.
SQL> create temporary tablespace temp tempfile
'C:oracleoradataora101t emp01.dbf' size 100m
extent management local uniform size 128k; Tablespace created.
3.2.2. Altering the Default Temporary Tablespace for a Database
Another method for remedying the loss of a temporary tablespace is to modify the existing temporary tablespace to a new or different temporary tablespace. This can be accomplished by using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE command. This is a fairly straightforward process. First, there must be a temporary tablespace existing in the database. If not, you will need to create a temporary tablespace to switch as the default tablespace.
Let's walk through an example:
First, create a temporary tablespace if one doesn't exist:
SQL> create temporary tablespace temp2 tempfile
'C:oracleoradataora101t emp2_01.dbf' size 100m
extent management local uniform size 128k; Tablespace created.
Next, alter the default temporary tablespace to a different temporary tablespace using the following command:
SQL> alter database default temporary tablespace temp2; Database altered.
NOTE
You must have another temporary tablespace available. If you attempt to switch the default tablespace to a permanent tablespace, you will get an ORA-12904 default temporary tablespace cannot be altered to PERMENANT type error.