RE: changing init.ora and spfile

From: <>
Date: Fri, 31 Jul 2009 13:28:57 -0400
Message-ID: <>

Is it not true now that automatic memory management will not occur is sga_max = target? Id resizing buffer cache, shared etc.

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
[] On Behalf Of Robert Freeman Sent: Friday, July 31, 2009 1:19 PM
To:; Subject: Re: changing init.ora and spfile

Kind of the hard way to do it. This would be easier I think:

Assuming database is up and running:

  1. You don't need to make an entry for SGA_MAX_SIZE if you are setting SGA_TARGET. SGA_MAX_SIZE will default to SGA_TARGET.

This changes the value SGA_MAX_SIZE in the SPFILE. This change is persistent, and will take place after the next reboot.

2. Change SGA_TARGET using alter system command: alter system set sga_target=500m scope=spfile;
This changes the value SGA_TARGET in the SPFILE. This change is persistent, and the scope=spfile indicates that the change will take place after the next reboot.

3. Restart the database (startup force)

Thats it.

If something bad happens and the database won't nomount (for example, you set sga_target to high) you can easily convert an spfile to a pfile and back when a database is down using the create spfile from pfile and create pfile from spfile commands. This should not happen often though.


 Robert G. Freeman
Oracle ACE
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Features (Oracle Press)
Other various titles out of print now... Blog: The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)

  • Original Message ---- From: "" <> To: Sent: Friday, July 31, 2009 11:09:16 AM Subject: changing init.ora and spfile


I want to make sure that I am making this change correctly. I have a DB which users spfile and I need to increase the SGA_MAX and SGA_TARGET parameters. My plan is to

  • modify the init.ora file
  • make a copy of an existing spfile
  • shut down the database
  • restart as startup using pfile=initXXX.ora
  • when database is started execute create spfile from pfile

That should guarantee that these changes are in the spfile and will not be
lost next time the database is bounced. Am I correct?

thank you

Gene Gurevich

Please consider the environment before printing this email.


Received on Fri Jul 31 2009 - 12:28:57 CDT

Original text of this message