SPFile/PFile help


Quite new to Oracle, so please bare with me.
I need to alter my shared pool in the configuration for the DB instance, but I am receiving the ORA-04033 Error (insufficent memory if though there isn't).  I see that this problem is common and to fix it I need to alter my SPFile, but I cannot find mine to do so. I ran 'show parameter spfile' but it just returned 'String'. Does this mean I have none?
It seems though that I have a Pfile called init.ora in my Oracle/admin/<instance name>'/pfile/ dir. Do I alter this instead?

Please help as I am not sure what to do next.

Also, any suggestions as to what the Shared Pool value should be? What decides its value?

Many thanks.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

schwertnerConnect With a Mentor Commented:
No, you can not alter using v$parameter. Only showing.

To test sga_max_size create a pfile. PFILE can be edited with text editors:

SQL>create pfile='c:\init.ora' from spfile;

After that edit the created pfile.

Now shutdown the db and open it using PFILE:

SQL>shutdown immediate
SQL>startup pfile=c:\init.ora

If it works fine create a SPFILE (show the name in ...\rdbms ...)

SQL>create spfile=c:\spfile...ora' from pfile='c:\init.ora'

Now shutdown the instance, backup the old SPFILE, replace it with the new
and bounce the instance:

Your PFILE should be under $ORACLE_HOME/database folder. It would have the name in the following format init<SID>.ora

Also regarding the Shared Pool value 200MB should be a good size.

Refer to this link for a script that will help you in estimating your shared pool size.
To change SPFILE parameter:
ALTER SYSTEM SET shared_pool_size=250m scope=both
Before this make a spare copy of the SPFILE as PFILE
CREATE PFILE='c:\init.ora' FROM spfile;

make sure that you use the right parameter

SELECT * FROM v$parameter;
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Depending on which platform you are using, you will find spfile or pfile either:
<ORACLE_HOME>/database    <--- windows or
<ORACLE_HOME>/dbs            <-- unix

you will find those files as in formats of spfile<SID>.ora , init<SID>.ora.

>>Oracle/admin/<instance name>'/pfile/
    Usually Oracle will not use this unless you start your instance as:
    startup pfile='<file in that directory>';

>>but I am receiving the ORA-04033 Error (insufficent memory if though there isn't).
    if you are using spfile (it should most of time), then what you need to do is:

    (1) enlarge your sga_max_size
     alter system set sga_max_size=<size>  scope=spfile;

    (2) restart the server.

    (3) enlarge your shared_pool_size
    alter system set shared_pool_size=<size>;

iainmulcahyAuthor Commented:
Thanks Actionwang, Found my SPFile, but when I issue the Alter system command I get the

ORA-32001: write to SPFILE requested but no SPFILE specified at startup.

iainmulcahyAuthor Commented:
My SPFILE does exist in the Database folder (using Windows) by the way. I opened it and it has all the values. However if I run 'show parameter spfile', it returns an empty value. Isn't it being recognised? Do I have to create a new one? I tired but i get 'insufficent privileges'.
SELECT * FROM v$parameter;
as user SYS!
Never edit SPFILE!
iainmulcahyAuthor Commented:
Thanks schwertner. Can you ellaborate on that? Do I alter using v$parameter? Can you give me an example of editing my sga_max_size without using the SPFile?
iainmulcahyAuthor Commented:
THanks man.

Ok, tried create pfile='c:\init.ora' from spfile but I am getting 'ORA-01031: insufficient privileges', even logged in under system. How can I change this
iainmulcahyAuthor Commented:
Ok, solved that. Working on your check list.
iainmulcahyAuthor Commented:

everything worked great. I altered the shared pool size, but on 'startup' I get the following errors:

ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
iainmulcahyAuthor Commented:
Ok that's solved.

schwertner, I did everything on your list but it doesn't seem to be loading the data from the SPfile in the database dir. None of my changes are been loaded!!! Is it even using that file?

Also, what file can I save the initialization parameters too?

iainmulcahyAuthor Commented:
I tried altering the PFILE in Oracle/admin/<instance name>/pfile/ called init.ora and this loaded my changes fine. So I guess I'm done. No need for all the above at all.

Should I create an SPFile from this??
Yes, you should create SPFILE (using the same name as the existing one).
Shutdown the DB.
Save the old SPFILE.
Replace it with the new one.
Startup the instance.
sorry for late reply,


       seems sch had your problem.
       I still recommend that you follow my instructions. Better to do from spfile.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.