oracle之参数文件探究(三)
',3,'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
END OF STMT
PARSE #16:c=4000,e=1136,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1371282335418052
EXEC #16:c=8000,e=30961,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1371282335510968
FETCH #16:c=8001,e=9483,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1371282335520638
FETCH #16:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=6,dep=0,og=1,tim=1371282335522342
STAT #16 id=1 cnt=7 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us cost=2 size=2115 card=1)'
STAT #16 id=2 cnt=7 pid=1 pos=1 obj=0 op='COUNT (cr=0 pr=0 pw=0 time=331 us)'
STAT #16 id=3 cnt=7 pid=2 pos=1 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=329 us cost=1 size=2115 card=1)'
STAT #16 id=4 cnt=10 pid=3 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=421 us cost=0 size=81 card=1)'
STAT #16 id=5 cnt=1920 pid=3 pos=2 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=28 us cost=0 size=203400 card=100)'
*** 2013-06-15 15:45:46.329
=====================
PARSING IN CURSOR #18 len=33 dep=0 uid=0 oct=42 lid=0 tim=1371282346329261 hv=0 ad='2b1f0eaafc08' sqlid='0000000000000'
alter session set sql_trace=false
END OF STMT
PARSE #18:c=0,e=1037,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1371282346329252
EXEC #18:c=0,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1371282346329521
可见我们查看的是v$parameter试图。
2）参数文件中静态参数和动态参数以及隐含参数
所谓动态参数，就是在数据库运行中修改的参数能够立刻在该实例中生效。静态参数需要重新启动实例之后才能工生效。
修改参数使用如下命令：
alter system set parameter_name=value scope={both|spfile|memory}
both:代表修改的参数能够在本实例立刻生效同时写入参数文件在
数据库下次启动实例的时候仍然有效。
spfile：代表修改参数对本次实例不生效，但是写入参数文件在数据库下次启动实例时生效。
member：代表修改的参数对本实例有效，不写入spfile在数据库下次启动市里的过程不生效。
如何查看我要修改的参数是下次实例启动生效还是立刻生效呢？
可以根据v$parameter中的ISSES_MODIFIABLE和ISSYS_MODIFIABLE进行判断。对于他们可以看一下介绍：
ISSES_MODIFIABLE Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE
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.
eg： SYS@orcl#col name for a20
SYS@orcl#col description for a70
SYS@orcl#r
1* select name,type,description,isses_modifiable,issys_modifiable from v$parameter where name in('processes','cpu_count','sort_area_size')
NAME TYPE DESCRIPTION ISSES_MODI ISSYS_MODIFIABLE
-------------------- ---------- ---------------------------------------------------------------------- ---------- ------------------
processes 3 user processes FALSE FALSE
cpu_count 3 number of CPUs for this instance FALSE IMMEDIATE
sort_area_size 3 size of in-memory sort wo