Temporary segments do not span tablespaces of Temporary Tablespaces Group

I took for granted that single session with one big sort can benefit from tablespace group.
However, it is wrong ! No wonder our data warehouse often hit ora-1652.
--before make change , temp1,2,3 size is 16gb,16gb,19gb respectively
--This is means available temporary range is 16gb to 19gb.

The relevant metalink doc is 245645.1 and 248712.1

Reason is quite simple , Temporary segments do not span tablespaces. This easy to understand, same as other segment.


-- change support id to use small temp3 , which is the default database temporary tablespace (check from database_properties table)

-- remove temp1, temp2,temp3 from temp_group

alter tablespace TEMP1 tablespace group '';
alter tablespace TEMP2 tablespace group '';
alter tablespace TEMP3 tablespace group '';

--drop temp2

drop tablespace temp2 including contents and datafiles;

--epxand temp1;

--shrink temp3

--after make change , temp1,3 size is 40gb,10gb respectively
--assign application id to use big temporary tablespace


Since we don't parallel DML & have limited diskspace, tablespace group does't help.

From this practice, I think tablespace group is only good if you have lots of tablespace to create tablespaces with same size for round-robin assignment fashion.
Ideally, can create them on separate disks to reduce I/O contention.


How come important things is missed out in many articles searched by Google !?