Re: dbms_system - set a string parameter in another session?
From: Tanel Poder <tanel_at_poderc.com>
Date: Sat, 27 Feb 2010 05:22:58 +0800
Message-ID: <4602f23c1002261322l6714ff83u9447d4fcd528f2ec_at_mail.gmail.com>
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.
Date: Sat, 27 Feb 2010 05:22:58 +0800
Message-ID: <4602f23c1002261322l6714ff83u9447d4fcd528f2ec_at_mail.gmail.com>
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_at_proquest.com>wrote:Received on Fri Feb 26 2010 - 15:22:58 CST
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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
>
-- http://www.freelists.org/webpage/oracle-l