Temporary Tablespace Group

Tablespace groups allow users to use more than one tablespace to store temporary segments.It contains only temporary tablespace.It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group.

Benefits:

-It allows the user to use multiple temporary tablespaces in different sessions at the same time.

-It allows a single SQL operation to use multiple temporary tablespaces for sorting.

How can we create tablespace group?

ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;

How to add new tablespace into this group?

CREATE TEMPORARY TABLESPACE temp2

  TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M

  TABLESPACE GROUP temp_ts_group;

How can we view tablespace group?

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_TS_GROUP                  TEMP

TEMP_TS_GROUP                  TEMP2

How can we delete a tablespace group?

ALTER TABLESPACE temp TABLESPACE GROUP ' ';

HTH:-)