ORA-01187 and ORA-01110 errors Regarding Temp Files

dbtut

6 years ago

Assume that the default temp table space of the system is TMP.

ORA-01187: can not read from file because it failed verification tests
ORA-01110: data file n: ‘/oradata/orcl/temp01.dbf’

We create a new temp tablespace on the system.

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  '/oradata/orcl/temp2_01.dbf' SIZE 10G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

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.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

1	SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

We are dropping the old temp tablespace.

SQL> DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES;

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.

SQL> CREATE TEMPORARY TABLESPACE TMP TEMPFILE  '/oradata/orcl/temp01.dbf' SIZE 30G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

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.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

1	SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

The temporarily created temp2 tablespace is deleted.

SQL>DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

1	SQL>DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;