ORA-25153: Temporary Tablespace is Empty

Problem Description
Whenever you try to access temporary tablespace it fails with error ORA-25153. Suppose I want to get the user creation script of user ARJU but it fails with following message.

SQL> SELECT DBMS_METADATA.GET_DDL('USER','ARJU') from dual;
ERROR:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_METADATA", line 2729
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

Cause of The Problem
The user assigned to a temporary tablespace does not exist in the database. For example the user is associated to a temporary tablespace and later someone has dropped it.

Solution of The Problem
We can see whether any temporary tablespace available in database by,
SQL> select count(*) from dba_temp_files;
COUNT(*)
----------
1

Now we see there is temporary tablespace existed in the database. But possibly user ARJU is not assigned to any temporary tablespace. We can check the user's ARJU temporary tablespace by,
SQL> select temporary_tablespace from dba_users where username='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP

But in database the available temporary tablespaces are,
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
TEMP3

So TEMP which is assigned to ARJU does not existed in database and hence we got ORA-25153: Temporary Tablespace is Empty.

Now change the temporary tablespace of user ARJU.
SQL> alter user arju temporary tablespace temp3;
User altered.

Any now above query and it is ok.
SQL> SELECT DBMS_METADATA.GET_DDL('USER','ARJU') from dual;
DBMS_METADATA.GET_DDL('USER','ARJU')
--------------------------------------------------------------------------------

CREATE USER "ARJU" IDENTIFIED BY VALUES '55E19EAC6BA480EA'
DEFAULT TABLESPACE "USER_TBS"
TEMPORARY TABLESPACE "TEMP3"

Related Documents
Free space in Temporary Tablespace
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.