Illegal option for "ALTER SYSTEM"???

[Mladen Gogala]

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

[Mark D Powell]

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

[Mladen Gogala]

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

[joel garry]

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SYS@TTST> alter system set event = '1013 trace name context off'
scope=both ;

alter system set event = '1013 trace name context off' scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SYS@TTST> alter system set event = '1013 trace name context off';

alter system set event = '1013 trace name context off'

*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SYS@TTST> alter system set events= '1013 trace name context off';

System altered.

SYS@TTST> alter system set event = '1013 trace name context off'
scope=spfile;

System altered.

"The default is SCOPE=BOTH if a server parameter file was used to
start up the instance, and MEMORY if a text initialization parameter
file was used to start up the instance."

And here I am, using a server parameter file. I wonder if someone had
the idea of using events to mean something different than event, but
the reality became confused when someone said "let's document it and
call it a feature." The manual does not say event is not modifiable
(I could've missed something...), but rather should only be modified
under supervision.

jg
--
@home.com is bogus.
http://hosted.ap.org/dynamic/stories/U/US_TEC_TECHNOLOGY_CONSOLIDATION?SITE=KYWAM&SECTION=HOME&TEMPLATE=DEFAULT

[Mark D Powell]

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