-
Dropping Tablespaces with missing datafiles
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
-
No way. U have to restore datafile from dump and then drop tablespace.
-
try
alter tablespace offline immediate
then drop
or
startup database in mount state
alter database datafile 'missing XXXXXX' offline drop
open database
drop tablespace
-
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.
-
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
Raghu
-
Hi Shestakov
Note I said immediate keyword, issueing offline immediate doesnt force checkpoint, i.e no need to write the datafiles
-
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.
-
I guess if we want use offline immediate the media recovery should be enabled right? . Correct me if I am wrong.
Raghu
-
Sorry you are asking to put, alter tablespace offline immediate, but not datafile. Media recovery need not be enabled.
Raghu
-
Re: Dropping Tablespaces with missing datafiles
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|