Temporary tablespace group

From Oracle FAQ
Jump to: navigation, search

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.

[edit] 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;

[edit] Monitor

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

[edit] Also see

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