ORA-25153 – Temporary Tablespace is empty

Usually you will face this problem whenever you have no datafiles at a certain temporary tablespace.

Troubleshooting:

--Identify the temporary tablespace with no datafiles
select tbs.tablespace_name "Tablespaces",
dbf.tablespace_name "Tempfiles"
from dba_tablespaces tbs
left outer join dba_temp_files dbf
on tbs.tablespace_name = dbf.tablespace_name
where tbs.contents='TEMPORARY'
and dbf.tablespace_name is null;

Now that you know that is the temporary tablespace that contains no tempfiles, you can simply add tempfiles to it or confirm if this is your default temporary tablespace. To do it, run the query below

--Check what is the default temporary tablespace
select property_value from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';

You can now choose more than 1 way to solve the issue, go for what suits you best:

Solution 1:

You want to add a tempfile to the temporary tablespace

--Replace what is in red
ALTER TABLESPACE desired_temp_tbs ADD TEMPFILE '/oradata/tempfile1.dbf' SIZE 200M;

Solution 2:

You want to change the user default temporary tablespace to another one that you know for sure contains tempfiles:

--Replace what is in red
alter user username temporary tablespace desired_temp_tbs;

Solution 3:

You want to change the default temporary tablespace of the whole instance:

--Replace what is in red
alter database default temporary tablespace desired_temp_tbs;