Multiple Temporary Tablespaces - Oracle DBA

Multiple Temporary Tablespaces

Using tablespace groups:-

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, so its name cannot be the same as any tablespace.
  • You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.

You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.
Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.
The view dba_tablespace_groups lists tablespace groups and their member tablespaces.

 Creating a tablespace group:-

You create a tablespace group implicitly when you include the tablespace group clause in the create temporary tablespace or alter tablespace statement and the specified tablespace group does not currently exist.

For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

Sql>create temporary tablespace lmtemp2 tempfile ‘/u02/oracle/data/lmtemp201.dbf’ size 50m      tablespace group group1;

Sql>alter tablespace lmtemp tablespace group group2;

 Changing members of a tablespace group:-

You can add a tablespace to an existing tablespace group by specifying the existing group name in the tablespace group clause of the create temporary tablespace or alter tablespace statement.

The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and lmtemp3.

Sql>create temporary tablespace lmtemp3 tempfile ‘/u02/oracle/data/lmtemp301.dbf’ size 25m     tablespace group group1;

The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2 already belongs to group1, it is in effect moved from group1 to group2:

Sql>alter tablespace lmtemp2 tablespace group group2;

Now group2 contains both lmtemp and lmtemp2, while group1 consists of only tmtemp3.

You can remove a tablespace from a group as shown in the following statement:

Sql>alter tablespace lmtemp3 tablespace group ”;

Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this results in the implicit deletion of group1.

Assigning a tablespace group as the default temporary tablespace:-

Use the alter database…defaulttemporarytablespace statement to assign a tablespace group as the default temporary tablespace for the database. For example:

Sql>alter database sample default temporary tablespace group2;

Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmtemp2.

If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first remove the tablespace from the tablespace group. Likewise, you cannot drop a single temporary tablespace as long as it is the default temporary tablespace.

Enroll for Instructor Led Live ORACLE DBA TRAINING


Get Updates on Tech posts, Interview & Certification questions and training schedules