2 Replies Latest reply: Jan 20, 2010 6:48 AM by Pavan DBA RSS

    Drop tablespace Example in Oracle 9i

      I have used Oracle 9i (9.2) database in my P.C. Today my database(ORCL) not open due to corruption of
      EXAMPLE01.dbf datafile. Now I want to drop the tablespace EXAMPLE associated with EXAMPLE01.dbf
      file. My database now in mount stage, so i can not drop this tablespace using the following command.

      message comes 'DATABASE NOT OPEN'.

      how could i solve this problem.
        • 1. Re: Drop tablespace Example in Oracle 9i
          Anurag Tibrewal

          Is this your test database?
          I recommend you backup your database in a state what it is now before procedding to any of the mentioned solution here.

          And before procedding below do you like to post where it shows that datafiles got corrupted?

          You need to offline your datafile at the mount stage.
          alter database datafile 'datafile name' offline
          alter database open
          drop tablespace <tablespace name> drop including contents

          PS: All the above commands are irrecoverable if you do not have a backup.
          • 2. Re: Drop tablespace Example in Oracle 9i
            Pavan DBA
            dont you have a backup of this file.if so try to recovery that file (which is the best solution)
            otherwise you can take data offilne and then open the database. finally issue drop tablespace command