[Oracle] parameter modification Summary

[Oracle] parameter modification summary Oracle parameter modification is complicated. Some parameters can be modified at the session level, and some must be modified at the system level, some parameters take effect immediately after modification (no restart is required), and some parameters take effect only after restart. How do we know this information? You can query the two columns in the dynamic view v $ parameter (as shown below): 1. ISSES_MODIFIABLE indicates whether the parameter can be modified (True or False) at the session level (Alter session set). 2. ISSYS_MODIFIABLE indicates whether the parameter can be modified at the system level (Alter system set). Three values are as follows: 1) IMMEDIATE-effective immediately; 2) DEFERRED-effective for next session; 3) false-A restart is required to take effect (Scope = spfile must be specified). Let's look at the following example:

[sql] SYS@TEST16>select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name in ('workarea_size_policy','audit_file_dest','sga_target','sga_max_size');    NAME                                                                             ISSES_MODIFIABL ISSYS_MODIFIABL  -------------------------------------------------------------------------------- --------------- ---------------  sga_max_size                                                                     FALSE           FALSE  sga_target                                                                       FALSE           IMMEDIATE  audit_file_dest                                                                  FALSE           DEFERRED  workarea_size_policy                                                             TRUE            IMMEDIATE  

 

Only workarea_size_policy can be modified at the session level, and the other three can only be modified at the system level. The modified sga_target takes effect immediately. The next session takes effect after the modified audit_file_dest takes effect. The modified sga_max_size takes effect only after being restarted (spfile ). The syntax of alter session/system set alter session set is as follows: alter session set parameter_name = parameter_value; as described above, not all parameters can be modified at the session level. Only isses_modifiable is true. The syntax of alter system set is as follows: Comment (optional): description can be added when modification; Deferred (optional): If the issys_modifiable of v $ parameter is deferred, the deferred option must be added to make the next session take effect. Scope (default: both): has the following three values: -- memory indicates that it is only modified in the memory and will not expire after the instance is restarted; -- spfile indicates that it is only modified in spfile, it takes effect only after the spfile is re-read after restart. -- both indicates modifying (recommended) Sid (default: *) in both memory and spfile: This option is for RAC. The default value is *, it indicates that all RAC instances are modified at the same time. If you do not want to modify them all, use Sid to specify the Oracle instance. An example today, developers complain that Oracle databases often fail to connect, the following error is reported: ORA-12519, TNS: no appropriate service handler found this error is because the Oracle parameter processes settings are too small, you need to adjust it, the current processes value is:

[sql] SYS@TEST16>show parameter processes;  NAME                                 TYPE        VALUE  ------------------------------------ ----------- ------------------------------  processes                            integer     100  

 

According to the query v $ parameter (as shown below), this parameter is system-level and must be restarted to take effect:

[sql] select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='process';  NAME                                                                             ISSES_MODIFIABL ISSYS_MODIFIABL  -------------------------------------------------------------------------------- --------------- ---------------  processes                                                                        FALSE           FALSE  

 

Use alter system set to modify:

[sql] SYS@TEST16>alter system set processes=1500 comment='change from 100 to 1500' scope=spfile;    System altered.  

 

Note: scope = spfile must be added here, otherwise the following error will be reported because issys_modifiable = falseORA-02095: specified initialization parameter cannot be modified