how do i delete data files

[bookouri]

Im creating a new database, unfortunately when I named one of my datafiles I didnt specify the path and oracle created it in the default path, so now i have one datafile in a different directory than my other files and I want to delete the file and recreate it in the right location. Oracle wont let me delete it. I get an error saying Offline-immediate disallowed unless media recovery enabled. What do I have to do to delete this data file?

any suggestions would be appreciated

 

[alexhu]

shut the database down

copy the datafile to the correct destination

startup the database - startup nomount

alter database rename file 'filespec' to 'filespec'

alter database open

Alex

 

[bookouri]

thanks, that worked for me... one question though, when i renamed the data file, Oracle defaulted to the wrong tablespace, is there a way to rename and include a specific tablespace?

thanks again

 

[rcurva]

You can also use,

ALTER TABLESPACE tsname
RENAME DATAFILE 'filespec1' TO 'filespec2';

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[alexhu]

What do you mean 'defaulted to the wrong tablespace' ??

Oracle will rename the file you selected - it will not move it to another tablespace

Alex

 

[bookouri]

I have several tablespaces.

D:\DATABASE\OMS\OMS01.ORA OMS 838860800
D:\DATABASE\OMS\OMSIND01.ORA OMS_INDX 838860800
D:\DATABASE\OMS\CTAGDAT1.ORA CTAG_DATA 04857600
D:\DATABASE\OMS\OMS02.ORA CTAG_DATA 838860800

The OMS01.ora and oms02.ora should be both OMS. But when I did the alter database and renamed the oms02.ora it "defaulted" to ctag_data as far as I can tell. The renaming script. I tried several versions of the alter tablespace to fix it, but havnt got the syntax right yet I guess.

 

[alexhu]

You're missing the point here

The command will rename the data file only.

You cannot move a data file between tablespaces.

If OMS does not have emough space then add a datafile.

If CTAG_DATA has too much space then either:

export all the data in the tablespace
drop and recrerate the tablespace
import the data
OR
shrink the unwanted file to a small size (alter database datafile 'file' resize 1m

Alex

 

[bookouri]

thanks I think Ive finally got a handle on this.... finally managed to get everything in its place and sized correctly...