Prematurely rm'd datafile, now cannot DROP TABLESPACE 2

[DeepDiverMom]

Gurus,

I have been doing maintenance on serveral database instances, getting rid of unwanted tablespaces (and rm'ing the tablespace datafiles).

I thought that I had already done a "DROP TABLESPACE..." on an empty tablespace, so I did an "rm <datafile>". I then discovered that I had not yet done a "DROP TABLESPACE...". Then when I tried to "DROP TABLESPACE...", I obviously received an error telling me that Oracle could not open the datafile, to allow the dropping of the tablespace.

What are my options for successfully doing a "DROP TABLESPACE..."?

Becca

 

[KenCunningham]

Becca, have you tried closing the database then mounting but not opening it. Then try the drop. Not sure if this will work, but no doubt others will be along with something that will!

 

[gunjan14]

Hi Becca,

Try (from SQLPlus) without closing the database..

SQL> alter database datafile <file name> offline drop;

and then drop the tablespace. Let us know if this works.

Gunjan

 

[KenCunningham]

Gunjan - maybe that's what I was thinking of, couldn't quite recall the syntax.

 

[DeepDiverMom]

Woo-hoo!!! Gunjan, your suggestion worked like a charm! Have a star.

And Ken, something in my mental archives tells me that your suggestion also would have worked. But I tried Gunjan's method first, and I didn't have time (or end-user concurrence) to try yours. But since I trust my instincts and my memory, I'm presuming your method would work, thus I'm also giving you a star for your efforts. (I just don't want a followup Tek-Tipster to think that I actually tested your method out.)

Thanks, guys.