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_NAME | PROPERTY_VALUE |
| —————————— | —————————— |
| DICT.BASE | 2 |
| DEFAULT_TEMP_TABLESPACE | TEMP |
| DBTIMEZONE | +01:00 |
| NLS_NCHAR_CHARACTERSET | AL16UTF16 |
| GLOBAL_DB_NAME | ARON.GENERALI.CH |
| EXPORT_VIEWS_VERSION | 8 |
| NLS_LANGUAGE | AMERICAN |
| NLS_TERRITORY | AMERICA |
| NLS_CURRENCY | $ |
| NLS_ISO_CURRENCY | AMERICA |
| NLS_NUMERIC_CHARACTERS | ., |
| NLS_CHARACTERSET | WE8ISO8859P1 |
| NLS_CALENDAR | GREGORIAN |
| NLS_DATE_FORMAT | DD-MON-RR |
| NLS_DATE_LANGUAGE | AMERICAN |
| NLS_SORT | BINARY |
| NLS_TIME_FORMAT | HH.MI.SSXFF AM |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM |
| NLS_TIME_TZ_FORMAT | HH.MI.SSXFF AM TZR |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_DUAL_CURRENCY | $ |
| NLS_COMP | BINARY |
| NLS_LENGTH_SEMANTICS | BYTE |
| NLS_NCHAR_CONV_EXCP | FALSE |
| NLS_RDBMS_VERSION | 9.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:
| USERNAME | TEMPORARY_TABLESPACE | ACCOUNT_STATUS |
| —————————— | —————————— | ——————————– |
| SYS | TEMPRY | OPEN |
| SYSTEM | TEMP | OPEN |
| OUTLN | TEMP | OPEN |
| DBSNMP | TEMP | OPEN |
| DBMONITOR | TEMP | OPEN |
| TEST | TEMP | OPEN |
| WMSYS | TEMP | EXPIRED & 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;
reference site:-https://techjourney.net/ora-25153-temporary-tablespace-is-empty-error-in-oracle/
