Re: sqlplus script dropping conected user

On 12 Sep, 17:07, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Sep 12, 7:38 am, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On Sep 12, 12:00 pm, Stan.S..._at_gmail.com wrote:
>
> > > I write installation scripts in sqlplus which as a first command issue
> > > "DROP USER". When the user to be dropped is currently connected i get
> > > "can't drop connected user" error, but the script carries on.
> > > What i want to do is to display prompt like "you have to disconnect
> > > first" then pause and exit the script on keypress.
> > > The thing is i can't think of any clever way to achieve it. I know i
> > > can use whenever sqlerror exit, but it just exits without telling why.
> > > I could most likely write some pl/sql block or a shell script to help
> > > with this, but it doesn't look very smart to me.
> > > Anybody there has a better solution?
>
> > Forcibly disconnect all sessions of <user> prior to starting the
> > installation script.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> As suggested killing the user sessions first is not difficult to
> script but I do not understand why you would be dropping connected
> users.
>
> What kind of installation scripts?
> Who is supposed to be running the installation scripts?
> Is this just an issue you are hitting because you are in development?
>
> More information about what you are doing and why might allow someone
> to provide useful guidence.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

The script is run by a deployer with an access to prod.

The user is being dropped to allow for a clean installation. The user is connected because there is an application running on an app server using connection pool with a number of sessions.

I was also thinkink about killing the user sessions as suggested by sybrandb but i would rather display the "you have to disconnect first" message to give the deployer an opportunity to shutdown the app server rather then forcibly kill the sessions.

I think it must be quite common situation and was just wondering whether some one has any better solution then i have. Received on Thu Sep 13 2007 - 06:10:41 CDT