用show parameter也能顯示隱含參數

Oracle 數據庫啟動時用到的數據庫初始化參數，我們通常是在 sqlplus 中使用 show parameter 命令來獲取。但這里看到只會是一些公開的參數，和已經修改的隱含參數。 Oracle 還有一批隱含參數，在 debug 或者實現某些功能時非常有用。

在網上，你可以很方便的查到如何查詢這些隱含參數。但是，如果我還想繼續使用 show parameter 方法顯示隱含參數呢？這該如何實現？
-
 

我在這里介紹一個方法，來實現這個目標。介紹這個方法的目的不僅僅是為了實現 show parameter 可以顯示隱含參數，還包括如何分析 show parameter 的實現過程。在遇到數據庫問題診斷需求時，這個方法，您也可以參考一下。

首先，我們分析 show parameter 這個命令是如何實現顯示初始化參數值的。

登錄 sqlplus / as sysdba

使用 alter session set sql_trace=true; 追蹤 show parameter 的執行過程。

SQL> alter session set sql_trace=true;

Session altered.

SQL> show parameter "_pga_max_size"

SQL> host; 

bash-3.2$ more /u01/Oracle/app/oracle/admin/htzq/udump/htzq2_ora_19678.trc

/u01/Oracle/app/oracle/admin/htzq/udump/htzq2_ora_19678.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Oracle_HOME = /u01/oracle/app/oracle/product/10.2.0/db

System name:     Linux

Node name:       webdg2

Release:         2.6.18-92.el5

Version:         #1 SMP Tue Apr 29 13:16:15 EDT 2008

Machine:         x86_64

Instance name: htzq2

Redo thread mounted by this instance: 2

Oracle process number: 30

Unix process pid: 19678, image: Oracle@webdg2 (TNS V1-V3)

=====================
www.linuxidc.com
=====================

PARSING IN CURSOR #2 len=290 dep=0 uid=0 oct=3 lid=0 tim=1307409201601477 hv=1905048613 ad='c9e347d8'

SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',         6,'big integer', 'un

known') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%_pga_max_size%') ORDER BY NAME_

COL_PLUS_SHOW_PARAM,ROWNUM

END OF STMT

PARSE #2:c=4999,e=4042,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1307409201601471

EXEC #2:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1307409201601611

FETCH #2:c=4999,e=5583,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1307409201607254

bash-3.2$ exit

exit

結果顯示 ,show parameter 就是查詢一下 V$PARAMETER 視圖。

接著，我們就分析 V$PARAMETER 視圖的定義

SQL> SELECT * FROM v$fixed_view_definition WHERE 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 , ISDEPRECAT
ED, DESCRIPTION, UPDATE_COMMENT, HASH  from GV$PARAMETER where inst_id = USERENV
('Instance')

SQL> r

   1* SELECT * FROM v$fixed_view_definition WHERE 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,'I

MMEDIATE',2,'DEFERRED',                                   3,'IMMEDIATE','FALSE'),

   decode(bitand(ksppiflg,4),4,'FALSE',                                      decod

e(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, ksppstcmn

t, ksppihash   from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and   ((translat

e(ksppinm,'_','#') not like '##%') and     ((translate(ksppinm,'_','#') not like

'#%')       or (ksppstdf = 'FALSE') or       (bitand(ksppstvf,5) > 0)))

V$PARAMETER 的定義主要來源于 GV$PARAMETER ， 兩個都是同義詞，來源于v_$parameter和gv_$parameter。

這個定義語句中最關鍵部分 (translate(ksppinm,'_','#') not like '#%') 的條件。就是它把開頭為 "_"  的隱含參數給過濾掉了。因為加了 ksppstdf = 'FALSE'  的條件，這保證如果已經手動改過隱含參數， show parameter 時會顯示已經修改的隱含參數。

最后，重建 gv$parameter視圖

create or replace view gv_$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 sys.x$ksppi x, sys.x$ksppcv y
 where (x.indx = y.indx) and (translate(ksppinm, '_', '#') not like '##%');

有一個疑問:

show parameter 時查的是同義詞gv$parameter。該同義詞無法刪除重建。

SQL> create synonym GV$PARAMETER for GV_$PARAMETER;
create synonym GV$PARAMETER for GV_$PARAMETER
               *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> drop synonym GV$PARAMETER;
drop synonym GV$PARAMETER
             *
ERROR at line 1:

ORA-02030: can only select from fixed tables/views

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]