topic Re: Doubt in pfile in Operating System - HP-UX

Doubt in pfile

Nirmalkumar — Mon, 18 Jun 2007 15:23:31 GMT

Hi all,

Is it possible to change the pfile dynamically by issuing

"Alter system parameter=parameter value" .

Note : The parameter is not declared in the pfile.

EXAMPLE:

i didnot decalare large_pool_size in pfile.whether i can change the pfile dynamically by issuing

Alter system large_pool_size=1000M;

i declared sga_max_size in pfile.

Help apperciated...

Thanks,
Nirmal.

Re: Doubt in pfile

Srimalik — Tue, 19 Jun 2007 02:12:06 GMT

Hi,

I dun have the answer to your question but you can subscribe to the mailing lists at lazydba.com, after that you will have one more group of db experts in addition it itrc experts to get the answers.

Re: Doubt in pfile

Yogeeraj_1 — Tue, 19 Jun 2007 03:35:31 GMT

hi Nirmal,

Which version of Oracle are you using?

In 10g, you would do the following:

1. alter system set large_pool_size=80M scope both;

2. create pfile from spfile;

revert

kind regards
yogeeraj

Re: Doubt in pfile

Patti Johnson — Tue, 19 Jun 2007 07:34:37 GMT

You do not need to explicitly set a value to modify it later. So if you did not include large_pool_size in your original init.ora ( from which you created the spfile) then you have the default value.

show parameter large_pool_size - will tell you the current value.

You can modify with
alter system set...
The default scope is BOTH, so the parameter will be changed dynamically and in the spfile. If you set sga_max_size, then you can only change parameters that will not cause the sga to grow beyond max_sga_size. If you try and increase the total sga size beyond max_sga_size it will error.

Patti

Re: Doubt in pfile

Nirmalkumar — Tue, 19 Jun 2007 15:06:01 GMT

Hi all,

Thank you for ur suggestions...

iam not asking whether we can change the value dynamically after declaring the large_pool_size parameter in pfile.

my question is without declaring large_pool_size parameter in pfile.

whether we can issue the below query

alter system large_pool_size=1000M;

Note: we are not using SPFILE in our environment.

yogeeraj - we are using 9i version oracle.

Thanks,
Nirmal.

Re: Doubt in pfile

Ben Dehner — Tue, 19 Jun 2007 17:02:49 GMT

The short version is "yes, you can do this". If you have not declared large_pool_size in the init file, then it is implicitly set to 0 MB in size. But it is still a dynamic parameter, and you can change it at any time with the appropriate "alter system".

Also, you noted that you have set sga_max_size, which is good. Increasing the large_pool from a non-zero value will cause an increase in the SGA size. If max_sga_size is unset, then the SGA cannot grow beyond the initial allocation (mostly from shared_pool, db_cache_size, and java_pool), and the attempt to increase the large_pool would fail with ORA-4033, Insufficent memory to grow pool. In this case, however, you could dynamically shrink one of the other memory pools to allocate space for the large_pool. This holds whether or not the large_pool_size was explicitly declared in the init file.

Re: Doubt in pfile

Yogeeraj_1 — Wed, 20 Jun 2007 02:21:49 GMT

hi Nirmal,

YES. without declaring large_pool_size parameter in pfile, you can issue the below query:
alter system large_pool_size=1000M;

However, beware that on the next database restart the value will be reset to the default (as specified in the pfile)

One of the advantages of using the spfile is that you do not have to go manually change the physical file when you make a change at the database level. Changes to the spfile is "made" using SQL commands...

good luck!

kind regards
yogeeraj

Re: Doubt in pfile

Nirmalkumar — Wed, 20 Jun 2007 12:57:25 GMT

Hi Ben and yogeeraj,

Thank u very much..

- Nirmal.