Temporary Tablespace Groups

A tablespace group is a set from multple tablespaces, you can have one or more  temporary tablespace working as if it were one. The idea is to get a larger temporary tablespace that can be used for special processes or specific users, requiring intensive use of it. Usually databases have one or at most two tablespaces temporary one designed for online process (should make little use of the temporal) and one for batch processes (with great requirement temporarily due to large sorts, creation of temporary tables, etc .)

 

I’ve rarely seen beyond two temporary tablespaces. Although sometimes  I saw a huge temporary tablespace for a particular process, this is to create such temporary specifically for the job, use it to perform its functions and when process finished to delete the tablespace. Here you could have used a temporary group, adding a temporary online and a batch, but often clients are reluctant because they have doubts that the temporary space runs out of space for online  users/processes  and in certain situations ora-600 errors may occur. Many times we musmake a preliminary study and ponder whether merge temporary tablespace creating a group or create a new temporary that will fit the need we have.

 

They have the following characteristics;

  • It contains at least one tablespace.
  • There is no explicit limit on the maximum number of tablespaces that are contained in a group.
  •  It shares the namespace of tablespaces.

 

The basic commands are;

i) Create a  tablespace group (In this case file is created on ASM);

CREATE TEMPORARY TABLESPACE MY_TEMPORAL TEMPFILE '+DATABD1_DG'
     SIZE 40 G
     TABLESPACE GROUP TBSGRP_1;

 

ii) Add a temporary tablespace to a temporary group;

ALTER TABLESPACE SIMPLE_TEMPORARY TABLESPACE GROUP TBSGRP_1;

 

iii)  Remove a tablespace from a temporary group;

ALTER TABLESPACE SIMPLE_TEMPORARY TABLESPACE GROUP '';

 

iv)   Assigning a tablespace group as the default temporary tablespace;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TBSGRP_1;

 

The view DBA_TABLESPACE_GROUPS  gives us information on existing groups and how they are incorporated or which are part of that  groups.

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s