Temporary tablespaces contain data that persists only for the duration of a user’s session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users. A temporary tablespace does exist on a permanent basis as do other tablespaces, such as the System and Sysaux tablespaces. However, the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users. The performance of temporary tablespaces is extremely critical when your application uses sort- and hash-intensive queries, which need to store transient data in the temporary tablespace. After starting up an instance, the first statement that uses the temporary tablespace creates a sort segment, which is shared by all sort operations in the instance. When you shut down the database, the database releases this sort segment. You can query the V$SORT_SEGMENT view to review the allocation and deallocation of space to this sort segment. You can see who is currently using the sort segment by querying the V$SORT_USAGE view. Use the V$TEMPFILE and DBA_TEMP_FILES views to find out details about the tempfiles currently allocated to a temporary tablespace. <h2>Creating a Temporary Tablespace</h2> You create a temporary tablespace the same way as you do a permanent tablespace, with the difference being that you specify the TEMPORARY clause in the CREATE TABLESPACE statement and substitute the TEMPFILE clause for the DATAFILE clause. Here’s an example: Example 1: <div class="codeme"><pre>SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'temp01.dbf' SIZE 50M AUTOEXTEND ON; Tablespace created.</pre></div> Example 2: <div class="codeme"><pre>SQL> CREATE TEMPORARY TABLESPACE temp3 TEMPFILE 'temp02.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; Tablespace created.</pre></div> In example 1 the SIZE clause in the second line specifies the size of the datafile and hence the size of the temporary tablespace, as 50MB. In the statement, the AUTOEXTEND ON clause will automatically extend the size of the temporary file, and thus the size of the temporary tablespace. By default, all temporary tablespaces are created with uniformly sized extents, with each extent sized at 1MB. In the example 2, the EXTENT MANAGEMENT clause is optional. The UNIFORM SIZE clause specifies a custom extent size of 16MB instead of the default extent size of 1MB. <div class="content-box-blue"><strong>NOTE</strong>: You use the TEMPFILE clause, not the DATAFILE clause, when you allocate space to a temporary tablespace. Oracle recommends that you use a locally managed temporary tablespace with a 1MB uniform extent size as your default temporary tablespace.</div> It is common to create a single temporary tablespace (usually named Temp) for each database, but you can have multiple temporary tablespaces, which are part of a temporary tablespace group, if your database needs them to support heavy sorting operations. In order to drop a default temporary tablespace, you must first use the ALTER TABLESPACE command to create a new default tablespace for the database. You can then drop the previous default temporary tablespace like any other tablespace. <h2>Altering a Temporary Tablespace</h2> You can issue the ALTER TEMPORARY TABLESPACE statement to perform various temporary tablespace management tasks, including adding a tempfile to grow a temporary tablespace. Below is an example showing how you can make the temporary tablespace larger: <div class="codeme"><pre>SQL> ALTER TABLESPACE temp ADD TEMPFILE '<PATH>/tempo3.dbf' size 1000M reuse;</pre></div> You can similarly use the following ALTER TABLESPACE command to resize a tempfile: <div class="codeme"><pre>SQL> ALTER DATABASE TEMPFILE '<PATH>/temp03.dbf RESIZE 200M;</pre></div> And you can use the following statement to drop a tempfile and remove the operating system file: <div class="codeme"><pre>SQL> ALTER DATABASE TEMPFILE '<PATH>/temp03.dbf' DROP INCLUDING DATAFILES;</pre></div> When you drop a tempfile belonging to a temporary tablespace, the tablespace itself will remain in use. <h2>Shrinking Temporary Tablespaces</h2> You may have to increase the size of a temporary tablespace to accommodate an unusually large job that makes use of the temporary tablespace. After the completion of the job, you can shrink the temporary tablespace using the SHRINK SPACE clause in an ALTER TABLESPACE statement. Here is an example: <div class="codeme"><pre>SQL> ALTER TABLESPACE temp SHRINK SPACE; Tablespace altered.</pre></div> The <strong>SHRINK SPACE</strong> clause will shrink all tempfiles to a minimum size, which is about 1MB. You can employ the KEEP clause to specify a minimum size for the tempfiles, as shown here: <div class="codeme"><pre>SQL> ALTER tablespace temp SHRINK SPACE KEEP 250m;</pre></div> Oracle uses a peculiar logic when shrinking tempfiles in a temporary tablespace. Let’s say you have a temporary tablespace that contains two 1GB tempfiles. You issue a command to shrink the tablespace to 1GB, as shown here: <div class="codeme"><pre>SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 1000M; Tablespace altered.</pre></div> If you query the V$TEMPFILE view, you will see this: <div class="codeme"><pre>SQL> SELECT file#, name, bytes/1024/1024 mb FROM v$tempfile; FILE# NAME MB ----- --------------------- --------- 1 [PATH]/temp01.dbf 999.9375 2 [PATH]/emp02.dbf' 1.0625</pre></div> The database shrinks one of the two tempfiles all the way down to 1MB and the other only by 1MB, leaving 999MB of space intact in that tempfile. If your goal is to shrink a particular tempfile down to a certain minimum, you can do so by specifying the name of the particular tempfile you want to shrink, as shown here: <div class="codeme"><pre>SQL> ALTER TABLESPACE temp SHRINK TEMPFILE tempfile '<PATH>/temp02.dbf' KEEP 100m; Tablespace altered.</pre></div> The ALTER TABLESPACE statement shown here shrinks just the tempfile you list by the amount you specify with the KEEP clause. It leaves the other tempfiles in the TEMP tablespace alone. The KEEP clause in the previous statement ensures that the specified tempfile retains 100MB of space. The following example shows how to shrink a single tempfile without any specific retained space: <div class="codeme"><pre>SQL> ALTER TABLESPACE temp SHRINK tempfile '<PATH>/temp03.dbf';</pre></div> Since no KEEP clause is specified in the previous statement, the database shrinks the tempfile to the minimum possible size, which is about 1MB. <h2>Default Temporary Tablespace</h2> When you create database users, you must assign a default temporary tablespace in which they can perform their temporary work, such as sorting. If you neglect to explicitly assign a temporary tablespace, the users will use the critical System tablespace as their temporary tablespace, which could lead to fragmentation of that tablespace, besides filling it up and freezing database activity. You can avoid these undesirable situations by creating a default temporary tablespace for the database when creating a database by using the DEFAULT TEMPORARY TABLESPACE clause. Oracle will then use this as the temporary tablespace for all users for whom you do not explicitly assign a temporary tablespace. Note that if you did not create a default temporary tablespace while creating your database, it is not too late to do so later. You can just create a temporary tablespace, as shown in the preceding examples, and make it the default temporary tablespace for the database, with a statement like below. However, please note this default temporary tablespace is only used for new users. For existing users, you have to alter the user to change the their temporary tablespace. <div class="codeme"><pre>SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temptbs02;</pre></div> You can find out the name of the current default temporary tablespace for your database by executing the following query: <div class="codeme"><pre>SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE ----------------------- ----------------- DEFAULT_TEMP_TABLESPACE TEMP</pre></div> <h2>12c database with Multitenant – Things to make a note of</h2> – There is one default temporary tablespace at the entire CDB level and you can create multiple temporary tablespaces at the CDB level and like traditional database, only one can be default temp tablespace at the CDB level. Users can be explicitly assigned some temp tablespaces at PDB level. – At the PDB level also, we can have the same structure like traditional one with multiple temp tablespaces and one default temp tablespaces. Users can be explicitly assigned some temp tablespaces at PDB level. – If a user has temp tablespace assigned explicitly at the CDB and PDB level, then the temp tablespace assigned to it depends on the container(PDB or CDB) in which it is present currently. – If a user is present in PDB but is not assigned any temp tablespace explicitly and the PDB also doesn’t have any default temp tablespace, then the default temp tablespace at the CDB level is assigned to that user(where as in traditional one we have system tablespace assigned).

Temporary tablespaces contain data that persists only for the duration of a user’s session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users.

A temporary tablespace does exist on a permanent basis as do other tablespaces, such as the System and Sysaux tablespaces. However, the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session, and the data can be shared by all users. The performance of temporary tablespaces is extremely critical when your application uses sort- and hash-intensive queries, which need to store transient data in the temporary tablespace.

After starting up an instance, the first statement that uses the temporary tablespace creates a sort segment, which is shared by all sort operations in the instance. When you shut down the database, the database releases this sort segment. You can query the V$SORT_SEGMENT view to review the allocation and deallocation of space to this sort segment. You can see who is currently using the sort segment by querying the V$SORT_USAGE view. Use the V$TEMPFILE and DBA_TEMP_FILES views to find out details about the tempfiles currently allocated to a temporary tablespace.

Creating a Temporary Tablespace

You create a temporary tablespace the same way as you do a permanent tablespace, with the difference being that you specify the TEMPORARY clause in the CREATE TABLESPACE statement and substitute the TEMPFILE clause for the DATAFILE clause. Here’s an example:

Example 1:

SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'temp01.dbf' SIZE 50M
AUTOEXTEND ON;

Tablespace created.

Example 2:

SQL> CREATE TEMPORARY TABLESPACE temp3
TEMPFILE 'temp02.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Tablespace created.

In example 1 the SIZE clause in the second line specifies the size of the datafile and hence the size of the temporary tablespace, as 50MB. In the statement, the AUTOEXTEND ON clause will automatically extend the size of the temporary file, and thus the size of the temporary tablespace. By default, all temporary tablespaces are created with uniformly sized extents, with each extent sized at 1MB.

In the example 2, the EXTENT MANAGEMENT clause is optional. The UNIFORM SIZE clause specifies a custom extent size of 16MB instead of the default extent size of 1MB.

NOTE: You use the TEMPFILE clause, not the DATAFILE clause, when you allocate space to a temporary tablespace. Oracle recommends that you use a locally managed temporary tablespace with a 1MB uniform extent size as your default temporary tablespace.

It is common to create a single temporary tablespace (usually named Temp) for each database, but you can have multiple temporary tablespaces, which are part of a temporary tablespace group, if your database needs them to support heavy sorting operations. In order to drop a default temporary tablespace, you must first use the ALTER TABLESPACE command to create a new default tablespace for the database. You can then drop the previous default temporary tablespace like any other tablespace.

Altering a Temporary Tablespace

You can issue the ALTER TEMPORARY TABLESPACE statement to perform various temporary tablespace management tasks, including adding a tempfile to grow a temporary tablespace. Below is an example showing how you can make the temporary tablespace larger:

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/tempo3.dbf' size 1000M reuse;

You can similarly use the following ALTER TABLESPACE command to resize a tempfile:

SQL> ALTER DATABASE TEMPFILE '/temp03.dbf RESIZE 200M;

And you can use the following statement to drop a tempfile and remove the operating system file:

SQL> ALTER DATABASE TEMPFILE '/temp03.dbf' DROP INCLUDING DATAFILES;

When you drop a tempfile belonging to a temporary tablespace, the tablespace itself will remain in use.

Shrinking Temporary Tablespaces

You may have to increase the size of a temporary tablespace to accommodate an unusually large job that makes use of the temporary tablespace. After the completion of the job, you can shrink the temporary tablespace using the SHRINK SPACE clause in an ALTER TABLESPACE statement. Here is an example:

SQL> ALTER TABLESPACE temp SHRINK SPACE;
Tablespace altered.

The SHRINK SPACE clause will shrink all tempfiles to a minimum size, which is about 1MB. You can employ the KEEP clause to specify a minimum size for the tempfiles, as shown here:

SQL> ALTER tablespace temp SHRINK SPACE KEEP 250m;

Oracle uses a peculiar logic when shrinking tempfiles in a temporary tablespace. Let’s say you have a temporary tablespace that contains two 1GB tempfiles. You issue a command to shrink the tablespace to 1GB, as shown here:

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 1000M;
Tablespace altered.

If you query the V$TEMPFILE view, you will see this:

SQL> SELECT file#, name, bytes/1024/1024 mb FROM v$tempfile;

FILE#  NAME                   MB
-----  --------------------- ---------
1      [PATH]/temp01.dbf     999.9375
2      [PATH]/emp02.dbf'      1.0625

The database shrinks one of the two tempfiles all the way down to 1MB and the other only by 1MB, leaving 999MB of space intact in that tempfile. If your goal is to shrink a particular tempfile down to a certain minimum, you can do so by specifying the name of the particular tempfile you want to shrink, as shown here:

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE tempfile '/temp02.dbf' KEEP 100m;
Tablespace altered.

The ALTER TABLESPACE statement shown here shrinks just the tempfile you list by the amount you specify with the KEEP clause. It leaves the other tempfiles in the TEMP tablespace alone. The KEEP clause in the previous statement ensures that the specified tempfile retains 100MB of space.

The following example shows how to shrink a single tempfile without any specific retained space:

SQL> ALTER TABLESPACE temp SHRINK tempfile '/temp03.dbf';

Since no KEEP clause is specified in the previous statement, the database shrinks the tempfile to the minimum possible size, which is about 1MB.

Default Temporary Tablespace

When you create database users, you must assign a default temporary tablespace in which they can perform their temporary work, such as sorting. If you neglect to explicitly assign a temporary tablespace, the users will use the critical System tablespace as their temporary tablespace, which could lead to fragmentation of that tablespace, besides filling it up and freezing database activity. You can avoid these undesirable situations by creating a default temporary tablespace for the database when creating a database by using the DEFAULT TEMPORARY TABLESPACE clause. Oracle will then use this as the temporary tablespace for all users for whom you do not explicitly assign a temporary tablespace.

Note that if you did not create a default temporary tablespace while creating your database, it is not too late to do so later. You can just create a temporary tablespace, as shown in the preceding examples, and make it the default temporary tablespace for the database, with a statement like below. However, please note this default temporary tablespace is only used for new users. For existing users, you have to alter the user to change the their temporary tablespace.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temptbs02;

You can find out the name of the current default temporary tablespace for your database by executing the following query:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME            PROPERTY_VALUE
-----------------------  -----------------
DEFAULT_TEMP_TABLESPACE  TEMP

Viewing Space Usage for Temporary Tablespaces

The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.

SQL> SELECT * from DBA_TEMP_FREE_SPACE;
 
TABLESPACE_NAME                     TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
----------------------------------- --------------- --------------- ----------
TEMP                                      250609664       250609664  249561088

Temporary Segment Handling in Temporary Tablespace

In RDBMS release 7.3, Oracle introduced the concept of a temporary tablespace. This tablespace would be used to hold temporary objects, like sort segments. For performance reasons, management of a tablespace that is defined as temporary was changed so that the allocated segments and extents would NOT be released and placed on the freelist, but would only be MARKED as free.

For example, when a sort occurs, Oracle allocates as many extents as are required to do the sort. At the end of the sort, the extents are marked as free, but they are not deallocated. The single sort segment approach avoids unnecessary space management:

A single sort segment is allocated and shared by multiple sort operations for a given instance.
Allocations and deallocations of space from this new sort segment will not require standard ST enqueue.
The first sort operation on the instance creates the sort segment. Subsequent sort operation may grow the sort segment by adding new extents or allocate the existing extents out of this segment.
The sort segment eventually grows to a stable state where no extents are allocated

No storage space deallocation after a sort operation, but only changing the status of the extents in the sort segments. As part of this, a new data structure was created. It is called the Sort Extent Pool and is allocated out of the Shared Pool in the SGA. This structure contains a description of all of the active sort segments and their extents in the sort segment. Processes that require access to the sort segment are synchronized by a local latch called Sort Extent Pool latch. The Sort Extent Pool latch can be found in the V$LATCH and V$LATCHNAME views.

As mentioned, there is a new tablespace type called TEMPORARY. By default, all tablespaces are created as PERMANENT. Permanent tablespaces can hold objects of all types, including temporary segments. Temporary segments created in a PERMANENT tablespace still follow the old algorithm and are cleaned up/removed by SMON after usage. Temporary tablespaces can ONLY hold sort segments, no other objects are allowed to be created in the tablespace. There are two ways a tablespace can be identified as TEMPORARY:

1. With the CREATE TABLESPACE…TEMPORARY command.
2. Using ALTER TABLESPACE…TEMPORARY.

12c database with Multitenant – Things to make a note of

– There is one default temporary tablespace at the entire CDB level and you can create multiple temporary tablespaces at the CDB level and like traditional database, only one can be default temp tablespace at the CDB level. Users can be explicitly assigned some temp tablespaces at PDB level.
– At the PDB level also, we can have the same structure like traditional one with multiple temp tablespaces and one default temp tablespaces. Users can be explicitly assigned some temp tablespaces at PDB level.
– If a user has temp tablespace assigned explicitly at the CDB and PDB level, then the temp tablespace assigned to it depends on the container(PDB or CDB) in which it is present currently.
– If a user is present in PDB but is not assigned any temp tablespace explicitly and the PDB also doesn’t have any default temp tablespace, then the default temp tablespace at the CDB level is assigned to that user(where as in traditional one we have system tablespace assigned).