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
