Re: dbms_system - set a string parameter in another session?

  • From: Jared Still <jkstill@xxxxxxxxx>
  • To: Tanel Poder <tanel@xxxxxxxxxx>
  • Date: Mon, 1 Mar 2010 08:44:24 -0800

For the time being at least, (and as it has been since 8i I would guess)
if you have need to do this dynamically, then the default category must
be used.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com



On Mon, Mar 1, 2010 at 8:30 AM, Tanel Poder <tanel@xxxxxxxxxx> wrote:

> While the regular (KSP) parameters and query compilation environment
> parameters (QKSCE, also known as optimizer environment) are stored in shared
> pool, the "use_stored_outlines" one isn't. It's similar to the
> "tracefile_identifier" parameter in that sense, these parameters are stored
> in the private memory of the process.
>
> So, if another process wants to change it, you would need to use oradebug
> to send a signal to that process, that process would stop doing whatever it
> was doing and handle the signal - change the string value (and allocate more
> memory if needed) and cleanly resume to do whatever it was doing.
>
> It's doable but probably too much effort compared to changing fixed
> variables via oradebug.
>
> Tanel.
>
>
> On Mon, Mar 1, 2010 at 10:40 PM, Jared Still <jkstill@xxxxxxxxx> wrote:
>
>> On Mon, Mar 1, 2010 at 1:21 AM, Dunbar, Norman <
>> norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>>
>>> Morning all,
>>>
>>> >> That string parameters can have "variable width."  is
>>> >> actually, in my
>>> >> opinion, a strong enough reason for Oracle to not provide or
>>> >> not even
>>> >> attempt to provide a SET command for parameters in other sessions.
>>>
>>> I disagree!
>>>
>>> However in my defence, I claim ignorance of how Oracle does things
>>> internally, but I'm almost 100% certain plain C is used, so, to change a
>>> string parameter would be as [simple as] follows:
>>>
>>>
>> Thank you Norman, I to am of the opinion it could be done.
>>
>> Perhaps it has never been done because it has not been formally requested?
>>
>>
>> Jared Still
>> Certifiable Oracle DBA and Part Time Perl Evangelist
>> Oracle Blog: http://jkstill.blogspot.com
>> Home Page: http://jaredstill.com
>>
>>
>

Other related posts: