show parameter数据来源

+++使用10046跟踪show parameter命令的数据来源
SQL> oradebug setmypid
Statement processed.

SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1504M
sga_target                           big integer 0
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_11725.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

+++格式化一下trace文件
[oracle@goolen ~]$ tkprof /opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_11725.trc ./1.txt

+++trace 文件的部分内容，找到一个sql
SQL ID: 7cfz5wy9caaf4
Plan Hash: 4015672053
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

+++查看V$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> select dbms_metadata.get_ddl(object_type=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC') from dual;

DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC
--------------------------------------------------------------------------------

  CREATE OR REPLACE PUBLIC SYNONYM "V$PARAMETER" FOR "SYS"."V_$PARAMETER"

SQL> set lines 200
SQL> select * from dba_synonyms where synonym_name='V$PARAMETER';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------------------------------
PUBLIC                         V$PARAMETER                    SYS                            V_$PARAMETER

SQL> select OWNER,object_name,object_type from dba_objects where object_name='V_$PARAMETER';

OWNER                          OBJECT_NAME                         OBJECT_TYPE
------------------------------ ----------------------------------- -------------------
SYS                            V_$PARAMETER                        VIEW

SQL> set long 99999
SQL> select text from dba_views where view_name='V_$PARAMETER';

TEXT
--------------------------------------------------------------------------------
select "NUM","NAME","TYPE","VALUE","DISPLAY_VALUE","ISDEFAULT","ISSES_MODIFIABLE
","ISSYS_MODIFIABLE","ISINSTANCE_MODIFIABLE","ISMODIFIED","ISADJUSTED","ISDEPREC
ATED","ISBASIC","DESCRIPTION","UPDATE_COMMENT","HASH" from v$parameter

SQL> select dbms_metadata.get_ddl(object_type=>'VIEW',NAME=>'V_$PARAMETER',SCHEMA=>'SYS') from dual;

DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'VIEW',NAME=>'V_$PARAMETER',SCHEMA=>'SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" ("NUM",
 "NAME", "TYPE", "VALUE", "DISPLAY_VALUE
", "ISDEFAULT", "ISSES_MODIFIABLE", "ISS
YS_MODIFIABLE", "ISINSTANCE_MODIFIABLE",
 "ISMODIFIED", "ISADJUSTED", "ISDEPRECAT
ED", "ISBASIC", "DESCRIPTION", "UPDATE_C
OMMENT", "HASH") AS
  select "NUM","NAME","TYPE","VALUE","DISPLAY_VALUE","ISDEF
AULT","ISSES_MODIFIABLE","ISSYS_MODIFIAB
LE","ISINSTANCE_MODIFIABLE","ISMODIFIED"
,"ISADJUSTED","ISDEPRECATED","ISBASIC","
DESCRIPTION","UPDATE_COMMENT","HASH" fro
m v$parameter

SQL> select view_definition from v$fixed_view_definition where view_name='V$PARAMETER';

VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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')

SQL> select view_definition from v$fixed_view_definition where view_name='GV$PARAMETER';

VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)))

+++ 可以看到，数据来源于基表x$ksppi , x$ksppcv