Dropping Tablespaces with missing datafiles

[hummy]

Hi...

Does anyone know how i can drop a tablespace when the actual data file assocaited with that tablespace is no longer there. Somehow the data file has been deleted but the tablespace still exists. When i use the normal drop command says it can't access the data file.

I have seen this being done before when someone used SVRMGRL command and dropped the tablespace through there but can't wuite remeber how they did it any clues anyone?

Thanks

[Shestakov]

No way. U have to restore datafile from dump and then drop tablespace.

[pando]

try

alter tablespace offline immediate
then drop

or

startup database in mount state

alter database datafile 'missing XXXXXX' offline drop
open database
drop tablespace

[Shestakov]

As i know :
alter tablespace ... offline;
has to write to this tablespace :
1) all uncommited transactions
2) checkpoint number in datafile header block for each file in the tablespace.

in this situation this command can't solve this problem.

>>> alter database datafile 'missing XXXXXX' offline drop

may solve this problem in noachivelog mode only if no one oracle objects or espesially
part of object like tables, indexes ... no place in this datafile.but in any way in archivelog
mode oracle will ignore drop clause.

If i wrong - correct me.

[raghud]

Make sure no transactions are pending

Shutdown abort
startup nomount
alter database mount
alter database datafile ' ' offline drop
open database
drop tablespace ' '

should work as pando said

[pando]

Hi Shestakov

Note I said immediate keyword, issueing offline immediate doesnt force checkpoint, i.e no need to write the datafiles

[Shestakov]

Originally posted by pando

Note I said immediate keyword, issueing offline immediate doesnt force checkpoint, i.e no need to write the datafiles

Yes, I agree, with immediate Oracle will not force checkpoint for the tablespace.

[raghud]

I guess if we want use offline immediate the media recovery should be enabled right? . Correct me if I am wrong.

[raghud]

Sorry you are asking to put, alter tablespace offline immediate, but not datafile. Media recovery need not be enabled.

[abhaysk]

Originally posted by hummy
Hi...

Somehow the data file has been deleted but the tablespace still exists. When i use the normal drop command says it can't access the data file.

Thanks

This sounds almost impossible.....

When the tablespace is online it would have obiviously locked all its datafiles....& when file is locked at OS level you just cant delete/rename/move the file.

I wonder how on earth ( may it be any OS ) lets you delete file when locked?


Can any one tell how this can be done...Tried to simulate the problem but it seems almost impossible.

Abhay.