ORA-25153: “Temporary Tablespace is Empty” although temporary tablespace and tempfile is available

According to oracle docs, the cause of the oracle error is to attempt was made to use space in a temporary tablespace with no files(datafile). And based on that action suggested is to add tempfile to the temporary tablespace with ADD TEMPFILE command.

In my case, Temporary tablespace already exists with sufficient free space into it. So I verified DEFAULT temporary tablespace, It was set to invalid temporary tablespace name.

The issue had resolved by setting default temporary tablespace to valid one.

Following query will help you find out default temporary tablespace:

SQL> select property_name, property_value from database_properties where

property_name='DEFAULT_TEMP_TABLESPACE';

Following query will help you to set default temporary tablespace:

SQL> alter database default temporary tablespace NEWTEMP;

Thanks, Comments are highly appreciated.
Stay Tune. 🙂

Leave a Reply

%d bloggers like this: