Temporary tablespace and disk space allocation

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.

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.

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.

SQL> DROP TABLESPACE TEMP_NEW;

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.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on November 8, 2007 at 10:24 am and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
Responses are currently closed, but you can trackback from your own site.