我们平时通过show parameter xxx来查询的参数都属于非隐含参数，这是通过v$parameter这个动态性能视图得到的，
这个动态性能视图记录了数据库运行时的信息和统计数据。
我使用的是oracle11g
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
下面我们通过跟踪文件来看看当使用show parameter 时是如何从系统中获取参数值的。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2018.trc
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2018.trc
SQL> alter session set sql_trace=true;
Session altered.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl.ora
SQL> ! cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2018.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2018.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 2018, image: oracle@localhost.localdomain (TNS V1-V3)
*** 2014-08-17 00:31:43.993
*** SESSION ID:(40.386) 2014-08-17 00:31:43.993
*** CLIENT ID:() 2014-08-17 00:31:43.993
*** SERVICE NAME:(SYS$USERS) 2014-08-17 00:31:43.993
*** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2014-08-17 00:31:43.993
*** ACTION NAME:() 2014-08-17 00:31:43.993
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1408260703993075 hv=1569151342 ad='4cfed0' sqlid='4tk6t8tfsfqbf'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=0,e=113,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1408260703993064
*** 2014-08-17 00:31:54.234
CLOSE #1:c=0,e=13,dep=0,type=0,tim=1408260714234771
=====================
PARSING IN CURSOR #1 len=289 dep=0 uid=0 oct=3 lid=0 tim=1408260714235047 hv=2462394820 ad='4d0b10c4' sqlid='7cfz5wy9caaf4'
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',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 #1:c=0,e=136,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4015672053,tim=1408260714235045
EXEC #1:c=0,e=125,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4015672053,tim=1408260714235315
FETCH #1:c=10998,e=13715,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=4015672053,tim=1408260714249112
FETCH #1:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4015672053,tim=1408260714249798
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us cost=2 size=2128 card=1)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='COUNT (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=1 size=2128 card=1)'
STAT #1 id=4 cnt=2 pid=3 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1786 us cost=0 size=94 card=1)'
STAT #1 id=5 cnt=2399 pid=3 pos=2 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=6058 us cost=0 size=203400 card=100)'
可以看到show parameter就是通过查询v$parameter视图获得的。
下面来看看v$parameter又是通过什么获得信息的呢？
我们首先通过oracle提供的特殊视图v$fixed_view_definition来查看v$parameter的定义。v$fixed_view_definition记录了其他视图的创建方式。
SQL> desc v$fixed_view_definition
Name Null? Type
----------------------------------------- -------- ----------------------------
VIEW_NAME VARCHAR2(30)
VIEW_DEFINITION VARCHAR2(4000)
SQL> select * from v$fixed_view_definition where view_name='V$PARAMETER';
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
V$PARAMETER
select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE
, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECAT
ED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id
= USERENV('Instance')
可以看到v$parameter又是通过GV$PARAMETER来获取信息的。以同样的方式得到GV$PARAMETER
视图的描述。
SQL> select * from v$fixed_view_definition where view_name='GV$PARAMETER';
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
GV$PARAMETER
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf, decode
(bitand(ksppiflg/256,1),1,'TRUE','FALSE'), decode(bitand(ksppiflg/65536,3),1,'I
MMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decod
e(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')), decode(bitand(ksppstvf,7),
1,'MODIFIED',4,'SYSTEM_MOD','FALSE'), decode(bitand(ksppstvf,2),2,'TRUE','FALSE
'), decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), decode(bitand(ksppi
lrmflg/268435456, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash from
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
x$ksppi x, x$ksppcv y where (x.indx = y.indx) and bitand(ksppiflg,268435456)
= 0 and ((translate(ksppinm,'_','#') not like '##%') and ((translate(ksppinm
,'_','#') not like '#%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5
) > 0)))
可以看到GV$PARAMETER是通过查询两张oracle的内部表x$ksppi和x$ksppcv来获取数据库参数信息等的。
注意到这里有个条件，translate(ksppinm,'_','#')not like '#%'，这个语句利用translate函数把以'_'开头的，
隐含参数过滤掉。知道了这两个内部表之后就可以写一个获取隐含参数的脚本。
脚本内容如下：
select
x.ksppinm name,
y.ksppstvl value,
x.ksppdesc describe
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id=userenv('Instance') and
y.inst_id=userenv('Instance') and
x.indx=y.indx and
x.ksppinm like '%&par%'
/
试验以下这个脚本：
SQL> @getd.sql
Enter value for par: _controlfile_autobackup_delay
old 6: and x.ksppinm like '%&par%'
new 6: and x.ksppinm like '%_controlfile_autobackup_delay%'
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIBE
--------------------------------------------------------------------------------
_controlfile_autobackup_delay
300
time delay (in seconds) for performing controlfile autobackups
Session altered.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl.ora
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2018.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 2018, image: oracle@localhost.localdomain (TNS V1-V3)
*** 2014-08-17 00:31:43.993
*** SESSION ID:(40.386) 2014-08-17 00:31:43.993
*** CLIENT ID:() 2014-08-17 00:31:43.993
*** SERVICE NAME:(SYS$USERS) 2014-08-17 00:31:43.993
*** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2014-08-17 00:31:43.993
*** ACTION NAME:() 2014-08-17 00:31:43.993
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1408260703993075 hv=1569151342 ad='4cfed0' sqlid='4tk6t8tfsfqbf'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=0,e=113,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1408260703993064
*** 2014-08-17 00:31:54.234
CLOSE #1:c=0,e=13,dep=0,type=0,tim=1408260714234771
=====================
PARSING IN CURSOR #1 len=289 dep=0 uid=0 oct=3 lid=0 tim=1408260714235047 hv=2462394820 ad='4d0b10c4' sqlid='7cfz5wy9caaf4'
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',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 #1:c=0,e=136,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4015672053,tim=1408260714235045
EXEC #1:c=0,e=125,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4015672053,tim=1408260714235315
FETCH #1:c=10998,e=13715,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=4015672053,tim=1408260714249112
FETCH #1:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4015672053,tim=1408260714249798
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us cost=2 size=2128 card=1)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='COUNT (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=1 size=2128 card=1)'
STAT #1 id=4 cnt=2 pid=3 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1786 us cost=0 size=94 card=1)'
STAT #1 id=5 cnt=2399 pid=3 pos=2 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=6058 us cost=0 size=203400 card=100)'
可以看到show parameter就是通过查询v$parameter视图获得的。
下面来看看v$parameter又是通过什么获得信息的呢？
我们首先通过oracle提供的特殊视图v$fixed_view_definition来查看v$parameter的定义。v$fixed_view_definition记录了其他视图的创建方式。
SQL> desc v$fixed_view_definition
Name Null? Type
----------------------------------------- -------- ----------------------------
VIEW_NAME VARCHAR2(30)
VIEW_DEFINITION VARCHAR2(4000)
SQL> select * from v$fixed_view_definition where view_name='V$PARAMETER';
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
V$PARAMETER
select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE
, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECAT
ED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id
= USERENV('Instance')
可以看到v$parameter又是通过GV$PARAMETER来获取信息的。以同样的方式得到GV$PARAMETER
视图的描述。
SQL> select * from v$fixed_view_definition where view_name='GV$PARAMETER';
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
GV$PARAMETER
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf, decode
(bitand(ksppiflg/256,1),1,'TRUE','FALSE'), decode(bitand(ksppiflg/65536,3),1,'I
MMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decod
e(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')), decode(bitand(ksppstvf,7),
1,'MODIFIED',4,'SYSTEM_MOD','FALSE'), decode(bitand(ksppstvf,2),2,'TRUE','FALSE
'), decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), decode(bitand(ksppi
lrmflg/268435456, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash from
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
x$ksppi x, x$ksppcv y where (x.indx = y.indx) and bitand(ksppiflg,268435456)
= 0 and ((translate(ksppinm,'_','#') not like '##%') and ((translate(ksppinm
,'_','#') not like '#%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5
) > 0)))
可以看到GV$PARAMETER是通过查询两张oracle的内部表x$ksppi和x$ksppcv来获取数据库参数信息等的。
注意到这里有个条件，translate(ksppinm,'_','#')not like '#%'，这个语句利用translate函数把以'_'开头的，
隐含参数过滤掉。知道了这两个内部表之后就可以写一个获取隐含参数的脚本。
脚本内容如下：
select
x.ksppinm name,
y.ksppstvl value,
x.ksppdesc describe
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id=userenv('Instance') and
y.inst_id=userenv('Instance') and
x.indx=y.indx and
x.ksppinm like '%&par%'
/
试验以下这个脚本：
SQL> @getd.sql
Enter value for par: _controlfile_autobackup_delay
old 6: and x.ksppinm like '%&par%'
new 6: and x.ksppinm like '%_controlfile_autobackup_delay%'
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIBE
--------------------------------------------------------------------------------
_controlfile_autobackup_delay
300
time delay (in seconds) for performing controlfile autobackups