用show parameter也能显示隐含参数

Oracle 数据库启动时用到的数据库初始化参数，我们通常是在 sqlplus 中使用 show parameter 命令来获取。但这里看到只会是一些公开的参数，和已经修改的隐含参数。 Oracle 还有一批隐含参数，在 debug 或者实现某些功能时非常有用。

在网上，你可以很方便的查到如何查询这些隐含参数。但是，如果我还想继续使用 show parameter 方法显示隐含参数呢？这该如何实现？

（miki西游 @mikixiyou的文档：原文链接: http://mikixiyou.iteye.com/blog/1550296 ）

我在这里介绍一个方法，来实现这个目标。介绍这个方法的目的不仅仅是为了实现 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 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