Re: Dropping user with logged in sessions

On 6 fév, 23:14, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> hasta..._at_hotmail.com wrote:
> >> What does the application enduserexpect to see during this operation?
> >> If I were using the app and got an Oracle error message during this
> >> operation, I'd be cursing the database.
>
> >> Why do you want to drop the database objects? What does this achieve for
> >> you?
>
> >> Cheers,
> >> Brian
>
> > Brian, this is a part of an emergency recovery procedure,
> > wherein the wholeuseris dropped and recreated.
>
> > A variation is to stop and block all connections while
> > a schema migration to a newer release is going on.
>
> > In theory the client apps should not be running, but
> > in actual reality some of them still are, by mistake,
> > often in remote locations.
>
> > While the schema is being updated or recreated,
> > the users will see the message that the database
> > is not available.
>
> > Schemas are versionned, and applications will
> > switch to a read-only mode when they find
> > themselves connected to the wrong version.
>
> Have you considered droppingthe schema objects rather than drop the
> entire schema? If you drop the entire schema, then you will have to
> regrant all privs and roles the schema originally had.
>

No, Brian, I didnt consider this.

As I recall, we decided (years ago) to drop the user because it was the path of least resistance. The same code is used afterwards to either recover or create a brand new user. That code grants the required priviledges (we own a dedicated instance on most sites) .

On sites where we use a shared instance, the local dba is responsible for backup and recovery with whatever procedure s/he feels confortable with.

Do you see any problem with this strategy ?

Regards

• raoul