Oracle 12cR1. After getting this ORA-01187 :

11-04 17:55 MY12cDB SYS AS SYSDBA> select * from dba_temp_files;

select * from dba_temp_files

              *

ERROR at line 1:

ORA-01187: cannot read from file  because it failed verification tests

ORA-01110: data file 257: '/data/MY12cDB/ora3/DBL13/ETMPT01_01.dbf'

 

I decided to re-create the TEMPORARY TABLESPACE that this file belonged to, which happened to be the DEFAULT TEMPORARY TABLESPACE, named TEMP, of my 12cR1 database.

 

Here are the steps taken:

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/data/MY12cDB/ora3/DBL13/TEMP2.dbf' SIZE 10M AUTOEXTEND ON  MAXSIZE 2048M;

alter database default temporary tablespace TEMP2;

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

drop tablespace temp including contents and datafiles;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/data/MY12cDB/ora3/DBL13/ETMPT01_01.dbf' SIZE 100M AUTOEXTEND ON  MAXSIZE 2048M;

alter database default temporary tablespace TEMP;

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

drop tablespace temp2 including contents and datafiles;

 

 

And here is the SQL*PLUS output:

 

11-04 17:55 MY12cDB SYS AS SYSDBA> select * from dba_temp_files;

select * from dba_temp_files

              *

ERROR at line 1:

ORA-01187: cannot read from file  because it failed verification tests

ORA-01110: data file 257: '/data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf'

 

 

11-04 18:02 MY12cDB SYS AS SYSDBA> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/data/MY12cDB/ora3/MY12cDB/TEMP2.dbf' SIZE 10M AUTOEXTEND ON  MAXSIZE 2048M;

 

Tablespace created.

 

 

11-04 18:04 MY12cDB SYS AS SYSDBA> alter database default temporary tablespace TEMP2;

 

Database altered.

 

 

11-04 18:04 MY12cDB SYS AS SYSDBA> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_VALUE

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

TEMP2

 

 

11-04 18:04 MY12cDB SYS AS SYSDBA> col PROPERTY_VALUE for a10

11-04 18:04 MY12cDB SYS AS SYSDBA> drop tablespace temp including contents and datafiles;

 

Tablespace dropped.

 

 

11-04 18:05 MY12cDB SYS AS SYSDBA> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf' SIZE 100M AUTOEXTEND ON  MAXSIZE 2048M;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf' SIZE 100M AUTOEXTEND ON  MAXSIZE 2048M

*

ERROR at line 1:

ORA-01119: error in creating database file '/data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf'

ORA-27038: created file already exists

Additional information: 1

 

 

 

 

11-04 18:05 MY12cDB SYS AS SYSDBA> !ls -lh /data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf

-rw-rw---- 1 oracle oracle 257M Jan 26 10:05 /data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf

 

 

11-04 18:05 MY12cDB SYS AS SYSDBA> !rm /data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf

 

 

11-04 18:07 MY12cDB SYS AS SYSDBA> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf' SIZE 100M AUTOEXTEND ON  MAXSIZE 2048M;

 

Tablespace created.

 

 

11-04 18:07 MY12cDB SYS AS SYSDBA> alter database default temporary tablespace TEMP;

 

Database altered.

 

 

11-04 18:07 MY12cDB SYS AS SYSDBA> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_V

----------

TEMP

 

 

11-04 18:07 MY12cDB SYS AS SYSDBA> drop tablespace temp2 including contents and datafiles;

 

Tablespace dropped.

 

 

11-04 18:08 MY12cDB SYS AS SYSDBA>  select * from dba_temp_files;

 

FILE_NAME

   FILE_ID TABLESPACE_NAME         BYTES     BLOCKS STATUS       RELATIVE_FNO AUTOEXTENSIB   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

/data/MY12cDB/ora3/MY12cDB/ETMPT01_01.dbf

         1 TEMP                104857600      12800 ONLINE                  1 YES          2147483648     262144            1  103809024       12672

 

To summarize:

  1. Create a new temporary tablespace TEMP2
  2. Make TEMP2 the default temporary tablespace
  3. Drop your original tablespace TEMP
  4. Create a new temporary tablespace TEMP
  5. Make TEMP the default temporary tablespace
  6. Drop TEMP2

And your default temporary tablespace is TEMP, as required, and has a brand new temp file that will not cause ORA-01187.