vineet_dubey1975 (2/10/2014)
drop in a loop makes the table inaccessible to external user? Can you share some e.g. how we can apply the loop that should i iterate through.Thanks for the help i am getting.
If users have connections that are created and dropped, there's a good chance of finding a time when no-one is connected. If some app creates a connection and keeps it open for ever, you will never be able to drop the DB and will need either to fix the app to behave better or find some different technique.
Assuming no app needs fixing, or that you fix it if it does, the loop should look something like:-
-- pseudocode
finished = false
while not finished
begin
drop db
if drop succeded
begin
rename new db
if rename succeded set finished true
else raise nasty error
end
end
and you need to decide whether the risk of rename failing is low enough that you don't need to handle that nasty error.
Tom