Oracle 10 Gb temporary tablespace Group

Overview of temporary tablespace groups

In Oracle 10 Gb, you can create multiple temporary tablespaces and form a temporary tablespace group. In this way, you can use multiple temporary tablespaces in the group for sorting, A temporary tablespace group must have at least one temporary tablespace. the maximum number of tablespaces is unlimited. The group name cannot be the same as the name of a tablespace.
A temporary tablespace group is created by specifying the group clause when creating a temporary tablespace. If you delete all temporary tablespaces in the group, the group also disappears.
We can move a tablespace from one group to another, delete a temporary tablespace from one group, or add a new tablespace to the group.
Using Temporary tablespace groups has the following advantages:
1. You can specify multiple temporary tablespace at the database level to avoid disk sorting problems when the temporary tablespace is insufficient;
2. When a user has multiple sessions at the same time, they can use different temporary tablespace;
3. In parallel operations, different subordinate processes can use different temporary tablespace
Use temporary tablespace groups
Create a temporary tablespace group:
SQL> create temporary tablespace tempts1 tempfile
2'/home/oracle/temp1_02.dbf' size 2 M tablespace group group1;
Tablespace created
SQL> create temporary tablespace tempts2 tempfile
2'/home/oracle/temp2_02.dbf' size 2 M tablespace group group2;
Tablespace created
Query temporary tablespace groups
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP1 TEMPTS1
GROUP2 TEMPTS2
Move a tablespace from a temporary tablespace group to another temporary tablespace group:
SQL> alter tablespace tempts1 tablespace group GROUP2;
Tablespace altered
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP2 TEMPTS1
GROUP2 TEMPTS2
Designate a temporary tablespace group to the user
SQL> alter user scott temporary tablespace GROUP2;
User altered
Set temporary tablespace at the database level
SQL> alter database <db_name> default temporary tablespace GROUP2;
Database altered.
Delete a temporary tablespace group (delete all temporary tablespaces that constitute the temporary tablespace Group)
SQL> drop tablespace tempts1 including contents and datafiles;
Tablespace dropped
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP2 TEMPTS2
SQL> drop tablespace tempts2 including contents and datafiles;
Tablespace dropped
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME