让Oracle的SHOW PARAMETER命令显示隐藏参数(二)
ecode(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 fro
m 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)))
Create pseudo-view of v$paramter
[oracle@zhongwc ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 1 09:01:58 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create or replace view my_v$parameter_with_hidden
(NUM,NAME , TYPE, display_Value , ISDEFAULT , ISSES_MODIFIABLE ,
ISSYS_MODIFIABLE ,ISMODIFIED , ISADJUSTED, DESCRIPTION,
UPDATE_COMMENT)
as
select x.indx+1,ksppinm,ksppity,ksppstvl,ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',
3,'IMMEDIATE','FALSE'), decode(bitand(ksppstvf,7),
1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),
ksppdesc, ksppstcmnt
from x$ksppi x, x$ksppcv y
14 where (x.indx = y.indx)
15 /
View created.
SQL> grant select on my_v$parameter_with_hidden to system;
Grant succeeded.
SQL> conn system
Enter password:
Connected.
SQL> create synonym v$parameter for sys.my_v$parameter_with_hidden;
Synonym created.
able to use "show parameter" to show hidden init parameters
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_db_file_optimizer_read_count integer 8
_optimizer_adaptive_cursor_sharing boolean TRUE
_optimizer_adjust_for_nulls boolean TRUE
_optimizer_autostats_job boolean TRUE
_optimizer_aw_join_push_enabled boolean TRUE
_optimizer_aw_stats_enabled boolean TRUE
_optimizer_better_inlist_costing string ALL
_optimizer_block_size integer 8192
_optimizer_cache_stats boolean FALSE
_optimizer_cartesian_enabled boolean TRUE
_optimizer_cbqt_factor integer 50
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_cbqt_no_size_restriction boolean TRUE
_optimizer_ceil_cost boolean TRUE
_optimizer_coalesce_subqueries boolean TRUE
_optimizer_complex_pred_selectivity boolean TRUE
_optimizer_compute_index_stats boolean TRUE
_optimizer_connect_by_cb_whr_only boolean FALSE
_optimizer_connect_by_combine_sw boolean TRUE
_