Re: Illegal option for "ALTER SYSTEM"???
Date: Fri, 9 Jul 2010 07:47:40 -0700 (PDT)
Message-ID: <7b5b5445-f3e9-4482-a4d1-3a489ea25372_at_j8g2000yqd.googlegroups.com>
On Jul 7, 11:15 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Wed, 07 Jul 2010 06:22:52 -0700, Mark D Powell wrote:
> > On Jul 7, 8:21 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> >> This is 10.2.0.4 on Linux x86-64:
>
> >> SQL> alter system set events='1013 trace name context off' scope=both
> >> sid='*';
> >> alter system set events='1013 trace name context off' scope=both
> >> sid='*'
> >> *
> >> ERROR at line 1:
> >> ORA-02065: illegal option for ALTER SYSTEM
>
> >> What's going on? When I try something else, this works:
>
> >> SQL> alter system set global_names=false scope=both sid='*';
>
> >> System altered.
>
> >> Elapsed: 00:00:00.17
> >> SQL>
>
> >> Why are "SCOPE=BOTH SID='*'" illegal options for alter system set
> >> events and yet work perfectly well with set global_names?
>
> >> --http://mgogala.byethost5.com
>
> > Maybe because the correct spelling is event, not events, in the ALTER
> > SYSTEM command per the SQL manual which also says the parameter is not
> > modifiable
>
> > SQL> alter system set event = '1013 trace name context off'
> > scope=spfile;
>
> > System altered.
>
> > SQL> /
> > alter system set event = '1013 trace name context off' scope=both
> > *
> > ERROR at line 1:
> > ORA-02095: specified initialization parameter cannot be modified
>
> > Though strangely enough if you leave scope= off the command works with
> > events:
>
> > SQL> alter system set events = '1013 trace name context off';
>
> > System altered.
>
> > Posted tests use 9.2.0.6 and appear to match initial posted results, but
> > reference to SQL manual based on 10.2.
>
> > HTH -- Mark D Powell --
>
> So, this shouldn't be working at all:
>
> SQL> alter system set events='1013 trace name context off';
>
> System altered.
>
> Elapsed: 00:00:00.06
>
> It does actually work. And using the undocumented "set events" has been
> around for years. It is even documented on some well respected websites,
> like Julian Dyke's:http://www.juliandyke.com/Diagnostics/Events/Events.html
>
> --http://mgogala.byethost5.com- Hide quoted text -
>
> - Show quoted text -
Actually I remembered something on my way to work yesterday but forgot to post it. The syntax using the word events is how events dynamic events were set in the instance back in the days before the spfile was introduced.
There was a syntax for use in the init.ora and for commands that could be dynamically there was an online syntax. It varied slightly from the init.ora version. What I think you have done is stumble upon it. Depending on how far back you go with Oracle this might now be ringing a little bell in your memory.
You may also recognize that the base syntax you tried is what we still use to get system state and hang analyze dumps. I wonder if the either the Oracle database architect or developer made a mistake when the spfile was implemented in using event verse events and created the little, oh by the way, issue you have ran into. It could have been a deliberate choice as the new form was expected to replace the old form or there may a reason Oracle needs both forms. Probably no point in speculating.
HTH -- Mark D Powell -- Received on Fri Jul 09 2010 - 09:47:40 CDT