Re: Dropping user with logged in sessions

On Feb 6, 6:39 am, hasta..._at_hotmail.com wrote:
> Dear group,
>
> I must write an automated procedure to drop
> an Oracle user/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

have a look at "alter system kill session <sid, serial#>" You can find the sid and serial# in the v$session table

It may take oracle some time to actually kill the session, so you have to see whether this works for you.

If that doesn't work you can resort to killing the sessions on a unix (or windows) level. But..........this can have catastrophic results if you kill the wrong oracle process.

Best regards,

Martijn Received on Tue Feb 06 2007 - 06:13:28 CST