Adding Oracle temp space for ARC (ETL failure)

• Article Type: General
• Product: Aleph
• Product Version: 18.01

Description:
For the last month, our weekly ETL has failed with the message:
ORA-12801: error signaled in parallel query server P003
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

We've increased the size of the TEMP tablespace twice, but it hasn't helped. Can you help us figure out what's going on?

Resolution:
The job of the ETL process is to transfer data from the Aleph database into the ARC database on the ARC server. So, at some point, the process needs to access the Oracle instance on the ARC server in order to get the data over there. The step where this process fails (or rather, just produces an error), is during this part. So, the temp space needs to be added to the ARC Oracle database on the ARC server. The temp space you added previously was to the Aleph oracle database, so that had no impact on this error.

There are no utilities for adding Oracle space for ARC as there are for Aleph. You just need to do it using sqlplus. You will need to have the information for an Oracle user that has DBA privileges. The ARC_ADMIN user does not have this level of authorization.

How to increase temp tablespace for ARC:

To see a list of Oracle temp files:
sqlplus $ARC_ADMIN
set linesize 999
column file_name format a60
select file_name,tablespace_name,bytes/1024/1024 "Size in MB" from dba_temp_files;

To add a temp file you will need an oracle user with DBA privileges:
sqlplus 'USER/PASSWORD as sysdba'
alter tablespace <Temp tablespace name> add tempfile '<new file full pathname>' size <file size>;
For example:
alter tablespace temp add tempfile '/exlibris/oradata/crn/crn_temp_04.dbf' size 6000M;

• Article last edited: 10/8/2013