DB files

Gals/girls.
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?

Thanks!

That's a classic "media failure", so you could restore the file . . . though the control file method could be quicker.

In 9i you can drop a ts INCLUDING CONTENTS AND DATAFILES

Of course, some OS don't let you delete a file that's in use . . . .

Quote:

---

Originally posted by OracleDoc
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.

---

r u sure ??

Code:

---

sys@DELDB.WORLD> create tablespace del_me datafile '/perfdb2/oradata/DELDB/del_me_01.dbf' size 10M;

sys@DELDB.WORLD> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/perfdb2/oradata/DELDB/system01.dbf
/perfdb2/oradata/DELDB/undotbs01.dbf
/perfdb2/oradata/DELDB/indx01.dbf
/perfdb2/oradata/DELDB/tools01.dbf
/perfdb2/oradata/DELDB/users01.dbf
/perfdb2/oradata/DELDB/del_me_01.dbf

6 rows selected.

oracle(DELDB):/perfdb2/oradata/DELDB>
> ls
del_me_01.dbf  redo01.log    redo03.log    temp01.dbf    undotbs01.dbf
indx01.dbf    redo02.log    system01.dbf  tools01.dbf    users01.dbf
oracle(DELDB):/perfdb2/oradata/DELDB>
> mv del_me_01.dbf del_me_01.dbf.old
oracle(DELDB):/perfdb2/oradata/DELDB>
> ls
del_me_01.dbf.old  redo01.log        redo03.log        temp01.dbf        undotbs01.dbf
indx01.dbf        redo02.log        system01.dbf      tools01.dbf        users01.dbf

sys@DELDB.WORLD> drop tablespace del_me;

Tablespace dropped.

sys@DELDB.WORLD> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/perfdb2/oradata/DELDB/system01.dbf
/perfdb2/oradata/DELDB/undotbs01.dbf
/perfdb2/oradata/DELDB/indx01.dbf
/perfdb2/oradata/DELDB/tools01.dbf
/perfdb2/oradata/DELDB/users01.dbf

sys@DELDB.WORLD>

---

This was on Unix... Oracle9i

What i will do in this case is

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.

Another useful link is

http://metalink.oracle.com/metalink/...atabase_id=NOT

LOL good Lord akhadar that's a lot of work...

Ok, I forgot to mention it's 8i and on Sun. Adewri, I did exactly that and it still wouldn't do drop it. I'm think'n it's because I'm on 8i.

alter database datafile 'XXX' offline drop;

drop tablespace xxx;