This content has been marked as final. Show 5 replies
971724 wrote:with *MUCH* difficulty!
(Total noob please help)
Regarding Oracle 11g
A co-worker added a datafile to a tablespace, however he didn't like what he had named it, so he placed that datafile offline, and created another.
So now, I have a tablespace named "Data" with three datafiles associated with it:
I tried logging in to sqlplus sys as sysdba and running.
"Alter tablespace data drop datafile "c:\location\data02";
but that didn't work, gives me the error that the data file must be online in order to drop it.
I tried "Place online" using Oracle enterprise manager but it gives me an error.
ORA-01113: file 32 needs media recovery ORA-01110: data file 32: 'c:\location\DATA02' .
How do I remove the file from the system?
create new tablespace
move all objects from DATA tablespace to new TS
drop data tablespace including datafiles.
Resgtrictions for dropping datafile:1 person found this helpful
If a datafile is not empty, it cannot be dropped.
If a datafile in a locally managed tablespace is offline, it cannot be dropped.
Make the datafile online, recover it (Is your database in archive log mode?)
and Then drop
You would need to issue the command recover datafile if you are in the archive log mode. If you are not, its better that you move the content from the first to_be_dropped tablespace to a new tablespace and then drop the old tablespace itself. Not only this would be an easy thing to do but also, it would give a little defrag sort of thing to you as well.1 person found this helpful
Thanks for the quick response.
It is a little odd that oracle can't remove a file that is already removed.... doesn't really make sense to me why they did that....
However, it is not important to remove a file that is 0Kb in size, and not being used by the database. So I will just make a note that it isn't used, and ignore that it is there.