Tag Archives: Temp

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

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 query

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 in two ways:
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 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 REUSE;

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.
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.


Temporary TEMP location Change in windows for avoid space error FTP process

Temporary Change TEMP Location in Windows platform for avoid insufficient Space error in C: Drive During FTP Process

In windows, when we do any operation like download, ftp etc, Due to insufficient space in C: Drive the we got error for insufficient space.

Suppose i created one batch file as ftp job which will transfer the backup of 16 GB files from Production Server to Test Environment daily. It’s start failed due to insufficient space but on D: drive on windows have sufficient space around 100 GB.

Error occurred due to environment variable TEMP is set to default C:\temp directory which is pointing to C: Drive which is not having sufficient space to do the operation then it will generate an error of insufficient space for operation.

You can change the temp space location temporary for any task like ftp if d drive have enough space

set temp= D:\
set tmp=D:\

You can set this parameter temporary before the operation start for session level or used it in batch files for batch operation.

Temporary Tablespace Usage by SQL Queries

Temporary Tablespace Usage by SQL Queries

Temporary tablespace is used for Sorting the data of table by sort segments.

Find out the SQL Statement using Temporary table space as operation

During running the SQL statements having order by clause, having join in between then oracle use the temporary table space for sorting purpose, if table size is larger than the memory allocated size then oracle use temporary space for getting result. It is kind of physical read from the hard disk. So, its better to avoid this kind of situation by tuning SQL queries

Following query give you the sql statement and session id which is using Temporary tablespace in Oracle:

select se.sid, se.username,su.blocks * ts.block_size / 1024 / 1024 mb_used, su.tablespace,su.sqladdr address, sq.hash_value, sq.sql_text from v$sort_usage su, v$session se, v$sqlarea sq, dba_tablespaces ts where su.session_addr = se.saddr and su.sqladdr = sq.address (+) and su.tablespace = ts.tablespace_name;