Temporary tablespace group

Temporary tablespace groups allow an Oracle database to write to multiple temp tablespaces simultaneously. A user is assigned to a group of temp tablespaces instead of a single temporary tablespace.

It is sometimes required to create temporary tablespace groups as Oracle can only write to one temp files in a tablespace at a time. This is a great tuning technique for systems that doesn't use RAID storage.

Examples

Create a temporary tablespace and implicitly add it to a temporary tablespace group:

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE ‘/app/oradata/temp01.dbs’ SIZE 100M
TABLESPACE GROUP temp_ts_grp_a;

Remove a temporary tablespace from a temporary tablespace group:

ALTER TABLESPACE temp02 TABLESPACE GROUP ‘‘;

Add a temporary tablespace to a temporary tablespace group:

ALTER TABLESPACE temp03 TABLESPACE GROUP temp_ts_grp_b;

Assign a user to a default temporary tablespace group:

ALTER USER scott TEMPORARY TABLESPACE temp_ts_grp_a;

Monitor

To see what temporary tablespace groups exist, select from the DBA_TABLESPACE_GROUPS view.

Also see

• Temp file - special type for data file used for transient data
• Tablespace - container for segments (tables, indexes, etc.)