Oracle Tablespace Group

Summary: In this tutorial, you will learn about Oracle temporary tablespace groups and how to use the tablespace group effectively to optimize internal Oracle operations.

Introduction to Oracle tablespace groups #

A tablespace group is a logical grouping of one or more temporary tablespaces.

A tablespace group enables Oracle to distribute temporary data across multiple temporary tablespaces, providing better performance and scalability.

The following describes the property of a tablespace group:

• A tablespace group must contain at least one temporary tablespace.
• The name of a tablespace group cannot be the same as any tablespace.
• A tablespace group can be assigned as a default temporary tablespace for the database or a temporary tablespace for a user.

Creating a tablespace group #

Oracle does not provide a statement to create a tablespace group. However, you can create a tablespace group when you assign the first temporary tablespace to the group using the CREATE TEMPORARY TABLESPACE statement:

CREATE TEMPORARY TABLESPACE tablespace_name 
    TEMPFILE 'path_to_file'
    SIZE 50M
    TABLESPACE GROUP group_name;Code language: SQL (Structured Query Language) (sql)

Or ALTER TABLESPACE statement:

ALTER TABLESPACE tablespace_name 
    TABLESPACE GROUP group_name;Code language: SQL (Structured Query Language) (sql)

Removing a tablespace group #

Oracle automatically drops a tablespace group when you remove the last temporary tablespace from the tablespace group.

Viewing tablespace groups #

The view DBA_TABLESPACE_GROUPS lists all tablespace groups and their member temporary tablespace.

SELECT
  tablespace_name,
  group_name
FROM
  dba_tablespace_groups;Code language: SQL (Structured Query Language) (sql)

Moving a tablespace to another tablespace group #

To move a temporary tablespace to another tablespace group, you use the ALTER TABLESPACE statement.

The following statement moves the temporary tablespace tablespace_name to the tablespace group destination:

ALTER TABLESPACE tablespace_name
TABLESPACE GROUP destination;Code language: SQL (Structured Query Language) (sql)

Note that the destination tablespace group must exist.

Assigning a tablespace group as the default temporary tablespace #

To assign a tablespace group as the default temporary tablespace for a database, you use the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_group;Code language: SQL (Structured Query Language) (sql)

All users who have not been assigned a temporary tablespace will use the temporary tablespaces contained in the tablespace_group.

Note that you cannot drop any temporary tablespace that belongs to a tablespace group that is specified as a default temporary tablespace. In this case, you first need to de-assign the temporary tablespace from the tablespace group and then remove it.

Benefits of using a tablespace group #

A tablespace group brings the following benefits:

• Enable multiple default temporary tablespaces to be used at the database level.
• Allow the user to use multiple temporary tablespaces simultaneously across different sessions.
• Reduce the contention when you have multiple temporary tablespaces.

Oracle tablespace group examples #

First, create a new temporary tablespace and assign it to the tablespace group tbs1:

CREATE TEMPORARY TABLESPACE temp2 
TEMPFILE 'temp2.dbf' 
SIZE 100M 
TABLESPACE GROUP tbsg1;Code language: SQL (Structured Query Language) (sql)

Since the tablespace group tbsg1 does not exist, the statement creates the tablespace group tbsg1.

Second, assign the temp temporary tablespace temp to the tbsg1 tablespace group:

ALTER TABLESPACE temp TABLESPACE GROUP tbsg1;Code language: SQL (Structured Query Language) (sql)

Third, assign the tablespace group gbsg1 as the default temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbsg1;Code language: SQL (Structured Query Language) (sql)

Finally, verify the current default temporary tablespace:

SELECT 
    property_name, 
    property_value 
FROM 
    database_properties 
WHERE 
    property_name='DEFAULT_TEMP_TABLESPACE';Code language: SQL (Structured Query Language) (sql)

Summary #

• A tablespace group is a logical grouping of one or more temporary tablespaces.
• Use tablespace groups to distribute temporary data across multiple temporary tablespaces for better performance and scalability.