Managing Temporary Tablespace

First we will discus about use of temporary tablespace. We use it to manage space for database sort operation. For example: if we join two large tables it require space for sort operation because oracle cannot do shorting in memory. This sort operation will be done in temperory tablespace.

We must assign a temporary tablespace to each user in the database; if we don’t assign temperory tablespace to user in the database oracle allocate sort space in the SYSTEM tablespace by default.

Important:

• That a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.
• When we 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.
• We cannot remove datafiles from a tablespace until you drop the entire tablespace but we can remove a TEMPFILE from a database:

SQL> ALTER DATABASE TEMPFILE ''<disk>:\<directory>\<Tablespace Name>.dbf' DROP INCLUDING DATAFILES;

• 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).

How does create Temporary Tablespaces?

CREATE TEMPORARY TABLESPACE temp

TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf' size 20M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.

How can define Default Temporary Tablespaces?

We can define a Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Important:

• The default “Default Temporary Tablespace” is SYSTEM.
• Each database can be assigned one and only one Default Temporary Tablespace.
• Temporary Tablespace is automatically assigned to users.

Restriction:

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.

How to see the default temporary tablespace for a database?

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

How to Monitoring Temporary Tablespaces and Sorting?

Use following query to view temp file information:

Select * from dba_temp_files; or Select * from v$tempfile;

Use following query for monitor temporary segment

Select * from v$sort_segments or Select * from v$sort_usage

Use following query for free space in tablespace :

select TABLESPACE_NAME,BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

How to Dropping / Recreating Temporary Tablespace? (Method)

This should be performed during off ours with no user logged on performing work.

If you are working with a temporary tablespace that is NOT the default temporary tablespace for the database, this process is very simple. Simply drop and recreate the temporary tablespace:

Step:1 Drop the Tablespace

DROP TABLESPACE temp;

Tablespace dropped.

Step: 2 Create new temporary tablespace.

CREATE TEMPORARY TABLESPACE TEMP

TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf' SIZE 500M REUSE

AUTOEXTEND ON NEXT 100M MAXSIZE unlimited

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

How to Dropping / Recreating Default Temporary Tablespace? (Method)

You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:

DROP TABLESPACE temp;

drop tablespace temp

*

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

Step: 1 Create another temperory tablespace.

CREATE TEMPORARY TABLESPACE temp2

TEMPFILE '<disk>:\<directory>\<Tablespace Name>.dbf'SIZE 5M REUSE

AUTOEXTEND ON NEXT 1M MAXSIZE unlimited

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

Step: 2 Make default tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

Step: 3 Drop old defaule tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Most Importent:

You do not need to assign temporary tablespace while creating a database user. The Temporary Tablespace is automatically assigned. The name of the temporary tablespace is determined by the DEFAULT_TEMP_TABLESPACE column from the data dictionary view DATABASE_PROPERTIES_VIEW.

Example:

Step:1 Create database user

create user test identified by test default TABLESPACE users;

User created.

Step: 2 View information

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM

DBA_USERS WHERE USERNAME='TEST';

USERNAME     DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

--------              ------------------------------    ------------------------------

TEST USERS TEMP

NOTE: Temporary Tablespace TEMP is automatically assigned to the user TEST.

Certain Restrictions?

• The default temporary tablespace can not be DROP.

• The default temporary tablespace cab not be taken offline