Re: dbms_system - set a string parameter in another session?
Date: Tue, 2 Mar 2010 00:30:13 +0800
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.
On Mon, Mar 1, 2010 at 10:40 PM, Jared Still <jkstill_at_gmail.com> wrote:
> On Mon, Mar 1, 2010 at 1:21 AM, Dunbar, Norman <
> norman.dunbar_at_environment-agency.gov.uk> 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