Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

Doubt in pfile

SOLVED
Go to solution
Nirmalkumar
Frequent Advisor

Doubt in pfile

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.
7 REPLIES
Srimalik
Valued Contributor

Re: Doubt in pfile

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.

abandon all hope, ye who enter here..
Yogeeraj_1
Honored Contributor

Re: Doubt in pfile

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Patti Johnson
Respected Contributor

Re: Doubt in pfile

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
Nirmalkumar
Frequent Advisor

Re: Doubt in pfile

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.

Ben Dehner
Trusted Contributor
Solution

Re: Doubt in pfile

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.
Trust me, I know what I'm doing
Yogeeraj_1
Honored Contributor

Re: Doubt in pfile

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Nirmalkumar
Frequent Advisor

Re: Doubt in pfile

Hi Ben and yogeeraj,

Thank u very much..



- Nirmal.