Oracle create temporary tablespace group

Since Oracle 10 you could have made temporary tablespace group. In this group you can add multiple temporary tablespaces which contains multiple tempfiles. You can also set temporary tablespace group to your default temp tablespace.

When you use temporary tablespace group Oracle will distribute temp operations in all tablespaces in group. If you use single temp tablespace Oracle won't distribute operations on several tempfiles even tablespace would have more tempfiles in it. So with temporary tablespace group you can spread I/O from big sort operations on several tempfiles (and different disks if you have the tempfiles in different disks).


# Create 2 new temp tablespaces into temp tablespace group temp_group (this will also create the temporary tablespace group if it does not exists yet):

SQL> create temporary tablespace temp_g01_tbs
tempfile '/u01/app/oradata/orcl/TEMPg01.DBF' size 2G
extent management local uniform size 1M
tablespace group temp_group;


SQL> create temporary tablespace temp_g02_tbs
tempfile '/u01/app/oradata/orcl/TEMPg02.DBF' size 2G
extent management local uniform size 1M
tablespace group temp_group;


From following views you can see tempfiles and temporary tablespace groups:
SQL> select * from dba_temp_files;
SQL> select * from dba_tablespace_groups;


Here you can see users default tablespaces:
SQL> select username, default_tablespace, temporary_tablespace from dba_users order by username;

And here you can change database default temporary tablespace to temporary tablespace group you just created:
SQL> alter database default temporary tablespace temp_group;