Oracle | Tablespace Groups

Oracle Tablespace Groups Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has 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, 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.

Data Dictionary Objects

CDB_TABLESPACE_GROUPS

DBA_TABLESPACE_GROUPS

TS$

System Privileges

ALTER TABLESPACE	DROP TABLESPACE	UNLIMITED TABLESPACE
CREATE TABLESPACE	MANAGE TABLESPACE

Create Tablespace Group

With Create Tablespace CREATE TEMPORARY TABLESPACE <tablespace_name> TEMP <data_file_path_and_name> SIZE <integer> <K | M | G | T | P | E> TABLESPACE GROUP <group_name>;

CREATE TEMPORARY TABLESPACE batchtemp

      TEMPFILE '/u02/oradata/temp04.dbf'

      SIZE 2E

      TABLESPACE GROUP temp_grp;

      

      desc dba_tablespace_groups

      

      SELECT *

      FROM dba_tablespace_groups;

With Alter Tablespace ALTER TABLESPACE <tablespace_name> TABLESPACE GROUP <group_name>;

ALTER TABLESPACE temp  TABLESPACE GROUP 
      tempgrp;

      

      SELECT *
      FROM dba_tablespace_groups;

      

      GROUP_NAME                     
      TABLESPACE_NAME

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

      TEMPGRP                        
      TEMP

Remove A Group Member

Remove Tablespace from Group ALTER TABLESPACE <tablespace_name> TABLESPACE GROUP '';

ALTER TABLESPACE batchtemp TABLESPACE GROUP '';

      

      SELECT *
      FROM dba_tablespace_groups;

      

      GROUP_NAME                     
      TABLESPACE_NAME

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

Default Temporary Tablespace Assignment

Make a Group the Default Group ALTER DATABASE <tablespace_name> DEFAULT TEMPORARY TABLESPACE <group_name>;

ALTER DATABASE orabase DEFAULT TEMPORARY TABLESPACE app_grp;

      

      SELECT * 

      FROM dba_tablespace_groups;