Re: Dropping user with logged in sessions

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 6 Feb 2007 22:14:25 GMT
Message-ID: <JD2B44.3JA@igsrsparc2.er.usgs.gov>

hasta_l3_at_hotmail.com wrote:
> On 6 fév, 15:01, Brian Peasland <d..._at_nospam.peasland.net> wrote:

>> hasta..._at_hotmail.com wrote:
>>> Dear group,
>>> I must write an automated procedure to drop
>>> an Oracleuser/schema (cascade)
>>> The procedure must succeed even if client apps
>>> are connected for that schema.
>>> Some clients perform automatic reconnection,
>>> and will quickly ( < 30 secs) try to reconnect
>>> if they find that the connection is lost.
>>> What is in your opinion the most foolproof
>>> way to implement this ?
>>> Thanks much
>>> --- Raoul
>>> Oracle 8.1.7.3 and 9.2.0.4
>>> Client connections are over  TCP/IP
>>> W 2000/2003
>> 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 whole user is 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 dropping the 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.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Tue Feb 06 2007 - 16:14:25 CST