Temporary Tablespaces Group in Oracle
This post explains why and how to create groups of temporary tablespaces. Grouping temporary tablespaces within a single group enable a user to consume temporary space from multiple tablespaces.
What is Temporary tablespace group
A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces. It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace. A temporary tablespace group name can appear where a tablespace name would appear when assigning a default temporary tablespace for the database or assigning a temporary tablespace for a user. It is not created explicitly. It gets created implicitly when the first temporary tablespace is assigned to it and it is dropped when the last temporary tablespace is removed from it.
Any temporary tablespace can be:
- moved from a group to another (if the group does not exist, it gets created)
- removed from a group
- added to a group if standalone
The temporary tablespace group helps to:
- avoid the problem where one temporary tablespace is inadequate to hold the results of a sort, particularly on a partition table.
- to use different temporary tablespaces when a user connects in multiple sessions simultaneously.
- enable parallel execution servers in a single parallel operation to use multiple temporary tablespaces.
Examples
1. Creating temporary tabalespace group:
SQL> create temporary tablespace LMTEMP 1
tempfile 'D:\ORACLE10\ORCL\temp1_01.dbf' size 50M
tablespace group GROUP1;
The group GROUP1 did not exist before the creation of the new temporary tablespace LMTEMP1. LMTEMP1 is the first temporary tablespace to belong to this group.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
-------------- --------------------
GROUP1 LMTEMP1
2. Create temporary tablespace:
SQL> create temporary tablespace lmtemp2
tempfile 'D:\ORACLE10\ORCL\temp1_02.dbf' size 2M
tablespace group group1;
Tablespace created.
A new temporary tablespace LMTEMP2 is added to the existing group GROUP1.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
----------------- --------------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2
3. A temporary tablespace can be moved to a new or another group:
SQL> alter tablespace LMTEMP1 tablespace group GROUP2 ;
Tablespace altered.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
-------------- -------------------
GROUP2 LMTEMP1
GROUP1 LMTEMP2
4. A temporary tablespace can be removed from a group so as to be standalone;
SQL> alter tablespace LMTEMP1 tablespace group '';
Tablespace altered.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
---------------- ----------------
GROUP1 LMTEMP2
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
---------------------
TEMP
LMTEMP1
LMTEMP2
Of course, this standalone temporary tablespace can be re-incorporated to a group:
SQL> alter tablespace LMTEMP1 tablespace group GROUP1;
Tablespace altered.
5. As the namespace is shared, the group name cannot be same as tablespace name. You should get below error if you try to create tablespace with name same as group name.
SQL> create temporary tablespace lmtemp5
tempfile 'D:\ORACLE10\ORCL\temp1_05.dbf' size 50M
tablespace group lmtemp5;
create temporary tablespace lmtemp5
*
ERROR at line 1:
ORA-10918: TABLESPACE GROUP name cannot be the same as tablespace name
6. If you drop all temporary tablespaces from GROUP1, the group is automatically removed:
SQL> create temporary tablespace LMTEMP3
tempfile 'D:\ORACLE10\ORCL\temp1_03.dbf' size 2M
tablespace group GROUP2;
Tablespace created.
SQL> create temporary tablespace LMTEMP4
tempfile 'D:\ORACLE10\ORCL\temp1_04.dbf' size 2M
tablespace group GROUP2;
Tablespace created.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------ -----------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2
GROUP2 LMTEMP3
GROUP2 LMTEMP4
SQL> drop tablespace lmtemp3 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace lmtemp4 including contents and datafiles;
Tablespace dropped.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
-------------- -------------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2
7. A default temporary tablespace group can be assigned to a user:
SQL> alter user scott temporary tablespace GROUP1;
User altered
8. A temporary tablespace group can also be assigned as the default temporary tablespace group at the database level:
SQL> alter database <db_name> default temporary tablespace GROUP1;
Database altered.</db_name>
Now, any of the temporary tablespaces belonging to the default database temporary tablespace group cannot be dropped:
SQL> drop tablespace LMTEMP2 including contents and datafiles;
drop tablespace lmtemp2 including contents and datafiles
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group
9. A user can benefit from this new feature , using different temporary tablespaces while connected under distinct sessions and performing several sort operations simultaneously:
-- DBA Session --
SQL> select username, session_num, tablespace from v$sort_usage;
no rows selected
-- SCOTT Session 1 --
SQL> select a.table_name, b.table_name from dict A, dict B;
-- SCOTT Session 2 simultaneously--
SQL> select a.table_name, b.table_name from dict A, dict B;
-- DBA Session --
SQL> select username, session_num, tablespace from v$sort_usage;
USERNAME SESSION_NUM TABLESPACE
----------------- ----------- -------------
SCOTT 97 LMTEMP2
SCOTT 150 LMTEMP1
User SCOTT is sorting on 2 different temporary tablespaces.
ORA-10921 Error
ORA-10921 error is reported if we try to drop any of the temporary tablespaces belonging to the default database temporary tablespace group.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
--------------- -----------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2
GROUP2 LMTEMP3
GROUP2 LMTEMP4
SQL> alter database [db_name] default temporary tablespace GROUP1;
Database altered.
SQL> drop tablespace LMTEMP2 including contents and datafiles;
drop tablespace lmtemp2 including contents and datafiles
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group
The solution will be either to drop the tablespace group or to remove the tablespace from the group.
SQL> alter tablespace LMTEMP2 tablespace group '';
Tablespace altered.
The tablespace can now be dropped:
SQL> drop tablespace lmtemp2 including contents and datafiles;
Tablespace dropped.