Changing an Initilization Parmeter in Oracle 10g release 2.

I want to increase the HASH_AREA_SIZE parmeter for my oracle 10g instance on startup. I understand that the init.ora is not any longer. How can this be done?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

    There is a new parameter in Oracle 10g. That is workarea_size_policy, with which all the *area_size will be managed internally by Oracle.
  some more info:
Check the parameter workarea_size_policy. If it's set to AUTO, then the *area_size parameters are obsolete and hence they are not shown in the init file. Oracle recommends using spfile from 9i. You can long-in as sys or a dba account and issue
alter system set HASH_AREA_SIZE =10000 -- any size you want
Oracle does not recommend using the HASH_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting  PGA_AGGREGATE_TARGET instead. HASH_AREA_SIZE is retained for backward compatibility.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
scott_m_rubyAuthor Commented:

How do you go about setting that for the instance?

CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.


-- Modify parameter(s) in inrtSID.ora

Mark GeerlingsDatabase AdministratorCommented:
You are correct, the init*.ora file is not used by default in Oracle9 or 10.  The "spfile" is usually used instead.  You do not make changes directly to the spfile as you did in the init*.ora file in the past.  You use "alter system set ..." commands instead, to make the changes indirectly.  By default these "alter system set..." commands affect the current (running) instance only, but if you add either: "scope = spfile" or "scope = both" the spfile will be modified and the change will take affect when the instance is restarted

It is possible to use an init*.ora file if you wish (but then the changed parameters do not take effect until you do an Oracle shutdown and restart).  MikeOM_DBA gave you the syntax for creating an init*.ora file from the spfile, and for starting Oracle from the init*.ora file instead of from the spfile.  I only use this technique though to remove parameters from the spfile.  After starting from an init*.ora file, you can use the opposite command:
create spfile from pfile
create spfile from pfile='[full path to pfile and pfilename]'

If you do that, you should then do an Oracle shutdown (so you are no longer running from the init*.ora file) then a restart (from the spfile) so you can make changes on-the-fly later if you need to.
ALTER SYSTEM SET parameter = value [COMMENT 'text'] [DEFERRED]

   SCOPE = {MEMORY|SPFILE|BOTH} [SID = 'sid' [,SID =...]]
Forced accept.

EE Admin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.