oracle show parameter,用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)

=====================

=====================

PARSING IN CURSOR #2 len=290 dep=0 uid=0 ct=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