Hi Jerry,
the problem with this approach is that it requires establishing a new
connection for each user, establishing connections is time consuming plus
resource consuming if the users sits there and does very little. Our
existing approach of preallocating connection and then share them out on
demand means that for a users connecting to oracle takes 0ms plus they only
have the connection for the life of a single procedure so we can support
many many active users over a few "real" connections so we can now allocate
more memory to the SGA as its not being tied up in the users area
Its now very unusual to find more than 10 connections to Oracle versus 140+
before
Rob
Jerry Gitomer wrote in message <35C9E059.56D8_at_ictgroup.com>...
>Hi Rob,
>
> Have you considered changing the front end app to invoke Oracle with
>the login id of the user? This would require that you set up the Oracle
>users with the same ids as the system accounts and that you come up with
>a contrived password for Oracle. In my case I use the Oracle user name
>as the Oracle password and depend on the operating system to provide
>security.
>
>regards
>
>Jerry
>
>
>
>Rob Edgar wrote:
>>
>> Maybe I didnt explain myself too well.
>>
>> Of course I know how to create and drop users in Oracle that is not the
>> issue here.
>>
>> We start off by creating a pool of connections to oracle in a mid tier
>> application under a generic user account. When a user ina front end app
>> needs to do somehting in Oracle one of these "pooled" connections is
>> allocated to hime. However as far as Oracle is concerned the "user" hasnt
>> chnged its still the generic user.
>>
>> What we would like to do is to change the session variable USERID to the
>> valid oracle account of the user whom we allocated the connection to. We
>> could do this by setting a global variable in oracle to the correct id
but
>> it would be a bit neater if we could get the session variable to change.
I
>> know this is probably a slim chance but I thought I might ask anyway...
>>
>> Rob
>>
>> EXE wrote in message <35C931F8.C5628ED6_at_exesolutions.com>...
>> >> Is there a way by using PL/SQL to change the USERID variable.
>> >>
>> >> We have people who will be consecutively sharing a permanently logged
in
>> >> connection and we would like to be able to reassign the variable as
the
>> >> connction gets allocated out to a user mainly so we can continue to
use
>> it
>> >> in triggers etc that store the value USERID.
>> >>
>> >> I know I could achieve the same thing with a package variable but it
>> would
>> >> be a little bit neater if I could set the USERID var instead of
creating
>> my
>> >> own.
>> >
>> >You do not "change" the USERID. You CREATE them and DROP them. Look into
>> the
>> >books for the CREATE USER and DROP USER commands.
>> >
>> >Regards,
>> >
>> >Daniel A. Morgan
>> >
>
>--
>Jerry Gitomer ICT Group
>jgitomer_at_ictgroup.com Langhorne PA
>jgitomer_at_yahoo.com Opinions are mine not those of ICT Group
Received on Mon Aug 10 1998 - 07:08:28 CDT
