Let's assume that your database is working on a Temporary Tablespace called Temp and contains one Tempfile called Temp02.dbf and let's assume that a malfunction occurred in this Datafile,

What will happen to the database?

The database will continue to work because the task of this Datafile is to store temporary data, and the best solution for this scenario is to create a new Datafile for the same Tablespace.

Here are the steps:-

1- Run the following query to determine the Temporary Tablespace that is currently working in the database.

SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%DEFAULT_TEMP%';

2- Add a new Tempfile to the Temp Tablespace.

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\oradata\TEMP03.DBF' SIZE 2048M;

3- Then we put the TEMP02.DBF DATAFILE in Offline mode, which is the Tempfile in which the error occurred.

ALTER DATABASE TEMPFILE 'D:\oradata\TEMP02.DBF' OFFLINE;

4- Then we delete it from the database.

ALTER DATABASE TEMPFILE 'D:\oradata\TEMP02.DBF' DROP;

5- Finally, you can inquire about the Temp File.

SELECT FILE_NAME FROM DBA_TEMP_FILES;