This discussion is archived
5 Replies Latest reply: Nov 19, 2012 6:13 AM by 974727 RSS

Removing offline Datafile

974727 Newbie
Currently Being Moderated
Hello,

(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:
"Data01.dbf" online,
"Data02.dbf", online
"Data02", offline

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?
  • 1. Re: Removing offline Datafile
    sb92075 Guru
    Currently Being Moderated
    971724 wrote:
    Hello,

    (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:
    "Data01.dbf" online,
    "Data02.dbf", online
    "Data02", offline

    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?
    with *MUCH* difficulty!

    create new tablespace
    move all objects from DATA tablespace to new TS
    drop data tablespace including datafiles.
  • 2. Re: Removing offline Datafile
    vlethakula Expert
    Currently Being Moderated
    Resgtrictions for dropping datafile:

    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
  • 3. Re: Removing offline Datafile
    user296828 Expert
    Currently Being Moderated
  • 4. Re: Removing offline Datafile
    Aman.... Oracle ACE
    Currently Being Moderated
    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.

    Aman....
  • 5. Re: Removing offline Datafile
    974727 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points