How to add Multiple Temporary Tablespace

In Oracle Database we can add multiple temporary tablespaces using tablespace groups, tablespace groups allowed to use multiple temporary tablespaces to store temp segments. The temp tablespace group is created automatically when you add the first tablespace is assigned a group.

Tablespace and Group Name

A tablespace group name and tablespace name must not be the same because both are sharing the same namespace as tablespace.

Add existing tablespace in Group using below query.

alter tablespace temp tablespace group tmp_group;

Now Add new tablespace in above newly created group using below query.

create temporary tablespace tmp
tempfile ‘/u01/app/oracle/oradata/db11g/tmp01.dbf’
size 10m
tablespace group tmp_group;

You can check assigned tablespace to group using the following query.

select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
————- ————————
TMP_GROUP TEMP
TMP_GROUP TMP

When the group is created, you can be assigned like a tablespace to user or you can set it default temporary tablespace.

Now assign a temporary tablespace group for a user.

alter user hr temporary tablespace tmp_group;

Now set a temp tablespace group as default temporary tablespace.

alter database default temporary tablespace tmp_group;

You can also remove a tablespace from a group.

alter tablespace tmp tablespace group ‘ ‘;

Now if you check

select * from dba_tablespace_groups;

GROUP_NAME ABLESPACE_NAME
———– —————-
TMP_GROUP TEMP

In the tablespace group, no specific number of maximum tablespace but must contain one tablespace. If you remove the last tablespace from the tablespace group then the group is automatically removed. If the group is assigned as a default temp tablespace then you cannot remove the last member from the group. To remove the last tablespace from the group see the example.

Before removing the last tablespace from the group you have to set your default temp tablespace another one.

alter database default temporary tablespace tmp;

Now remove last tablesapce from the group.

alter tablespace tmp tablespace group ”;

Now check the group is exist or not.

select * from dba_tablespace_groups;

Read - How to Install Oracle 11g R2 on RHEL-6 step by step

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology