Re: Illegal option for "ALTER SYSTEM"???

From: Mladen Gogala <no_at_email.here.invalid>
Date: Wed, 7 Jul 2010 15:15:46 +0000 (UTC)
Message-ID: <pan.2010.07.07.15.15.45_at_email.here.invalid>

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

Received on Wed Jul 07 2010 - 10:15:46 CDT