1. Introduction
A tablespace group can allow a user to consume temporary tablespace from multiple table spaces. Table space groups have the following characteristics:
1) contains at least one table space. There is no limit to the maximum number of table spaces contained in a table space group.
2) and Tablespace share namespace, cannot have the same name as any table space.
When you assign a temporary tablespace to a database or user, you can determine the tablespace group name wherever the tablespace name can appear.
You do not have to explicitly create a table space group. When you specify the first temporary tablespace for a table space group, the table space group is implicitly created. When the last temporary tablespace that the Tablespace group contains is removed, the table space group is also deleted.
Using a table space group instead of a temporary tablespace can reduce the number of separate table spaces that are not sufficient to accommodate the sort results, especially on large tables with multiple partitions. A table space group can also enable a single parallel operation to use multiple temporal tablespace enables multiple parallel execution servers.
2. Table Space Group related information
1) Data dictionary
- Cdb_tablespace_groups
- Dba_tablespace_groups
- ts$
2) System permissions
- ALTER tablespace
- DROP tablespace
- UNLIMITED tablespace
- CREATE tablespace
- MANAGE tablespace
3. Create a table space group
1) CREATE TABLE space
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;
2) Change Table space
ALTER tablespace <tablespace_name>
Tablespace GROUP <group_name>;
Altertablespace tools tablespace GROUP apps_grp;
SELECT * from Dba_tablespace_groups;
4. Remove Group members
ALTER tablespace<tablespace_name> tablespace GROUP ';
Altertablespace batchtemp tablespace GROUP ";
SELECT * from Dba_tablespace_groups;
5. Specify a default tablespace group
ALTER database<tablespace_name>
DEFAULT Temporary tablespace<group_name>;
ALTER DATABASE orabase DEFAULT temporary tablespace app_grp;
SELECT * from Dba_tablespace_groups;
Table space Group for fault tolerance & performance New in oracle12c