Oracle ORA-25153

  Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.

When executing SQL query, the following Oracle error may appears:

ORA-25153: Temporary Tablespace is Empty

The cause for the ORA-25153 error is because attempt was made to use space in a temporary tablespace with no files (no datafiles defined).

To solve the problem, the solution is just by adding files (datafiles) to the TEMP tablespace by using ADD TEMPFILE command, or by using “Add Datafiles” in Oracle Enterprise Manager.

If you check and found that TEMP tablespace already has data files, check the default temporary tablespace for all users and your database and set the default temporary tablespace to a valid temporarary tablespace.

To check the default temporary tablespace of the database:
To check default temporary tablespace for all users of the database:
SQL> select username, temporary_tablespace, account_status from dba_users;

If default temporary tablespace is wrong the alter it with the following command:
SQL> alter database default temporary tablespace temp;

If wrong temporary tablespace is found, alter it with the correct tablespace name (for example, sys) with the following SQL:
SQL> alter user sys temporary tablespace temp;

Alternatively, recreate or add a datafile to your temporary tablespace and change the default temporary tablespace for your database;
SQL> drop tablespace temp including contents and datafiles;

SQL> create temporary tablespace temp tempfile ‘/db/temp01.dbf’ size 100m autoextend off extent management local uniform size 1m;

SQL> alter database default temporary tablespace temp;