Solved

Having an issue with temp datafile

Posted on 2007-04-09
2
984 Views
Last Modified: 2008-01-09
SQL> select * from dba_temp_files;
ERROR:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: 'F:\ORACLE\ORADATA\IWDBT\TEMP01.DBF'

I just ran this query against my database. The path f:\oracle\oradata\iwdbt\ doesn't exist in my environment. How can I get this fixed? The correct path is f:\oracle\oradata\iwdb
0
Comment
Question by:ACSDBA
2 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 18877797
These 2 steps should work:

alter database tempfile 'F:\ORACLE\ORADATA\IWDBT\TEMP01.DBF' drop;
alter tablespace <temp_ts> add tempfile 'F:\ORACLE\ORADATA\IWDBF\TEMP01.DBF' size <size>;

You need to substitiute in the tablespace_name for <temp_ts> and the size of the temp file for <size>.
0
 
LVL 7

Expert Comment

by:Fayyaz
ID: 18880554
Hope below steps will help

1). Create another temporary tablespace
2). Make this newly created tablespace as default temporary tablespace
3). drop the old temporary tablespace including temp files.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now