<aside> 💡 ORA-01187: cannot read from file because it failed verification tests

</aside>

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
*
ERROR at line 1:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: 'E:\ORACLE\ORADATA\IC\TEMP01.DBF'

SOLUTION:

Cách 1

1. Check the table space name

select * from v$tablespace;

2. Add the Tempfile with new name into temp tablespace

alter tablespace TEMP add tempfile 'E:\ORACLE\ORADATA\IC\TEMP_01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M;

3. Drop the tempfile from the Database causing problem:

alter database tempfile 'E:\ORACLE\ORADATA\IC\TEMP01.DBF' drop;

4. Delete the temp file from file system.

Cách 2

1. Check default tablespace

select * from database_properties where property_name like 'DEFAULT%TABLESPACE';

2. Steps to clear the temporary tablespace:
Note: Used only when load on server is very less because it may cause the server in hang state if large no of transaction is going in the database.
Check the usage of Temporary tablespace;

select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks,
a.sid, a.serial#, a.username, a.osuser, a.status
from v$session a, v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;

3. Find the location of temp files:

select * from v$tempfiles;

4. Create another Temporary table space in database;

Create temporary tablespace TEMP2 tempfile 'E:\ORACLE\ORADATA\IC\TEMP01.DBF'  size 500m;

5. Make the second temporary tablespace as default.

Alter database default temporary tablespace temp2;

6. Drop the old temporary tablespace.

Drop tablespace temp;

7. Need to add more temp files to second temporary files.

Alter database tempfile 'E:\ORACLE\ORADATA\IC\TEMP01.DBF' autoextend on next 100m;

8. Check the tablespace view to verify:

select * from v$tablespace;
select property_name, property_value from database_properties;

9. Manually remove the temp files from oradata folder of old temp tablespace. Do it carefully.