Oracle Temporary tablespace group

Oracle Temporary tablespace group a Temporary tablespace group contains at least one Temporary tablespace. The temporary tablespace group cannot be created explicitly. It is automatically created when the first temporary tablespace is assigned to the Group and deleted when all the temporary tablespace in the group is removed. Operations supported by the temporary tablespace group: you can move the temporary tablespace from one group to another (if the group does not exist, it is automatically created ). You can remove the temporary tablespace from the group. You can add temporary tablespace that does not belong to any group to a group. Benefits of using a temporary tablespace group: prevent a temporary tablespace from having insufficient space. When a single user connects to multiple sessions at the same time, different temporary tablespace can be used. Multiple temporary tablespace can be used in parallel operations. The following are examples: 1. create temporary tablespace group SQL> create temporary tablespace TMP4 tempfile '/u01/app/oracle/oradata/PROD/tmp04.dbf' size 64 M autoextend on maxsize 2G extent management local uniform size 1 M tablespace group TMPGRP; 2. view information about temporary tablespace groups in the system SQL> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ -------------------------------- TMPGRP TMP4 3, add SQL> create temporary tablespace TMP3 tempfile '/u01/app/oracle/oradata/PROD/tmp03.dbf' size 64 M autoextend on maxsize 2G extent management local uniform size 1 M; tablespace created. SQL> create temporary tablespace TMP2 tempfile '/u01/app/oracle/oradata/PROD/tmp02.dbf' size 64 M autoextend on maxsize 2G extent management local uniform size 1 M; Tablespace created. SQL> create temporary tablespace TMP1 tempfile '/u01/app/oracle/oradata/PROD/tmp01.dbf' size 64 M autoextend on maxsize 2G extent management local uniform size 1 M; Tablespace created. 4. move the temporary tablespace to the new temporary tablespace group SQL> alter Tablespace TMP1 tablespace GROUP TMPGRP; tablespace altered. SQL> alter tablespace TMP2 tablespace GROUP TMPGRP; Tablespace altered. SQL> alter tablespace TMP3 tablespace GROUP TMPGRP; Tablespace altered. 5. Check the information about the temporary tablespace group in the system again. SQL> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME -------------------------- TMPGRP TMP1TMPGRP TMP2TMPGRP TMP3TMPGRP TMP4 6. remove temporary tablespace from temporary Tablespace group SQL> drop tablespace tmp4 including contents and datafiles; Tablespace dropped. 7. Check the information about the temporary tablespace group in the system again. SQL> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME -------------------------- TMPGRP TMP1TMPGRP TMP2TMPGRP TMP3 8. you can specify the database or the user's default temporary tablespace as the temporary tablespace group SQL> alter Database default temporary tablespace TMPGRP; database altered. SQL> select * from database_properties where property_NAME like '% DEFAULT_TEMP_TABLESPACE %'; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION -------------------- ------------------------------------------------------------ DEFAULT_TEMP_TABLESPACE TMPGRP Name of default temporary tablespace can be seen, in this case, the default temporary tablespace of the database is the temporary tablespace group TMPGRP.