How to Resolve ORA-25153: Temporary Tablespace is Empty

ORA-25153

After activating a physical standby database, I found some SQL statements returned ORA-25153:

Description

ORA-25153: Temporary Tablespace is Empty

Cause

An attempt was made to use space in a temporary tablespace with no files.

Action

Add files to the tablespace using ADD TEMPFILE command.

This is because the temporary tablespaces contain no any temp files after the standby database was activated.

You might think the default temporary tablespace of this activated database is called TEMP. This is usually right, but you have to make sure about it.

Here we query DATABASE_PROPERTIES to know it.

SQL> column property_value format a20;

SQL> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';



PROPERTY_VALUE

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

TEMP2

As you can see, the default temporary tablespace is TEMP2 in this database. Let's see what temp files we have:

SQL> column tablespace_name format a12;

SQL> column file_name format a50;

SQL> select tb.tablespace_name, tf.file_name from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';



TABLESPACE_N FILE_NAME

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

TEMP2

SYSTEMP2

There's nothing in TEMP2 and SYSTEMP2 as expected.

Solution

You may just add some new temp files into these tablespaces for SQL statements that use them to sort their query results.

SQL> alter tablespace TEMP2 add tempfile '/oradata/ORCL/TEMP21.dbf' size 2789212160 reuse autoextend on next 10485760 maxsize unlimited;



Tablespace altered.



SQL> alter tablespace SYSTEMP2 add tempfile '/oradata/ORCL/SYSTEMP21.dbf' size 2789212160 reuse autoextend on next 10485760 maxsize unlimited;



Tablespace altered.

Now check it again.

SQL> select tb.tablespace_name, tf.file_name from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';



TABLESPACE_N FILE_NAME

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

TEMP2        /oradata/ORCL/TEMP21.dbf

SYSTEMP2     /oradata/ORCL/SYSTEMP21.dbf

Creating a new temporary tablespace instead of adding temp files to existing temporary tablespaces would not be helpful to solve ORA-25153.

SQL> create temporary tablespace TEMP tempfile '/oradata/ORCL/TEMP01.dbf' size 2789212160 reuse autoextend on next 10485760 maxsize unlimited extent management local uniform size 1048576;



Tablespace created.

About Default Temporary Tablespace

There're several points worth mentioning about temporary tablespaces:

There must be a default temporary tablespace in the database. Most users will use the default one for sorting operations.

But the default temporary tablespace of the database can be changed by this statement.

SQL> alter database default temporary tablespace TEMP3;



Database altered.

Some users will use their own temporary tablespace, not necessarily use the default one to run SQL.

SQL> column username format a10;

SQL> select username from dba_users where temporary_tablespace = 'SYSTEMP2';



USERNAME

----------

SYS

SYSTEM

DBSNMP

How to Resolve ORA-25153: Temporary Tablespace is Empty

ORA-25153

Description

Cause

Action

Solution

About Default Temporary Tablespace

2 thoughts on “How to Resolve ORA-25153: Temporary Tablespace is Empty”

Leave a Reply Cancel reply