Solved

database file removed

Posted on 2010-09-09
14
772 Views
Last Modified: 2013-12-19

the database file temp02.dbf was deleted.

How can you recreate the file? I have tried

DROP TABLESPACE  temp02 INCLUDING CONTENTS and receive the error:

ORA-00959: tablespace 'TEMP02' does not exist

Thanks for any suggestions
0
Comment
Question by:sstrange
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 168 total points
ID: 33640183
you have combined two different ideas and syntaxes.

if the datafile temp02.dbf was deleted, whatever tablespace was associated with it is now corrupt
try these queries...

select * from dba_data_files where file_name like '%temp02.dbf%'
select * from dba_temp_files where file_name like '%temp02.dbf%'

that tablespace doesn't have to be called TEMP02,  and based on your error, it isn't.

then you can decide if you want to drop that tablespace, or if you want to try to restore the file.
If the latter, you will need to have a backup to restore from.
0
 
LVL 6

Expert Comment

by:anumoses
ID: 33640195
create tablespace ts_something
  logging
  datafile '/dbf1/ts_sth.dbf'
  size 32m
  autoextend on
  next 32m maxsize 2048m
  extent management local;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 33640198
For a restore,  I'm assuming the file really is a "data" file  and not a "temp" file despite the name

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:sridharv9
ID: 33640402
If you are still looking for solution, post results from following commands.

select file_name, tablespace_name, online_status  from dba_data_files;

select file_name, tablespace_name, status  from dba_temp_files;
0
 

Author Comment

by:sstrange
ID: 33641941

So I ran the following:


SQL> select * from dba_temp_files where file_name like '%temp02.dbf%';
select * from dba_temp_files where file_name like '%temp02.dbf%'
              *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/export/oradata/ITDEV/temp02.dbf'

I do not have a backup to restore this from.

Ideas?
0
 
LVL 7

Assisted Solution

by:sumit2906
sumit2906 earned 166 total points
ID: 33643644
If it is only a temp file, you can try this:
ALTER DATABASE TEMPFILE '/export/oradata/ITDEV/temp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/export/oradata/ITDEV/temp02.dbf' DROP;

ALTER TABLESPACE <tablespace_name> ADD TEMPFILE '<path_and_file_name>' SIZE 500M;
0
 

Author Comment

by:sstrange
ID: 33664738

I am able to take the file offline, but when I go to drop the datafile the database locks up and nothing happens.

Ideas?
0
 
LVL 8

Assisted Solution

by:Rindbaek
Rindbaek earned 166 total points
ID: 33912049
if its a temp tablespace  you have dropped the file from, i suggestc that you create a new temp tablespace and alter the default temp tablespace for the system and users.
it goes something like this
CREATE TEMPORARY TABLESPACE temp01
      TEMPFILE '/oradata/temp01_01.dbf' SIZE 256M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16 M autoextend on next 128M max size 4G;

--Change default temporary tablespace (im not sure if you had this in oracle 8):
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;


--If you dont have to many users you can run this and copy paste the outcome of the select below  to change the temp tablespace of the users, you may need to modify the where clause....

set pagesize 999;
set linesize 200;
select 'ALTER USER ' || USERNAME || ' TEMPORARY TABLESPACE temp01;' from dba_users where TEMPORARY_TABLESPACE='TEMP';

When thats done and everything works drop the old temp tablespace
 
0
 
LVL 8

Expert Comment

by:Rindbaek
ID: 34292154
Hi angellll

since no one else wants to comments on this i will do it rsiking my neck.

sdstuber assist with 33640183 to determine the nature of the datafile.
Summit had a go but unfortunately  it didn't work. If sstrange returns with news that it was solved based on 33643644 credits shoud go there else i belive i have provided a vaild solution in 33912049.

/Rindbaek

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34292247
I concur,  first determine if file is datafile or tempfile,  then if datafile then restore, if tempfile then recreate

so split with

http:#33640183
http:#33643644
http:#33912049
0
 
LVL 8

Expert Comment

by:Rindbaek
ID: 34292285
fair enough ;-)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34440955
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

685 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