Oracle Database Discussions

Just came across this post from Howard about PFILE and SPFILE:

I don;t know how many times I've had to say this, but since I've had to say it to my boss, it bears repeating here:

The SPFILE makes absolutely zero, null, nought difference to whether or not something is changeable dynamically or not. Not one bit, one whit or one iota.

Parameters are as static or as dynamic as they were when init.oras rules the roost. Check in V$PARAMETER: if the thing says ISSYSMODIFIABLE is TRUE, then the parameter can be altered with an alter system command -and that's true whether you're using an init.ora or an spfile. If it says ISSYSMODIFIABLE=FALSE, then the parameter CANNOT be altered with an alter system command, and that's true whether you're using an init.ora or an spfile.

Now comes the subtlety: if you add SCOPE=SPFILE to your alter system command then you aren't actually altering the system at all. All you're doing is asking the instance to edit the spfile. So an alter system set db_block_size=67238 scope=spfile will work, because you're not actually asking to alter the current block size at all. You're merely asking the instance to do what you would otherwise have done with notepad or gedit to a traditional init.ora.

Only if you SCOPE=MEMORY is your alter system command actually trying to change the currently running instance.

Of course, the trouble starts when you miss off a SCOPE clause, because then you get SCOPE=BOTH, which means MEMORY+SPFILE. But try that on a parameter which is SYSMODIFIABLE=FALSE: it won't work, because the MEMORY bit trips over the fact that the parameter is not system (dynamically) modifiable. Which just goes to prove that the existence of an SPFILE changes ABSOLUTELY NOTHING about whether a parameter can be dynamically modified or not.

So no, the sentence "my database is using spfile and I have option to use alter system command" makes zero sense if, by it, you mean "I'm using an spfile so can I change things dynamically which I wouldn't be allowed to if I was stuck using a boring old init.ora"

The answer is always and forever, "NO"!

I blame a certain bouffant-haired so-called expert for first promulgating this myth that all parameters suddenly became dynamic in the presence of an spfile. It was never true when he wrote it. It isn't true now. It never will be true. It just happens to be the case that "alter system..scope=spfile" is Oracle's equivalent of "vi init.ora" as far as spfiles are concerned.

To me, this PFILE and SPFILE thing truly was a false myth ... I read more than once the wrong interpretation of SPFILE and now this comes like a surprise to me.
In example on a Sibex book I read:
"When changes are made to the PFILE, the instance must be shut down and restarted before it takes effect. Most changes to the SPFILE can be made dynamically, while the instance is open and running."
This is absolutely misleading in my opinion, since both statements are actually true for both PFILE and SPFILE and it does not make sense to present them as if they represent specific differences. They rather tend to create the wrong concept that SPFILE can change the instance parameters dinamically whereas it only save your time for editing them with vi or gedit.

Thx for clarifying this.

enrico

null