TEMP TBS Oracle 18cR2 Tablespace Groups
Oracle Tablespace Groups
Version 18.2.0.0

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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 tools TABLESPACE GROUP apps_grp;

SELECT *
FROM dba_tablespace_groups;
 
Remove A Group Member
Remove Tablespace from Group ALTER TABLESPACE <tablespace_name> TABLESPACE GROUP '';
ALTER TABLESPACE batchtemp TABLESPACE GROUP '';

SELECT *
FROM dba_tablespace_groups;
 
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;

Related Topics
Data Files
DDL Statements
Tablespaces
What's New In 12cR2
What's New In 18cR1

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved