Systems Engineering and RDBMS

Temporary tablespace and disk space allocation

Posted by decipherinfosys on November 8, 2007

As we all know, temporary tablespace is used in case of sorting (ORDER BY) and grouping (GROUP BY) operations. Temporary tables are also created in temporary tablespace. It contains data only for the duration of a session. Space allocation of the temporary tablespace can be viewed using V$SORT_SEGMENT view. It can be created either during CREATE DATABASE command by using the DEFAULT TEMPORARY TABLESPACE clause or we can create it separately using the CREATE TEMPORARY TABLESPACE clause.  We can also assign a temporary tablespace to a user by using the CREATE USER or ALTER USER command. Once assigned, for any sort or grouping operations, assigned temporary tablespace will be used. In absence of temporary tablespace, all the sort or group operations occur in the SYSTEM tablespace, which is not a good idea. Following is the syntax to create temporary tablespace.

2      TEMPFILE ‘C:\oracle\product\10.2.0\oradata\orcl\TEMP_TB.DBF’ SIZE 5M
3  /

Tablespace created.

If you notice the above syntax carefully, you will realize that we are using TEMPFILE instead of DATAFILE. This TEMPFILE clause works with only the TEMPORARY TABLESPACE statement. TEMPFILE never generates REDO information for data in temporary tablespace. Also there is no need to backup temp files.

TEMPFILE slightly differs with DATAFILE in terms of space allocation. Unlike DATAFILE, for which space is allocated at the creation time for the defined size, space for TEMPFILE is allocated when first statement which uses temporary tablespace is issued. Once allocated, space is never de-allocated or released until database is shutdown. This behavior gives benefit from the tablespace management perspective, but we may run into an issue, if database and/or temp tablespaces are not sized correctly. At later stage when there is a need for bigger chunk of tempfile allocation because of huge sort or grouping operation, allocation may fail if there is not enough disk space on the operating system.

We can avoid this scenario by making sure that required space is allocated  at the time of tablespace creation so later on there is no need to reallocate the space. Let us perform following steps in order to achieve this.

First step is to create regular tablespace. As we have already mentioned earlier that, space is allocated for DATAFILE at the time of tablespace creation itself, we will go ahead and create the regular tablespace.

2    DATAFILE ‘C:\oracle\product\10.2.0\oradata\orcl\TEMP_NEW.DBF’ SIZE 50M;

Above command will create a regular tablespace TEMP_NEW with 50M space allocated for corresponding datafile.  Second step is to drop the tablespace we created in the first step. Dropping tablespace does not drop the datafile so file remains there.


Third step is to create temporary tablespace and attach the previously created datafile (with proper space allocated) from step 1. In order to attach the orphan file to the tablespace, we need to use ‘REUSE’ clause.

2     TEMPFILE ‘C:\oracle\product\10.2.0\oradata\orcl\TEMP_NEW.DBF’

Tablespace created.

By using these sample steps, we can allocate enough space for the temporary tablespace on a permanent basis.

One Response to “Temporary tablespace and disk space allocation”

  1. […] by decipherinfosys on April 1, 2009 We covered temporary tablespaces in one of our blog post before.  In 10g, Oracle introduced the concept of tablespace groups for temporary tablespaces. We […]

Sorry, the comment form is closed at this time.

%d bloggers like this: