5 Replies Latest reply: Nov 19, 2012 8:13 AM by 974727 RSS

    Removing offline Datafile

    974727
      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
          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
            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
            • 4. Re: Removing offline Datafile
              Aman....
              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
                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.