结论：
1，v$parameter视图不能重建，只能查询，因为它是ORACLE自己构建的视图，而非用户后期定义的视图2，show parameter显示隐含参数，可以在SYS用户定义视图，然后在非SYS用户构建PUBLIC SYNONYM V$parameter同义词
3，SYS用户下以上述的第2方式创建V$PARAMETER仍不能显示隐含参数
测试
1,数据库版本SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
2，获取参数_gc_policy_time
SQL> show parameter _gc_policy_time
SQL>
3，以10046分析上述参数取自哪个ORACLE视图
SQL> conn /as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> show parameter _gc
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_11099.trc
4，从10046 TRACE文件可知show parameter从视图v$parameter提取数据
PARSING IN CURSOR #1 len=289 dep=0 uid=0 oct=3 lid=0 tim=1441114843749895 hv=2462394820 ad='de1d3d48' 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
5,可知v$parameter是一个同义词对象
SQL> set linesize 300
SQL> select owner,object_name,object_type from dba_objects where object_name='V$PARAMETER';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------------------------------------- -------------------
PUBLIC V$PARAMETER SYNONYM
6，v$parameter同义词是基于视图v_$parameter定义
SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='V$PARAMETER';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC V$PARAMETER SYS V_$PARAMETER
7，获取视图v$parameter的定义,可见v$parameter视图是源于gv$parameter
SQL> select view_name,view_definition from v$fixed_view_definition where lower(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 , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id = USERENV('Instance')
8，查询视图gv$parameter的定义
SQL> select view_name,view_definition from v$fixed_view_definition where lower(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,'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',4,'SYSTEM_MOD','FALSE'), decode(bitand(ksppstvf,2),2,'TRUE','FALSE'), decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'
), ksppdesc, ksppstcmnt, ksppihash from 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)))
SQL>
9，可见不显示隐含参数其实对2个底层表进行了谓词过滤，只要移除即可
SQL> set autot traceonly
SQL> select 1 from v$parameter where name=lower('_gc_policy_time');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1128103955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 113 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 113 | 1 (100)| 00:00:01 |
|* 2 | FIXED TABLE FULL| X$KSPPI | 1 | 81 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL| X$KSPPCV | 100 | 3200 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
"KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
2 - filter("KSPPINM"='_gc_policy_time' AND
"X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
10,创建自定义的视图
create or replace view v_display_hidden_parameter
(INST_ID, NUM, NAME , TYPE , VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE
, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED ,
ISDEPRECATED , DESCRIPTION, UPDATE_COMMENT , HASH )
as
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',4,'SYSTEM_MOD','FALSE'),
DECODE(bitand(ksppstvf,2),2,'TRUE','FALSE'),
DECODE(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),
ksppdesc,
ksppstcmnt,
ksppihash
FROM
x$ksppi x,
x$ksppcv y
WHERE
x.indx = y.indx
View created.
11,验证上述视图是否可以显示隐含参数
SQL> select name from v_display_hidden_parameter where name like '%gc_policy_time%';
NAME
--------------------------------------------------------------------------------
_gc_policy_time
12，重建v$parameter同义词
SQL> create or replace public synonym v$parameter for v_display_hidden_parameter;
Synonym created.
13,验证show parameter gc_policy_time是否可以成功显示,不成功,这是何因呢？
SQL> show parameter _gc_policy_time
SQL>
14，我们先暂停上述的分析，尝试另一种方法
15，，把上述的视图授权给非SYS用户SCOTT
SQL> grant select on v_display_hidden_parameter to scott;
Grant succeeded.
16,进入SCOTT用户创建同义词
SQL> conn scott/system
Connected.
SQL> create or replace public synonym v$parameter for v_display_hidden_parameter;
Synonym created.
SQL> show parameter _gc_policy_time
ORA-00980: synonym translation is no longer valid
17，上述报错原因在于SCOTT用户无权查询或引用SYS定义的自定义视图
SQL> host oerr ora 00980
00980, 00000, "synonym translation is no longer valid"
// *Cause: A synonym did not translate to a legal target object. This
// could happen for one of the following reasons:
// 1. The target schema does not exist.
// 2. The target object does not exist.
// 3. The synonym specifies an incorrect database link.
// 4. The synonym is not versioned but specifies a versioned
// target object.
// *Action: Change the synonym definition so that the synonym points at
// a legal target object.
18，重建构建同义词，但引用SYS自定义视图，必须要指定SYS用户，这下成功了
SQL> create or replace public synonym v$parameter for sys.v_display_hidden_parameter;
Synonym created.
19，SHOW PARAMETER这下成功了
SQL> show parameter gc_policy_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_gc_policy_time integer 10
SQL>
20,同义词
SQL> conn /as sysdba
Connected.
SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='V$PARAMETER';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC V$PARAMETER SYS V_DISPLAY_HIDDEN_PARAMETER
SQL> select owner,object_name,object_type from dba_objects where object_name='V$PARAMETER';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------------------------------------- -------------------
PUBLIC V$PARAMETER SYNONYM
SQL> drop public synonym v$parameter;
Synonym dropped.
SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='V$PARAMETER';
no rows selected
SQL> drop view v$parameter;
drop view v$parameter
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1788992/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1788992/
1888
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
