ODI-1228 with ORA-25153: Temporary Tablespace is Empty

Version : 

Oracle DB 11.2.0.4.0 - 64bit Production

Issue:

ETL using ODI failing with following error
   
ODI-1228: Task INT_HDI_ARC_HIERARCHY (Integration) fails on the target ORACLE connection HDI.
Caused By: java.sql.SQLException: ORA-25153: Temporary Tablespace is Empty

Analysis:

The error lookup with oerr command showed this

$ oerr ora 25153
25153, 00000, "Temporary Tablespace is Empty"
// *Cause: An attempt was made to use space in a temporary tablespace with no files.
// *Action: Add files to the tablespace using ADD TEMPFILE command.

Verified that the default tablespace of both DB and the DB user doing the ETL is temp1

SQL>  select property_value from database_properties where property_name like '%DEFAULT_TEMP_TABLESPACE%';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP1

SQL> select TEMPORARY_TABLESPACE from dba_users where USERNAME='HDI_UPGR2';

TEMPORARY_TABLESPACE
------------------------------
TEMP1

But when we select the tablepace name based on dba_temp_files view, TEMP1 is missing and onlt TEMP tablespace is seen as shown below.

SQL> select tablespace_name from dba_temp_files;

TABLESPACE_NAME
------------------------------
TEMP

Reason:

The file is deleted at OS level.

We cannot recreate the TEMP1 tablespace because the entry is still available in data dictionary and only the temp file associated with the tablespace is physically removed. If we try to recreate we will get following error.

ORA-01543: tablespace 'TEMP1' already exists

Solution:

Add a file to the TEMP1 tablespace using ALTER TABLESPACE command as shown here

SQL> alter tablespace temp1 add tempfile '<FILE_PATH>/temp01.dbf' size 100m autoextend on next 1m maxsize unlimited;

Tablespace altered.

Now when we query dba_temp_files, we can get TEMP1 as well.


SQL> select tablespace_name from dba_temp_files;

TABLESPACE_NAME
------------------------------
TEMP
TEMP1

ETL succeded after that