結論：

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資料庫效能分析與最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案

聯絡方式：

手機：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/

oracle 11g show parameter顯示隱含引數hidden parameter_x$ksppi

結論：

測試

個人簡介

相關文章