Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Database Administration / Oracle Tablespace Group

Oracle Tablespace Group

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

Introduction to Oracle tablespace groups

A tablespace group typically consists of multiple temporary tablespaces. A tablespace group allows a user to consume temporary space from multiple temporary tablespaces, instead of a single temporary tablespace. By using a tablespace group, you can avoid the problem caused when one temporary tablespace does not have enough space to hold the results of a sort.

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 explicitly. You 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;

Or ALTER TABLESPACE statement:

ALTER TABLESPACE tablespace_name TABLESPACE GROUP group_name;

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 * FROM DBA_TABLESPACE_GROUPS;

Moving a tablespace to another tablespace group

To move a temporary tablespace to another tablespace group you use the ALTER TABLESPACE statement. This statement moves the temporary tablespace tablespace_name to the tablespace group destination:

ALTER TABLESPACE tablespace_name TABLESPACE GROUP destination;

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;

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 which 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 in different sessions at the same time.
  • Reduce the contention in case 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;

Because the tablespace group tbsg1 has not existed, the statement also created the tablespace group tbsg1.

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

ALTER TABLESPACE temp TABLESPACE GROUP tbsg1;

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

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbsg1;

Finally, verify the current default temporary tablespace:

SELECT property_name, property_value FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';
oracle tablespace group - default temporary tablespace

In this tutorial, you have learned about the Oracle tablespace group and how to use tablespace group effectively to optimize internal Oracle operations.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Temporary Tablespace
Next Oracle CREATE USER

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.