Printable View
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
Hi Shestakov
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.Quote:
Originally posted by pando
Note I said immediate keyword, issueing offline immediate doesnt force checkpoint, i.e no need to write the datafiles
I guess if we want use offline immediate the media recovery should be enabled right? . Correct me if I am wrong.
Sorry you are asking to put, alter tablespace offline immediate, but not datafile. Media recovery need not be enabled.
This sounds almost impossible.....Quote:
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
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.
U R on windows OS I guess :p :p :p :D :D :DQuote:
Originally posted by abhaysk
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.
wass big deal...and y r u so amused?Quote:
Originally posted by nagarjuna
U R on windows OS I guess :p :p :p :D :D :D
making all such faces?????
i dont see any point....
Abhay.
:p :p :p :D :D :DQuote:
Originally posted by abhaysk
wass big deal...and y r u so amused?
making all such faces?????
i dont see any point....
Abhay.
:p :p :p :D :D :DQuote:
Originally posted by abhaysk
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
If you dont know, learn it.. Dont make sentences. Good DBAs should have patience. You dont have a bit of it. In UNIX, Oracle does not lock the files at OS level.
i do have patientence.....Quote:
Originally posted by nagarjuna
:p :p :p :D :D :D
:p :p :p :D :D :D
If you dont know, learn it.. Dont make sentences. Good DBAs should have patience. You dont have a bit of it. In UNIX, Oracle does not lock the files at OS level.
now unix dosent lock the file even though its in use??
thats rediculous....i dont have knowledge in unix but wass the point in not locking a file even if it is in use....
If this is the case then I would better reley on Windows...
Abhay.
well unix does not lock files, that's not new
windows does and as some of us might noticed there are some threads saying they cannot start database in windowz after taking backup because the datafile is locked by the backup software, this for example does not happen in unix.
it can be bad or good but I still prefer *NIX, *BSD platforms to run Oracle
lol.. this Abhay is SQL server kinda DBA I guess ;)
UNIX is for gurus. We need proper plan before implementing it. Then, it's worth, it lasts longer. We have more options regarding security in unix. Windows does not give much choices. It does everything itself. ;) Windows is good for home made, home use database. Not for bigger ones..
Don't take it so easily. In my environment we have millions cost servers based n Windows.May be they have different behaviour to Other OS. A DBA is a DBA don't make a joke of it u must be precise with your words.
24*7Quote:
Originally posted by akhadar
Don't take it so easily. In my environment we have millions cost servers based n Windows.May be they have different behaviour to Other OS. A DBA is a DBA don't make a joke of it u must be precise with your words.
Is it possible with windows based databases????? Dont you reboot your windows servers once in every week?????
Can you imagine an OLTP of 300GB on any windows?????
---------------------
24*7
Is it possible with windows based databases?????
----------------------
Sure why not?.
-------------------------
Dont you reboot your windows servers once in every week?????
-------------------------
Why ?.Since iam in Clustered environment.May be u didnot travelled world wide.
dont thing that iam a supporter of Bill gates.
Stop accusing others and other platforms.Since it is a professional site be professional.
-------
Great minds discuss ideas.Average minds discuss events.Small minds discuss people.
regards
akhadar
Okay.. I beg your pardon. Mistake regretted.Quote:
Originally posted by akhadar
---------------------
24*7
Is it possible with windows based databases?????
----------------------
Sure why not?.
-------------------------
Dont you reboot your windows servers once in every week?????
-------------------------
Why ?.Since iam in Clustered environment.May be u didnot travelled world wide.
dont thing that iam a supporter of Bill gates.
Stop accusing others and other platforms.Since it is a professional site be professional.
-------
Great minds discuss ideas.Average minds discuss events.Small minds discuss people.
regards
akhadar
I did the same mistake Abhay did. Being rude in replies. :confused:
And, I still believe that paging is a problem with windows OS.. Mebbe, I am wrong.
Now this is allegations ( Direct )....Quote:
Originally posted by nagarjuna
I did the same mistake Abhay did. Being rude in replies. :confused:
Hrishy , Amar , Jurij ....had i ever been rude in replies....???????
And as far as Windows in concerned...
we have 2 TB data...with spoasticated Win mirroring ... so we dont need any comlex back up stratagies....
WE dont reboot machine once in week as u said...only reboot is required if any windows related patches are applyed....
as far as SQL Server DBA ... that you mentioned .. am not..
well am not even Oracle DBA...to be frank ( coz i am fresher and jus joined company say 5 months back )....
You might be very very good DBA...but try respecting ppl.
and thas my personel suggestion to u....
Abhay.
Hi,
You can solve this problem by Recreating the controlfile but not including the missing datafile in the CREATE CONTROLFILE command.
This allow you to drop the tablespace related to the missing datafile when your database ia started.
Thanks, Let me know when this Works...
Bhavin Hingu
Hi Abhay
Unequivocal NO Abhay was never rude Abhay like many people here , was always willing to help..sometimes he even went to simulate the problem..and that spirit i always admire in him.
Hello nagarjuna..Abhay is a excellent guy..i read this thread..i dont think so at any point of time he was rude..abhay just tested on windoze..so what the thread initiator did not mention his OS at all.
well abhay if you r fresher and just joined the company 5 months back..dude i am simple impressed by your skills and talents..you dont have to prove to anybody that you were nice :-)..we all know you were nice.
regards
Hrishy
P.S:advice is like snow the softer it falls the deeper it sinks in
Thanks Hrishy...:-)Quote:
Originally posted by hrishy
Hello nagarjuna..i dont think so at any point of time he was rude..abhay just tested on windoze..so what the thread initiator did not mention his OS at all.
regards
Hrishy
Quote:
well abhay if you r fresher and just joined the company 5 months back..dude i am simple impressed by your skills and talents..you dont have to prove to anybody that you were nice
Wow Abhay if u'r only 5 months old in oracle.u'r really good man.
Guys unix maybe a superior os allowing u to do many things at the os level,windows is not too bad .Someone mentioned about rebooting every week.that's not true man.i feel win2k server is reliable in a clustered environment.
Quote:
Originally posted by nagarjuna
:p :p :p :D :D :D
:p :p :p :D :D :D
If you dont know, learn it.. Dont make sentences. Good DBAs should have patience. You dont have a bit of it. In UNIX, Oracle does not lock the files at OS level.
Dear Nagarjuna
Sorry to comment on this but this forum is for people to find answers they do not know. And Abhay has been very helpful in this forum for many . And also you can see in many threads how he uses his language and has many times told many gurus not to use terms like 'RTFM' and acknowldges tehm to use 'RTM'. Maybe your a great DBA but see Abhay has posted twice asmuch as you have. Pls dont feel hurt. I am sorry if i have hurt you. Staying in madras(Merina pola sugam kedaikuma) has to be cool man. We are all trying to survive in deserts. All the best for your great career.God bless you
Dear Abhay
my heartiest applauses your simply great.