Temporary tablespace groups in 10g

The main idea behind temporary tablespace groups is to improve the scalability of operations that require more temporary space. This is a new feature introduced in 10g.

There must be a minimum of one tablespace in a temporary tablespace group and groups cannot exist without at least one temporary tablespace.

The group will not exist when the last tablespace member is removed from it. For a user to be able to use the group, assign the tablespace group name when assigning temporary tablespace to users.

There is no explicit limit on the maximum number of tablespaces that are contained in a group.

EXAMPLES
– create a temporary tablespace without a group

CREATE TEMPORARY TABLESPACE temp1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP ‘’; -- same as not specifying a group



– create a temporary tablespace group with one tablespace (TEMP1) in it:

CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE ‘C:/oracle/10g/temp1.dbf ‘ SIZE 250M
TABLESPACE GROUP TEMPGRP1;

If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:



– user TEMPGRP1 as the default temporary tablespace for all new users in the database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPGRP1;


- Make user ALI use the temporary tablespace group

ALTER USER ALI TEMPORARY TABLESPACE TEMPGRP1;