Re: 9i, pfiles and spfiles

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 11 Oct 2003 01:04:40 +1000
Message-ID: <3f86cb14$0$15134$afc38c87@news.optusnet.com.au>

Andy wrote:

> I agree that this is a nice mess of an spfile but I think they are a big
> improvement. Why ? The syntax for alter system allows changes to be made
> to the file, to the running instance or to both (subject to some
> limitiations - some parameters only take effect on restart so can't be
> made to a running
> instance or to "both". Previously for the both option you have to issue
> the alter session command and remember to update the init.ora just how you
> wanted it. Since spotting the SCOPE= option I've become a fan of the
> spfile
> and init.ora can go rot :-). Previous to that I couldn't see the point -
> too ingrained in my Ora 8i ways - that'll teach me to RTFM.

Well, I perversely see it the other way around. That which used to be known to only temporarily affect the instance now, by default, becomes a permanent fixture, unless you remember to specify scope=memory.

I had a rather unusual example just the other day: temporarily add in a 16K buffer cache in order to be able to read a 16K transportable tablespace (which was to be dropped immediately afterwards). Obviously this is only do-able in 9i, but I make a point where possible of using the init.ora, not the spfile. So even in 9i I've become used to 'alter system set db_16K_cache_size=180M;', knowing that I can then read my tablespace, drop it, and have done. Not this time: I was wondering for a couple of hours why I seemed so short of memory on the box, despite the usual bounce of the database after the drop, when I remembered that the particular database I'd used to do the read was one I'd created an spfile for three days earlier. That meant the addition of the extra cache was permanent.

So it's just a question of perspective, and your half full is my half empty.

What I really hate about it is the confusion that arises when you have both

the init.ora AND the spfile....never mind what happens when you have an
init.ora that's *named* spfile.ora!! They should have just abolished the
init.ora and have done with it. And provided a light-weight tool to edit it

directly (OEM is not light-weight!!). Indeed, I reckon they should have included the init.ora within the control file itself: I can see no real need for one binary file to point to another binary file.

But I'm slowly coming around to it. Its definitely the way of the future, and I expect the init.ora to be abolished completely in a version or three's time. So we might as well get used to it.

> Andy
>
> P.S. To save a file in vi use :w or :x to save and exit just like it's
> always been ;-O VI rules :-)

Isn't there an ESC keypress in there first ;-)

Me: I'll stick with pico.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------

Received on Fri Oct 10 2003 - 10:04:40 CDT