4 Replies Latest reply: Jan 13, 2009 11:14 PM by 247514 RSS

    Drop datafile from a offline tablespace

      We are running oracle 9.2 on windows.

      Out of the 3 datafiles in a non-system tablespace A, one datafile A-DF1 was accidently droped from the file system by our DBA.

      The other two datafiles were brought online by using the ALTER DATABASE RECOVER command by bringning the database in mount phase.

      Now we want to remove that datafile A-DF1 from the tablespace and bring that tablespace online.

      The database is not in archive mode and no cold backup was present.

      The solution that we want to try is:

      1-drop the tablespace A without dropping the datafiles
      2-create a new tablespace and reuse those datafiles
      3-assign this new tablespace to the affected user

      Would this work? Do you have any other safe solution for this problem?

        • 1. Re: Drop datafile from a offline tablespace
          I don't see any chance for you.
          Reuse does not mean, that the contents of the datafiles will be available. It's just a syntactic means to tell the DB, that the DB file intentionally shall be overwritten. Without using reuse, the DB would complain about an existing file.
          • 2. Re: Drop datafile from a offline tablespace

            Your solution is a wrong solution.

            1. I guess, that the tablespace A won't be a empty tablespace.As, the objects are present in the tablespace you can't drop the tablespace without dropping its contents.You will have to use *DROP TABLESPACE <tbs_name> INCLUDING CONTENTS; -- This will drop all the contents.

            2. Do, you have a export backup, or any kind of backup.As the database is in no archivelog mode, you won't be able to do complete recovery.

            3. Take the datafile A-DF1 OFFLINE and keep the tablespace online. -- alter database datafile 'datafile_name' offline;

            If you don't have any kind of backup, the data is LOST.

            • 3. Re: Drop datafile from a offline tablespace
              Is that possbile to take the backup of the control file in text and eliminate the reference of this lost datafile in that tablespace > restart the database with this new controlfile?
              • 4. Re: Drop datafile from a offline tablespace
                No, it's not possible. Without control file reference Oracle will report a file named MISSINGxxx in the list.

                Oracle just didn't provide a way to drop datafile without dropping the tablespace in 9i