Re: cannot delete entry from spfile

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 01 Dec 2004 06:58:47 +1100
Message-ID: <41acd106$0$20379$afc38c87@news.optusnet.com.au>

Fabrizio wrote:

> Howard J. Rogers wrote:
> 

>> Ed Stevens wrote:
>>
>>>
>>> Gen it to a pfile, edit to your heart's content, then gen it back to a
>>> spfile. I leave the syntax details to the student as homework.
>>
>>
>>
>> I'll reply to you Ed, but I see you are not alone.
>>
>> There is zero need to convert to a pfile to edit out (ie, remove) the
>> deprecated parameter, given that it has not prevented him from at
>> least starting his instance.
>>
>> The command people should apparently be a little more familiar with is:
>>
>> alter system reset max_enabled_roles scope=spfile sid='*' ;
>>
>> (And the "sid=" clause is required, even though the value specified
>> is, in this case, the default in any case!).
>>
>> In a world where converting to a pfile might not actually remain an
>> option for ever and ever, it might be a good idea to know how to work
>> with spfiles 'natively'.
>>
>> Regards
>> HJR

> 
> 
> Thank you. Second time in a row you teach me something useful about spfile.
> 
> Just to complete what you have started:
> can you explain me the part on "sid='*'"?
> I'm puzzled: why do I need to specify "all instances"?

It's bizarre, isn't it?!

When you're not running RAC, you might have thought that the specification of a SID was not necessary, since you've only got the one to choose from.

Even in a RAC environment, you might reasonably have thought the specification of a SID was not necessary, because "*" is the default one. If you ever do 'alter system set shared_pool_size=200M scope=spfile', for example, and then look at what has been set inside the spfile, you'll see an entry for...

*.shared_pool_size=200M

...the "*" gets put in automatically.

But when it comes to *resetting* a parameter, relying on the default to kick in doesn't work. You absolutely, explicitly, have to specify either a named instance or a "*":

SQL> alter system reset transactions_per_rollback_segment scope=spfile; alter system reset transactions_per_rollback_segment scope=spfile

                                                                 *

ERROR at line 1:
ORA-00905: missing keyword

SQL> alter system reset transactions_per_rollback_segment scope=spfile sid='*';

System altered.

I can't see the logic in having to specify explicitly what would ordinarily be regarded as the default. But that's simply the way it is.

Best regards,
HJR

> 
> As always: many thanks
> 

Received on Tue Nov 30 2004 - 13:58:47 CST