Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Assume that the default temp table space of the system is TMP.
ERROR:
ORA-01187: can not read from file because it failed verification tests
ORA-01110: data file n: ‘/oradata/orcl/temp01.dbf’
SOLUTION:
We create a new temp tablespace on the system.
1 | SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata/orcl/temp2_01.dbf' SIZE 10G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; |
We set the default temp tablespace of the system as newly created TEMP2.
1 | SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; |
We are dropping the old temp tablespace.
1 | SQL> DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES; |
We are re-creating the old default temp tablespace. This and the next step is not necessary. but you can apply it for use with default settings.
1 | SQL> CREATE TEMPORARY TABLESPACE TMP TEMPFILE '/oradata/orcl/temp01.dbf' SIZE 30G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; |
The default temp is changed back to TMP tablespace.
1 | SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP; |
The temporarily created temp2 tablespace is deleted.
1 | SQL>DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES; |
