Setting , Resetting/Unsetting Database Parameters

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)