ORA-01187: cannot read from file because it failed verification tests
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: ‘E:\ORACLE\ORADATA\IC\TEMP01.DBF’
Check the following error occurred during time of dba_temp_files view querySolution in two ways:
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'
First way to add and remove only the tempfile. First add new tempfile then remove old.
Second way to remove complete temp tablespace and created new tablespace.
First Way
1. Check the table space name2. Add the Tempfile with new name into temp tablespace
select * from v$tablespace;3. Drop the tempfile from the Database causing problem:
alter tablespace TEMP add tempfile 'E:\ORACLE\ORADATA\IC\TEMP_01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M;4. Delete the temp file from file system.
alter database tempfile 'E:\ORACLE\ORADATA\IC\TEMP01.DBF' drop;
Second Way
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.