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).
