Temporary Tablespace

Temporary Tablespace: - This tablespace contains temporary data. ex:Sorting information

Temparory Tablespace Groups:

This is available from oracle 10g onwards.By using this feature number of temporary tablespaces can be assign to a temporary tablespace group. This feature improves the database performance. All users temporary calculations are distributed among the temporary tablespaces through the temporary tablespaces group.

Note : - In this method temporary tablespace group is default.

Managing and configuration of Temporary tablespace Groups :

SQL>select * from v$tablespace;

SQL>set wrap off

SQL>select * from database_properties;

SQL>set wrap on

SQL> create temporary tablespace temp_PROD tempfile '/u01/PROD/tempPROD.dbf' size 10m tablespace group temp_PROD_group;

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_PROD_GROUP                TEMP_PROD

SQL>create temporary tablespace temp_PROD2

                  tempfile ‘/u01/PROD/tempPROD2.dbf’ size 10m;

SQL>alter tablespace temp_PROD2 tablespace group  temp_PROD_group;

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_PROD_GROUP                TEMP_PROD

TEMP_PROD_GROUP                TEMP_PROD2

sys>alter database default temporary tablespace TEST_PROD_Group;

sys>select * from database_properties;

sys>alter tablespace temp_PROD tablespace group temp_PROD_group ;