ORA-25153: Temporary Tablespace is Empty

ORA-39083: Object type INDEX failed to create with error:
ORA-25153: Temporary Tablespace is Empty

Summary:

when try to execute a simple sql, a error jump out and tell me “ORA-25153: Temporary Tablespace is Empty”.
Right away, I search temp tablepsace and find it. 


SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         1       1910026462 20-AUG-14         60          1 ONLINE  READ WRITE
1.0737E+10    1310720   1.0737E+10       8192
/oracledb/oradata/stlbas/temp01.dbf


SQL>

Now add a temp datafile and change the default temporary tablespace if necessary

alter tablespace temp add tempfile '/oracledb/oradata/stlbas/temp02.dbf' size 1024M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Now try to execute the query with order by clause