Re: Cannot delete Tablespace without datafile

I have had to do this before, and I can't remember all of the syntax, but I believe this is what you will have to do.

First you need to drop the file from the database.

svrmgrl> alter database datafile '...' offline drop; The tablespace is now offline (and you should be able to open the database); svrmgrl> alter database open;
svrmgrl> drop tablespace ... including contents cascade constraints;

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
"Sudarshan Sampath" <ssampath_at_home.com> wrote in message
news:393668CE.C0FE7CE9_at_home.com...


> Is there a way to drop a Tablespace that has no datafile?

> I accidently deleted the datafile associated with the

> tablespace and I have no back up for it.

>

> I performed the following steps to fix the problem after shutting down

> the

> database and mounting it in exclusive mode:

> > alter database create datafile 'raid/oracle/app/abcd.dbf'

 Media recovery is needed after this step


> > alter database recover datafile 'raid/oracle/app/abcd.dbf'

> Now, it complains that with this error:

>

> ORA-00279: Change generated at 5/31/00 12:50pm needed for thread 1

> ORA-00289: Suggestion : '/raid/oracle/app/000014244.arc'

> ORA-00280: Change 1234567865 for thread 1 is in sequence #86831

>

> After this step, the database doesn't open at all. It complains bad

> header

> file.

>

> Is there a simple way to delete the datafile or tablespace when database

>

> is mounted only?

>

> Please advise.

>

> -Sudarshan

>