There are more than 250 documented and more than 900 documented parameters for Oracle 10g
You can query the documented parameters with the query below
select name,value,isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated,
from v$parameter
You can query the list of undocumented parameters with the script mentioned before “how to view list of hidden parameters”
Also you can query v$parameter2 as the same way you query the v$parameter view to view the session specific parameter values.
The values from the columns isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated are important.
isdefault = TRUE/FALSE –indicates if the value is the default value
isses_modifiable=TRUE/FALSE –indicates if the value can be modified by sessions
issys_modifiable = IMMEDIATE/DEFERRED/FALSE –indicates if the value can be modified by alter system command. Values represents when the changes takes effect. For immediate, no matter what you use (spfile or init file ) parameter change takes effect immediately.For deferred, parameter can be changed but change takes affect in subsequent sessions not for currently established sessions. For false it means parameter cannot be changed if you dont have a spfile.
isinstance_modifiable= true/false –this option is spesicif for RAC which has multiple instances for one database
If you want to view value of the parameter briefly you can also use show parameter command which searches the entered string with wildcard on both ends
SQL> show parameter pool
NAME TYPE VALUE
———————————— ———– ——–
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 7759462
shared_pool_size big integer 0
streams_pool_size big integer 0
As you see the output above all parameters which include “pool” string in it are listed briefly.
Setting Parameter for Instance
You can set the parameter values with the command below
Alter system set parameter=value <comment=’text’> <deferred>
<scope=memory|spfile|both> <sid=’sid|*’>
elements of the command;
scope =BOTH/SPFILE/MEMORY indicates the scope of the parameter setting. both is the default behaviour.
sid = SID/* if you are on RAC environment you can give SID or * for this element which indicate that the setting will effect only the given SID or all nodes in RAC configuration.
comment= You can comment the change for future lookups. This comment can be viewed from UPDATE_COMMENT column of v$parameter view.
deferred = this is the interesting element which i was aware of the existence till i read the book of Mr. Kyte. Indicates that the parameter change takes place for subsequent sessions only (not currently established sessions, including the one making the change). This element must be used for the parameters whose ISSYS_MODIFIABLE column in v$parameter view is deferred.
- When you want to change the parameter only for the running instance and not for the subsequent instance you can change it only in memory. ;
SQL> alter system set cpu_count=4 scope=MEMORY;
System altered.
- When you want to change the parameter for subsequent instances only
SQL> alter system set cpu_count=4 scope=SPFILE;
System altered.
- When you want to change the parameter for running and subsequent instances (if issys_modifiable is not false);
SQL> alter system set cpu_count=4 scope=BOTH;
System altered.
!!!!!When using an init.ora parameter file, the default and only valid
value is SCOPE=MEMORY. So you must manually edit your init.ora file to change the parameter for subsequent instances.
Setting Parameters for sessions
You can change the values of some parameters whose ISSES_MODIFIABLE column of v$parameter view is TRUE. Scope of the parameter will be the end of the session.
SQL> alter session set timed_statistics=false;
Session altered.
SQL> show parameter timed_statistics
NAME TYPE VALUE
———————————— ———– ——-
timed_statistics boolean FALSE
Resetting/Unsetting parameters
If you want to use the default value of the parameter you have changed. You must reset it.
alter system reset parameter <scope=memory|spfile|both> sid=’sid|*’
Elements are same as setting parameter. The only difference is “you must” give si value(for single instance use ‘*’).
SQL> alter system reset control_file_record_keep_time scope=spfile sid=’*’;
System altered.
!!!!!Do not try to change the value of a parameter with ” or null to reset it to default
Common Error codes for parameter setting/resetting;
1-ORA-02095:
SQL> alter system set db_writer_processes=2;
alter system set db_writer_processes=2
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified (this means parameter cannot be changed dynamically)
2-ORA-02096:
SQL> alter system set object_cache_optimal_size=204800;
alter system set object_cache_optimal_size=204800
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
This happened because the parameter is deferred parameter and must be set with this option like below
SQL> alter system set object_cache_optimal_size=204800 deferred;
System altered.
3- ORA-32009
SQL> alter system reset control_file_record_keep_time scope=both sid=’*’;
alter system reset control_file_record_keep_time scope=both sid=’*’
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance xe
Action is said Retry the query for the local instance if needed. (I tried it below but it gives me another error)
4- ORA-32010
SQL> alter system reset control_file_record_keep_time scope=both sid=’xe’;
alter system reset control_file_record_keep_time scope=both sid=’xe’
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE
Action: Change the sid and/or the parameter. (I couldnt do it i think this is a bug ?????)
5-ORA-00096:
SQL> alter system set log_archive_dest_state_1=1 scope=both;
alter system set log_archive_dest_state_1=1 scope=both
*
ERROR at line 1:
ORA-00096: invalid value 1 for parameter log_archive_dest_state_1, must be from among alternate, reset, defer, enable
Footnote (07/07/2009) On Oracle 11G you dont need to put scope=spfile sid=’*’ for resetting the parameter.
SQL> alter system reset pga_aggregate_target;
System altered.
References Used :
Oracle® Database SQL Reference 10g Release 2 (10.2)
Oracle® Database Reference 10g Release 2 (10.2)
Oracle® Database Error Messages 10g Release 2 (10.2)
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution
(Mr Thomas Kytes Book Pg=116-121)
does the “alter system reset…” work for all parameters ??
Comment by JJ — October 25, 2007 @ 2:25 am
It seems that the ORA-32009 error comes from having different SID values across the ALTER SYSTEM SET/RESET commands. I found that if the SID values were exactly the same when resetting the parameter as when setting the parameter then everything worked (which in my case meant that I had to add an extraneous SID parameter in my SET command that really wasn’t needed).
Terry
Comment by Terry — November 29, 2007 @ 9:31 pm
Hello all,
regarding ORA-32010: I just ran into the same problem while trying to reset a hidden parameter and found out that the error is related to the fact that Oracle cannot change the value of this parameter “in memory”, i.e. if You specify scope=spfile it will work. The error message in this context is a bit misleading.
alter system reset control_file_record_keep_time scope=both sid=’xe’
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE
Comment by Stefan Obermeyer — April 23, 2009 @ 12:57 pm
[…] from: https://coskan.wordpress.com/2007/03/07/setting-or-resetting-unsetting-parameters/ […]
Pingback by Oracle CPU_COUNT parameter | SAP and some other IT Knowlegde from my works. — March 27, 2011 @ 3:56 pm
good !! it helps.
But I want to change the below parameters in Oracle 11.2, Please advise exactly what SQL command I need to pass?
_fix_control (13077335)
_fix_control (13627489)
_mutex_wait_scheme
_mutex_wait_time
_optimizer_adaptive_cursor_sharing
_optimizer_extended_cursor_sharing_rel
_optimizer_use_feedback
db_files
event (31991)
remote_os_authent
Thanks in advance
Comment by Bhagirathi — January 21, 2013 @ 8:18 am
A motivating discussion is definitely worth comment.
There’s no doubt that that you need to publish more on this subject, it might not be a taboo matter but generally people don’t talk about such
subjects. To the next! Kind regards!!
Comment by omron bf508 body composition and body fat monitor bathroom scale review — February 12, 2013 @ 8:38 am
Hello, Neat post. There’s a problem with your website in web explorer, might check this? IE still is the market chief and a good component of other people will omit your wonderful writing because of this problem.
Comment by Ona — January 10, 2014 @ 3:14 am
I am really thankful to the owner of this web site who has shared this fantastic post at at this time.
Comment by Car Rental Kuala Lumpur — February 23, 2014 @ 12:02 pm
Hey! I just would like to give an enormous thumbs up for the nice data
you might have right here on this post. I will likely be coming back
to your weblog for more soon.
Comment by SEO — March 24, 2014 @ 10:01 am
Hi,
Iam really thankful for you.
Sap Training in Chennai
Comment by rekha — July 18, 2014 @ 11:00 am
Somebody necessarily lend a hand to make severesly
posts I would state. That is the first time I frequented your web page annd up
to now? I surprised with the research you made to create this actual publish incredible.
Magnificent activity!
Comment by Celebrity Diet Plans — July 24, 2014 @ 4:00 pm
Quality articles or reviews is the main to invite the people to
go to see the site, that’s what this site is providing.
Comment by porn subscription rankings — July 25, 2014 @ 6:00 am
Write down what you’re grateful for each day. In moments when you’re feeling really down, read
Oakley Jupiter Squared http://www.ebuyaccessories.com
Comment by Oakley Jupiter Squared — September 18, 2014 @ 1:10 am
Masteг consultant for do – TERRa, Justin Harrison, lost 7 lbs.
Hօw our mind adapts to failureѕ will largely determine the
condition of ourr inherited body. Whenn most people would throw in the towel, these people simply stucҝ with it.
Comment by phentermine — October 6, 2014 @ 5:24 am
Hi I would like to log in a database and set all the underscore parameters that are changed on system level back to their default value.
I would like to do it in a script, so I cannot hard-code values.
How can I accomplish this?
– Apparently reset does not work on session level. Does it?
– Where can I get the default values of the parameters?
thanks
Comment by vaurob — November 16, 2014 @ 9:04 pm
you’re in reality a just right webmaster. The website loading velocity is incredible. It kind of feels that you are doing any distinctive trick. In addition, The contents are masterwork. you have performed a great job in this subject!
Comment by SEO pyramid — September 29, 2015 @ 4:27 am
Good it helps!!!
Coskan ,
May i know why we should not reset the parameter with null values.
Appreciate your help!!!!
Thanks,
Uday
Comment by udayjampaniUday — May 29, 2016 @ 5:03 am
one quick ques tion,if i change value of open_cursors para using alter system,will existing sessions also takes this increased value or only new .or future sessions (i am not using deffered)
Comment by lucky — January 24, 2018 @ 1:08 pm
pass the test series 7
Setting , Resetting/Unsetting Database Parameters | Coskan’s Approach to Oracle
Trackback by nclex exam results — April 26, 2019 @ 5:16 am
corporate proxy solicitation Services rendered
Setting , Resetting/Unsetting Database Parameters | Coskan’s Approach to Oracle
Trackback by georgeson proxy solicitation services — April 26, 2019 @ 5:31 am
Is there a difference between resetting a parameter and explicitly setting it to its default value?
Comment by stephan t uzzell — March 4, 2021 @ 8:40 pm