[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