ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Spfile Pfile

Spfile Pfile

原创 Linux操作系统 作者:tthero00boo 时间:2013-11-20 13:46:19 0 删除 编辑

 

alter_system_set_clause
lets you set or reset the value of any initialization parameter.

The ability to change initialization parameter values depends on whether you have started up
the database with a traditional client-side initialization parameter file (pfile) or with a
server parameter file (spfile). To determine whether you can change the value of a particular
parameter, query the ISSYS_MODIFIABLE column of the V$PARAMETER dynamic performance view.

/*  使用alter_system_set子句,可设置或重置初始化参数的值
 但这取决于数据库是使用pfile还是spfile启动,
 对于特定参数的修改,可查询v$parameter的ISSYS_MODIFIABLE列
*/

If you are using a pfile, then the change will persist only for the duration of the instance.

However, if you have started the database with an spfile, then you can change the value of
the parameter in the spfile itself, so that the new value will occur in subsequent instances.

/*  如果使用pfile 那么改变的作用范围仅在当前实例级
 如果使用spfile 改变可以被写入spfile中,可以在后面的实例中都生效
*/


查看数据库使用哪种初始化参数文件

--pfile:
SQL> startup pfile='/opt/oracle/112/dbs/initmyorcl11.ora.20131120'
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string


value不为空即是使用spfile


ALTER SYSTEM SET parameter = value

;


ALTER SYSTEM RESET parameter sid = ‘sid | *’ ;


deferred : 指定系统修改是否只对以后的会话生效(对当前会话无效,包括指定此修改的会话)。

默认情况下,ALTER SYSTEM命令会立即生效,但是有些参数不能“立即”修改,只能为新建立的会话修改这些
参数,可以使用以下查询那些参数必须使用deferred

sid = ‘sid | *’ : 主要用于集群环境,默认值为sid = ‘*’,通过该参数可以为集群中任何给定的实例唯一地指定参数设置。
reset子句,sid部分是必须的,在非RAC环境中,SID必须指定为sid='*';在RAC环境中就可以通过SID=’sid’来指定此参数对哪个实例起作用。

V$PARAMETER
ISSES_MODIFIABLE 
                Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)

ISSYS_MODIFIABLE 
                Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:

                      IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used 
                                  to start the instance. The change takes effect immediately.

                      DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used 
                                  to start the instance. The change takes effect in subsequent sessions.

                      FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used
                                  to start the instance. The change takes effect in subsequent instances.

对于ISSYS_MODIFIABLE = IMMEDIATE /DEFERRED /FALSE

初始化参数分为动态和静态.


静态参数的更改必须重启数据库才能生效,只在实例启动时才读取,即要么改pfile,要么改spfile --FALSE

改pfile --> vi
改spfile --> 1. sqlplus alter system set ... scope = spfile
    2. vi pfile , create spfile from pfile;


动态参数的更改可以立即生效,不管是用pfile/spfile启动

但有些参数的修改只能在以后的会话中应用--deferred
当前会话也可马上生效 -- immediate
alter system set ...


其实区别只是:

使用pfile时,alter system set ,默认的scope = memory,不可以命令修改静态参数(ISSYS_MODIFIABLE=FALSE )



/* deferred */

SQL> alter system set object_cache_max_size_percent=12 deferred;

System altered.

SQL> show parameter object_cache_max_size_percent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
object_cache_max_size_percent        integer     10
SQL> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn / as sysdba
Connected.
SQL> show parameter object_cache_max_size_percent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
object_cache_max_size_percent        integer     12

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28859270/viewspace-777093/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle Wrap Procedure
请登录后发表评论 登录
全部评论

注册时间:2013-06-30

  • 博文量
    31
  • 访问量
    142627