dropping a tablespace and datafiles

[stevenriz]

I wanted to drop a tablespace and it's associated datafiles. So I issued

DROP TABLESPACE DATATEST
INCLUDING CONTENTS
CASCADE CONSTRAINTS;

and thought I could remove the datafiles in the OS now the db doesn't startup.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 260059120 bytes
Fixed Size 69616 bytes
Variable Size 92037120 bytes
Database Buffers 163840000 bytes
Redo Buffers 4112384 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/oradata/indxtest.dbf'
SVRMGR> DROP TABLESPACE datatest INCLUDING CONTENTS;
DROP TABLESPACE datatest INCLUDING CONTENTS
*
ORA-01109: database not open
SVRMGR>

I don't care about these files, I am going to recreate the tablespace. How do I get this db to start back up normally so I can recreate the tablespace again? If it is impossible now, I will just reinstall.

thanks!

 

[SantaMufasa]

You should have been fine with your,

"DROP TABLESPACE DATATEST INCLUDING CONTENTS;" command. Did it come back with a "Tablespace dropped." response? (I'm not familiar with the "...cascade constraints" option on a "DROP TABLESPACE..." command.)

As a fallback contingency, do the following:

shutdown abort
startup mount
alter database drop datafile '/oradata/indxtest.dbf';
alter database open;

Let us know if that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[stevenriz]

Hi Dave, yes it did come back with tablespace dropped so I wasn't sure what actually happened and why it was still looking for those files... I will try this and get back to you. Thanks!!
Steve

 

[hoinz]

Steve,

I wonder what your naming conventions are.
I understand you dropped a tablespace DATATEST, and you deleted a few files, one of them called '/oradata/indxtest.dbf'.
But was this file part of your DATATEST tablespace?
Or maybe of another tablespace, INDXTEST?
(How else would you separate data and indices?)

Not sure if 'CASCADE CONSTRAINTS' would work this way ...

 

[stevenriz]

God, I'm so @#$@# stupid. There are index and data tablespaces and I neglected to drop the index tablespace. I dropped them with a similar statement Mufasa,

alter database lawtest datafile '/oradata/indxtest.dbf' offline DROP;

but hoinz, you are correct. We've been straight out day and night getting the system that crashed back online, now I am rebuilding the test system with current data on all different file systems with toothpicks in my eyes. Thanks for the help, seriously. I am all set now.