[20170502]11G查詢隱含引數檢視.txt

[20170502]11G查詢隱含引數檢視GV$SYSTEM_PARAMETER3.txt

--//oracle 存在許多隱含引數,一直以為oracle沒有提供正常的檢視查詢該內容,實際上oracle 11G已經提供這方面的功能,只不過oracle並不公開.
--//自己也是偶然發現:

1.環境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> column VIEW_DEFINITION format a120
SYS@book> SELECT * FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME like '%V$SYSTEM_PARAMETER%';
VIEW_NAME                      VIEW_DEFINITION
------------------------------ ------------------------------------------------------------------------------------------------------------------------
GV$SYSTEM_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,'IMMEDIATE',2,'DEFERRED',                                  3,'IMMEDIATE','FALSE'),
                                 decode(bitand(ksppiflg,4),4,'FALSE',                                     decode(bitand(ksppiflg/65536,3), 0, 'FALSE',
                               'TRUE')),     decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE'),  decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),  decode(bit
                               and(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),  decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'),  ksppdesc, k
                               sppstcmnt, ksppihash from x$ksppi x, x$ksppsv y  where (x.indx = y.indx) and  bitand(ksppiflg,268435456) = 0 and  ((tran
                               slate(ksppinm,'_','#') not like '##%') and    ((translate(ksppinm,'_','#') not like '#%')      or (ksppstdf = 'FALSE') o
                               r      (bitand(ksppstvf,5) > 0)))

V$SYSTEM_PARAMETER             select  NUM , NAME , TYPE , VALUE , DISPLAY_VALUE,  ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIA
                               BLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from GV$SYSTEM_PARAMETER where i
                               nst_id = USERENV('Instance')

GV$SYSTEM_PARAMETER2           select x.inst_id,kspftctxpn,ksppinm,ksppity,kspftctxvl,  kspftctxdvl, kspftctxdf,  decode(bitand(ksppiflg/256,1),1,'TRUE
                               ','FALSE'),  decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',                                  3,'IMMEDIATE',
                               'FALSE'),  decode(bitand(ksppiflg,4),4,'FALSE',                                     decode(bitand(ksppiflg/65536,3), 0,
                               'FALSE', 'TRUE')),     decode(bitand(kspftctxvf,7),1,'MODIFIED','FALSE'),  decode(bitand(kspftctxvf,2),2,'TRUE','FALSE')
                               ,  decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),  decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'),
                                 ksppdesc, kspftctxvn,  kspftctxct from x$ksppi x, x$ksppsv2 y  where ((x.indx+1) = kspftctxpn) and  ((translate(ksppin
                               m,'_','#') not like '##%') and    (translate(ksppinm,'_','#') not like '#%'     or (kspftctxdf = 'FALSE') or     (bitand
                               (kspftctxvf,5) > 0)))

V$SYSTEM_PARAMETER2            select  NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, IS
                               MODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, ORDINAL, UPDATE_COMMENT from GV$SYSTEM_PARAMETER2 where inst
                               _id = USERENV('Instance')

GV$SYSTEM_PARAMETER3           select x.inst_id,x.indx+1,ksppinm,ksppstdvl from x$ksppi x, x$ksppsv y  where (x.indx = y.indx)
GV$SYSTEM_PARAMETER4           select x.inst_id,kspftctxsid,kspftctxpn,ksppinm,ksppity,kspftctxdvl,  kspftctxvn,kspftctxct, ksppilrmflg  from x$ksppi x
                               , x$ksppsv2 y  where ((x.indx+1) = kspftctxpn) and  ((kspftctxdf = 'FALSE') or (bitand(kspftctxvf,8) = 8))

V$SYSTEM_PARAMETER4            select SID, NUM, NAME, TYPE, DISPLAY_VALUE, ORDINAL, UPDATE_COMMENT,  PARAM_FLAG from GV$SYSTEM_PARAMETER4 where INST_id
                                = USERENV('Instance')

7 rows selected.

--//自己看還存在幾個檢視GV$SYSTEM_PARAMETER3,GV$SYSTEM_PARAMETER4,V$SYSTEM_PARAMETER4 ,很奇怪oracle並沒有像往常那樣建立V$SYSTEM_PARAMETER3.僅僅存在
--//GV$SYSTEM_PARAMETER3.btw:我查詢10.2.0.4版本沒有這些檢視.
--//很明顯GV$SYSTEM_PARAMETER3可以查詢全部引數.

2.對比看看:

SYS@book> @ &r/hide _allow_resetlogs_corruption
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%_allow_resetlogs_corruption%')
NAME                        DESCRIPTION                                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
--------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_allow_resetlogs_corruption allow resetlogs even if it will cause corruption                   TRUE                   FALSE                  FALSE

SYS@book> select * from GV$SYSTEM_PARAMETER4 where name like '%_allow_resetlogs_corruption%';
no rows selected

SYS@book> select * from GV$SYSTEM_PARAMETER3 where name like '%_allow_resetlogs_corruption%';
   INST_ID        NUM NAME                                     DISPLAY_VALUE
---------- ---------- ---------------------------------------- -----------------
         1       1265 _allow_resetlogs_corruption              FALSE

--//奇怪oracle寫的為什麼不把描述帶出來.

3.另外注意一點:

SYS@book> select OWNER,VIEW_NAME  from dba_views where view_name like '%SYSTEM_PARAMETER%';
OWNER  VIEW_NAME
------ ------------------------------
SYS    V_$SYSTEM_PARAMETER
SYS    V_$SYSTEM_PARAMETER2
SYS    GV_$SYSTEM_PARAMETER
SYS    GV_$SYSTEM_PARAMETER2

--//很明顯oracle有所隱藏,必須以sys使用者執行訪問上面3個檢視(GV$SYSTEM_PARAMETER3,GV$SYSTEM_PARAMETER4,V$SYSTEM_PARAMETER4)

SCOTT@book> select * from SYS.GV$SYSTEM_PARAMETER4 where name like '%_allow_resetlogs_corruption%';
select * from SYS.GV$SYSTEM_PARAMETER4 where name like '%_allow_resetlogs_corruption%'
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SCOTT@book> select * from SYS.GV$SYSTEM_PARAMETER3 where name like '%_allow_resetlogs_corruption%';
select * from SYS.GV$SYSTEM_PARAMETER3 where name like '%_allow_resetlogs_corruption%'
                  *
ERROR at line 1:
ORA-00942: table or view does not exist