Reason and Fix for ORA-25153: Temporary Tablespace is Empty

advertisements

_____________________________________________________________________________________________________________________

Normally you get this error when you create a new database and if you forget to add temporary tablespace to the database.

The second situation when you open a standby database in read-only mode and when you try to perform some sorting and ordering.

This can be resolved by adding temp files to the temp tablespace. First thing you have to check is whether the default tablespace is added to the database or not. Follow below mentioned steps to resolve this error.

select property_name, property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

SQL> /

PROPERTY_NAME                  PROPERTY_VALUE

------------------------------ ----------------------------------------

DEFAULT_TEMP_TABLESPACE        TEMP

If the default temporary tablespace is not assigned to the database, you have to assign one.

alter database default temporary tablespace temp;

Check whether the tempfiles(datafiles) added to the default temporary tablespaces.

SQL> select COUNT(*) from dba_Temp_files where tablespace_name ='TEMP';

  COUNT(*)

----------

         1

Normally when you create or clone a new database you have to add the tempfiles manually. For the standby database also when you try to open the database in read-only mode you have to add the tempfile to the temp tablespace.

Alter tablespace temp add tempfile ‘/data/oracle/prod/prod_temp01.dbf’ size 300M;

_____________________________________________________________________________________________________________________

Website Stats