Temporary Tablespace Group : ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP

Our Application team has encountered the below error while running the month end jobs. So below are the recommendation which fixed the issue.

##########################
## Error
##########################

ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP

### Full Error

ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance host01.example.com:aua01p2 (2)
ORA-01652: unable to extend temp segment by 256 in tablespace AU_TEMP
ORA-06512: at "au_ADHOC.au_EOM_REPORTS_NEW", line 3334
ORA-06512: at "au_ADHOC.au_EOM_REPORTS_NEW", line 3904
ORA-06512: at line 1

##########################
#  Error Occurred
##########################

Error occured while application month end jobs are running in 10.2.0.3.0 version

##########################
## Command Executed
##########################

Application jobs

##########################
## Issue Description.
##########################

### 10.2.0.3.0

We have a temporary tablespace of size 63 GB, due to month end multiple jobs were running at the same time and it errors out with unable to extend error.

##########################
## Solution
##########################

From oracle 10g onwards, we can create a temporary tablespace group such that if one tablespace in that group ran out of space then the other available tablespace will be used.

A new group will be created when a tablespace is assigned to the group.

SQL> Select * from dba_tablespace_groups;

no rows selected

Alter tablespace AU_TEMP tablespace group au_TMP_GROUP;

Alter tablespace USER_TEMP tablespace group au_TMP_GROUP;

SQL> Select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ -------------------------
au_TMP_GROUP                  USER_TEMP
au_TMP_GROUP                  AU_TEMP

And then change the tablespace of the user who is executing the job.

SET LINES 200
COL USERNAME FOR A25
COL ACCOUNT_STATUS FOR A18
COL DEFAULT_TABLESPACE FOR A20
COL PROFILE FOR A20
col password for a25
col temporary_tablespace for a15

select username,account_status,password,PROFILE,DEFAULT_TABLESPACE,temporary_tablespace,created from dba_users
where username='au_ADHOC' order by username;

USERNAME                  ACCOUNT_STATUS     PASSWORD                  PROFILE              DEFAULT_TABLESPACE   TEMPORARY_TABLE CREATED
------------------------- ------------------ ------------------------- -------------------- -------------------- --------------- ---------
au_ADHOC                 OPEN               DEFAULT              au_ADHOCD1M         AU_TEMP     09-DEC-04

#### Assigning the user the newly created tablespace group.

Alter user au_adhoc temporary tablespace au_tmp_group;

SQL> Alter user au_adhoc temporary tablespace au_tmp_group;

User altered.

SQL> select username,account_status,password,PROFILE,DEFAULT_TABLESPACE,temporary_tablespace,created from dba_users
where username='au_ADHOC' order by username;
  2
USERNAME                  ACCOUNT_STATUS     PASSWORD                  PROFILE              DEFAULT_TABLESPACE   TEMPORARY_TABLE CREATED
------------------------- ------------------ ------------------------- -------------------- -------------------- --------------- ---------
au_ADHOC                 OPEN               DEFAULT              au_ADHOCD1M         au_TMP_GROUP   09-DEC-04

#### To unassign the tablespace from the temp tablespace group.

Alter tablespace USER_TEMP tablespace group '';


SQL> Select * from dba_tablespace_groups;

no rows selected