I have been DBA-ing for a while now, and I today I used a “new” command which I have never used in the previous 20+ years I have worked with Oracle: DROP DATABASE. It’s amazing what you miss sometimes!

So, what does it do? As the name implies, it drops the database. That is, it deletes all of your database files. This is significantly safer than using traditional Operating System methods (assuming you even have access and are not using ASM), and for added safety you have to be running the database in RESTRICT MOUNT mode.

What a lovely command. It goes to all the trouble of locating and removing those troublesome files that were put into the wrong place by another DBA that you would have missed if cleaning up manually.

It certainly made the pre-production clone script (cloning from the Physical Standby) that I have just written quite a bit easier to code.

Does anybody know if there is any other reason for running the database in RESTRICT MOUNT mode, or was it designed just for this command?

EDIT: I was just informed via Twitter (@chandlerdba) by @pfierens that this also takes out your SPFILE. If you are using an SPFILE (you should be!) and want to keep your db parameters for a db rebuild, I would recommend creating a pfile from the spfile first…

Has anyone ever done an RMAN “drop database including backups” ?   You REALLY need to be sure you don’t want it back to run that one!


4 Responses to DROP DATABASE command

  1. mdinh says:

    Used it a few times but only in DEV environment – short blog here – http://mdinh.wordpress.com/2007/09/19/drop-database/


  2. mwidlake says:

    Oddly enough, I had a discussion with some DBA colleagues just this week about something similar. I think it is easier, safer and cleaner to drop a database than to drop a set of schemas from a database you want to otherwise keep intact. They felt it was no problem to clean up all the bits for one “logical database” in an instance containing others.

    Add into the mix that I have not as yet seen any sign of object, tablespace, schema or other naming standards at this client site…


    • Every time I go to a new site, inside it’s like Munch’s The Scream.

      It’s the same everywhere – they do some really good impressive stuff and then mess it all up with a lack of something really really important. The worst sites then stop you fixing it using politics and bureaucracy and a large amount of fear (of change).

      That said, some sites do pretty much everything wrong and you are left in wonder at the awesome capabilities of operating systems and Oracle to work well despite the best efforts of idiots to mess the whole thing up.

      (there’s no need to go to the other 20% of sites – they are getting it right!)


      • mwidlake says:

        I think you just summed up 97.3% of the IT industry. I’m going home and I ain’t coming out again…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.