I messed up and I hope there's some ancient DBA secret that I don't know about to fix it, hence the question before the community.
Here's the scenario....
I'm in my database cleaning up some tablespaces that are no longer required. Since Oracle won't remove the datafile(s) associated with the tablespace, once you remove the tablespace you have to go into the OS and rm the datafile, and that's exactly what I was doing. However... during my datafile removal I got a little to presumptuous and deleted a datafile that was still being used by an existing tablespace. Even though the datafile I deleted was going to get blown away, I did the removal backwards therefore causing the tablespace to freak out. Now, since the datafile is gone, Oracle won't let me blow the tablespace away because he can't get a lock on the datafile.
Does anyone know of a way to remedy this situation other than backing the controlfile to trace, editing the file to remove said tablespace and associated datafile(s), shutdown the database, blow away existing controlfiles, crank database back up with edited controlfile?
Oracle it's not just a database it's a lifestyle!
-------------- BTW....You need to get a girlfriend who's last name isn't .jpg
1.Recreate the user tablespace.If all the objects in the tablespace can be re-created (recent export is available, tables can be re-populated using scripts or through SQL*Loader)Then, offline drop the datafile, drop the tablespace with including contents option.
2.Restore file from backup and do a media recovery.Then u can drop the tablespace as per the normal procedure.