獲取oracle的隱含引數

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