Table space Group for fault tolerance & performance New in oracle12c

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

• Grammar

CREATE Temporary tablespace<tablespace_name>

TEMP <data_file_path_and_name>

SIZE <integer> <k | M | G | T | P | E>

Tablespace GROUP <group_name>;

• Example

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

• Grammar

ALTER tablespace <tablespace_name>

Tablespace GROUP <group_name>;

• Example

Altertablespace tools tablespace GROUP apps_grp;

SELECT * from Dba_tablespace_groups;

4. Remove Group members

• Grammar

ALTER tablespace<tablespace_name> tablespace GROUP ';

• Example

Altertablespace batchtemp tablespace GROUP ";

SELECT * from Dba_tablespace_groups;

5. Specify a default tablespace group

• Grammar

ALTER database<tablespace_name>

DEFAULT Temporary tablespace<group_name>;

• Example

ALTER DATABASE orabase DEFAULT temporary tablespace app_grp;

SELECT * from Dba_tablespace_groups;

Table space Group for fault tolerance &amp; performance New in oracle12c