ORA-25153 Temporary Tablespace is Empty Error in Oracle

»»»ORA-25153 Temporary Tablespace is Empty Error in Oracle
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:

SQL> select property_name, property_value from database_properties;

The SQL will return the following results, look for DEFAULT_TEMP_TABLESPACE for the setting:

PROPERTY_NAMEPROPERTY_VALUE
————————————————————
DICT.BASE2
DEFAULT_TEMP_TABLESPACETEMP
DBTIMEZONE+01:00
NLS_NCHAR_CHARACTERSETAL16UTF16
GLOBAL_DB_NAMEARON.GENERALI.CH
EXPORT_VIEWS_VERSION8
NLS_LANGUAGEAMERICAN
NLS_TERRITORYAMERICA
NLS_CURRENCY$
NLS_ISO_CURRENCYAMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CHARACTERSETWE8ISO8859P1
NLS_CALENDARGREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGEAMERICAN
NLS_SORTBINARY
NLS_TIME_FORMATHH.MI.SSXFF AM
NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMATHH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY$
NLS_COMPBINARY
NLS_LENGTH_SEMANTICSBYTE
NLS_NCHAR_CONV_EXCPFALSE
NLS_RDBMS_VERSION9.2.0.6.0

If default temporary tablespace is wrong the alter it with the following command:

SQL> alter database default temporary tablespace temp;

To check default temporary tablespace for all users of the database:

SQL> select username, temporary_tablespace, account_status from dba_users;

The query will return the following result, check if all users TEMPORARY_TABLESPACE is set to correct settings:

USERNAMETEMPORARY_TABLESPACEACCOUNT_STATUS
——————————————————————————————–
SYSTEMPRYOPEN
SYSTEMTEMPOPEN
OUTLNTEMPOPEN
DBSNMPTEMPOPEN
DBMONITORTEMPOPEN
TESTTEMPOPEN
WMSYSTEMPEXPIRED & LOCKED

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;

By |2016-12-09T08:43:29+00:00December 9th, 2016|Categories: Databases|Tags: |7 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.