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

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

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

Like this:

Related

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

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.

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

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.

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!

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.

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