How to Resolve ORA-25153: Temporary Tablespace is Empty

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. Let's do a check.

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

There's nothing in TEMP2  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.

Verify -

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

About Default Temporary Tablespace

There're several points worth mentioning about temporary tablespaces:

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

But this can be changed by this statement.

SQL> alter database default temporary tablespace TEMP3;

Database altered.

2. 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 = '&TEMPTS';

USERNAME
----------
SYS
SYSTEM