Oracle 关于 alter parameter 的设置 详解

今天主要学习了下set parameter 的参数： 

 

To see the file location:

SQL>  show parameter dump;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /oracle/ora102/admin/dbsrun/bd
                                                 ump
core_dump_dest                       string      /oracle/ora102/admin/dbsrun/cd
                                                 ump
max_dump_file_size                   string      UNLIMITED
shadow_core_dump                     string      partial
user_dump_dest                       string      /oracle/ora102/admin/dbsrun/ud
                                                 ump

2: Setting values in spfiles:

Command:
Alter system set parameter = value <comment='text'> <deferred> <scope=memory | spfile | both>

comment:

"deferred" specifies whether the system change takes place for subsequent sessions only
(not current established sessions, including the one making the change). By default,
the "alter system" command will take effect immediately, but some parameters can not
be changed "immediately" -- they can be changed only for newly established sessions.
we can use the following query to see what parameter mandate the use of deferred:

SQL> SELECT NAME from v$parameter where ISSYS_MODIFIABLE= 'DEFERRED';
 
NAME
--------------------------------------------------------------------------------
backup_tape_io_slaves
audit_file_dest
object_cache_optimal_size
object_cache_max_size_percent
sort_area_size
sort_area_retained_size
olap_page_pool_size
 
7 rows selected.
 
SQL> ALTER system set sort_area_size=65536;
ALTER system set sort_area_size=65536
                                    *
ERROR at line 1:

ORA-02096: specified initialization parameter is not modifiable with this option
 
 
SQL> ALTER system set sort_area_size=65536 deferred;
 
System altered.
 
SQL>

--------------

"SCOPE = memory | spfile | both" indicates the "scope" of this parameter setting. we have the
choice of setting the parameter values with the following:

 1:SCOPE=memory changes it in the instance(s) only; it will not survice a database restart.
The next time you restart the database, the setting will be whatever it was before the change.

 2:SCOPE=spfile changes the value in the spfile only. The change will not take place until
the database is restarted and the SPFILE is processed again. Some parameters may only be changed
using this option - for example, the processes parameter must use SCOPE=SPFILE, as we can not
change the active instance value.

 3:SCOPE=both means the parameter change take place both in memory and in the spfile. The
change will be reflected in the current instance and, the next time you restart the database, the
change will be in effect.