ORA-25153 temporary tablespace is empty

In the morning, the boss said that the temporary tablespace is empty. Let me see it. I see the ORA-25153, it must be because the logical name of the temporary tablespace is still there, but the temporary file cannot be found.

Solution:

1. query the status of the temporary tablespace.

Select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

TEST_TBS ONLINE

TEST_TBS_TEMP ONLINE

It indicates that the tablespace is online.

2. query temporary tablespace files

Select tablespace_name, file_name from dba_temp_files;

Unselected row

The physical file cannot be found.

3. alter database default temporary tablespace temp;

After you run this command, the ORA-12907 is reported: tablespace TEMP is already the default temporary tablespace

This indicates that the temporary tablespace has lost the data file.

4. Add a temporary file to it.

Alter tablespace temp add tempfile '/opt/Oracle/oradata/temp02.dbf' size 512 m autoextend on;

5. Check whether the file is successfully created.

Select tablespace_name, file_name from dba_temp_files;