Thank you.
I had done a DESC DBMS_SYSTEM and used SET_EV but never bothered to look for doc/notes on SET_BOOL_PARAM_IN_SESSION.
Hemant
At 04:08 AM 12-06-03 -0800, you wrote:
Oh, my bad.. ;)
Here you go:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production
SQL> select sid, serial#, username from v$session;
SID SERIAL# USERNAME ---------- ---------- ------------------------------ 1 1 2 1 3 1 4 1 5 1 6 1 7 3757 8 3761 9 3761 10 3761 11 20 NORAD_USER
SID SERIAL# USERNAME ---------- ---------- ------------------------------ 12 7213 SYS 17 532 GERRY
13 rows selected.
SQL> exec dbms_system.SET_BOOL_PARAM_IN_SESSION (17, 520, 'optmizer_mode', TRUE);
PL/SQL procedure successfully completed.
Mark
-----Original Message----- Hemant K Chitale Sent: 12 June 2003 12:09 To: Multiple recipients of list ORACLE-L
I am talking of a scenario where another user has already connected to the database and is running a report. I can use dbms_system.set_ev to get a 10046 trace online [the "running session"] but can't change it's optimizer_mode. I do not want to have to stop the session, change and restart it --- it could be a report to modify which could take a few weeks in the approval cycle or it could be coming from an application server / JSP which has generated it's own SQL. Changing optimizer_mode globally at the ALTER SYSTEM level isn't what I am looking for either. Hemant
--- Mark Leith <[EMAIL PROTECTED]> wrote:
> Connected to: > Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production > With the Partitioning option > JServer Release 8.1.7.0.0 - Production > > SQL> alter session set optimizer_mode = rule; > > Session altered. > > SQL> > > SQL> alter session set optimizer_mode = choose; > > Session altered. > > SQL> > > HTH > > Mark > > =================================================== > Mark Leith | T: +44 (0)1905 330 281 > Sales & Marketing | F: +44 (0)870 127 5283 > Cool Tools UK Ltd | E: [EMAIL PROTECTED] > =================================================== > http://www.cool-tools.co.uk > Maximising throughput & performance > > -----Original Message----- > Hemant K Chitale > Sent: 12 June 2003 11:25 > To: Multiple recipients of list ORACLE-L > > > > Is there any way that we can change the optimizer_mode > for a running session ? [Oracle 8.1.7] > > The session may have started with the instance-level > optimizer_mode=RULE [Oracle Apps R11.0] but I > want to change the optimizer_mode for subsequent SQLs > executed by the session, without killing and > restarting the session -- something like using > DBMS_SYSTEM.SET_EV to set an event in a running session. > > > > Hemant K Chitale > http://hkchital.tripod.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Hemant K Chitale > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting > services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like > subscribing). > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.487 / Virus Database: 286 - Release Date: 01/06/2003 > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.487 / Virus Database: 286 - Release Date: 01/06/2003 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mark Leith > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting > services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like > subscribing). >
Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.487 / Virus Database: 286 - Release Date: 01/06/2003
--- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.487 / Virus Database: 286 - Release Date: 01/06/2003
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
