Re: dbms_system - set a string parameter in another session?
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: Mark.Bobak@xxxxxxxxxxxx
- Date: Sat, 27 Feb 2010 05:22:58 +0800
I think one potential technical problem here is that string parameters can
have variable width. It's easy to change boolean or an int as its the same
length, same location, but with string you might need to go the way of
allocating the memory for that string on behalf of the other session etc.
I'm not saying that this is necessarily the case with dbms_system, but in
general changing strings is trickier. That's why if anyone recommends you to
change the corresponding UGA variable with oradebug, don't do it, you may
corrupt & crash something.
Another reason here is that the use_stored_outlines "parameter" isn't even a
real (KSP) parameter, it's not listed in v$parameter/x$ksppi and it's not
part of the compilation environment (which you'll see in 10053 trace or the
v$%_optimizer_env views) either...
--
Tanel Poder
http://tech.e2sn.com
http://blog.tanelpoder.com
On Sat, Feb 27, 2010 at 5:14 AM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>wrote:
> Hi Jared,
>
>
>
> Yep, you’re missing something….and that would be a way to set a string
> parameter in another session. We’re all missing it… J
>
>
>
> I’ve seen this discussed a number of times over the years. To my
> knowledge, there’s still no satisfactory solution, through 11.2.
>
>
>
> -Mark
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jared Still
> *Sent:* Friday, February 26, 2010 3:10 PM
> *To:* Oracle-L Freelists
> *Subject:* dbms_system - set a string parameter in another session?
>
>
>
> I feel like I am missing something when I look at dbms_system.
>
>
>
> There are routines for setting integer and boolean parameter values in
> another session:
>
> dbms_system.set_bool_param_in_session
>
> dbms_system.set_int_param_in_session
>
>
>
> What about setting string values?
>
>
>
> For instance, I would like to do the equivalent of this in another users
> session:
>
>
>
> ALTER SESSION SET use_stored_outlines=CIM_TUNE
>
>
>
> but via dbms_system when the user logs on, or dynamically.
>
>
>
> I know I can use execute immediate in a trigger, but that does
>
> not help with changing the value in a current session.
>
>
>
> Any ideas?
>
>
>
> Hopefully this is not a D'Oh! moment. :)
>
>
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>
Other related posts:
