Coskan’s Approach to Oracle

March 7, 2007

Setting , Resetting/Unsetting Database Parameters

Filed under: Basics — coskan @ 2:19 pm

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.

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)

About these ads

13 Comments »

  1. does the “alter system reset…” work for all parameters ??

    Comment by JJ — October 25, 2007 @ 2:25 am

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. Hi,
    Iam really thankful for you.
    Sap Training in Chennai

    Comment by rekha — July 18, 2014 @ 11:00 am

  10. 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

  11. 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

  12. 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: