v$和gv$来源

本篇文章以v$parameter和gv$parameter为例做讲解
1、查看v$parameter对象类型，并查看其创建sql语句

SELECT * FROM all_objects a WHERE a.object_name='V$PARAMETER';
select  dbms_metadata.get_ddl(object_type=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC') from dual;
--V$PARAMETER SYNONYN
CREATE OR REPLACE PUBLIC SYNONYM "V$PARAMETER" FOR "SYS"."V_$PARAMETER"

2、查看V_$PARAMETER对象类型，并查看其创建语句

SELECT *　FROM all_objects a WHERE a.object_name='V_$PARAMETER';
select  dbms_metadata.get_ddl(object_type=>'VIEW',NAME=>'V_$PARAMETER',SCHEMA=>'SYS') from dual;
--V_$PARAMETER VIEW
  CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" 
  ("NUM", "NAME", "TYPE", "VALUE", "DISPLAY_VALUE", "ISDEFAULT", "ISSES_MODIFIABLE", 
  "ISSYS_MODIFIABLE", "ISINSTANCE_MODIFIABLE", "ISMODIFIED", "ISADJUSTED", "ISDEPRECATED", 
  "ISBASIC", "DESCRIPTION", "UPDATE_COMMENT", "HASH") AS 
  SELECT "NUM",
         "NAME",
         "TYPE",
         "VALUE",
         "DISPLAY_VALUE",
         "ISDEFAULT",
         "ISSES_MODIFIABLE",
         "ISSYS_MODIFIABLE",
         "ISINSTANCE_MODIFIABLE",
         "ISMODIFIED",
         "ISADJUSTED",
         "ISDEPRECATED",
         "ISBASIC",
         "DESCRIPTION",
         "UPDATE_COMMENT",
         "HASH"
    FROM V$PARAMETER;

3、查看V$PARAMETER类型，并查看其创建语句

SELECT * FROM v$fixed_table a WHERE a.name='V$PARAMETER';
SELECT * FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_name='V$PARAMETER';
 --V$PARAMETER VIEW
    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')

4、查看GV$PARAMETER类型，及其创建sql语句

SELECT * FROM v$fixed_table a WHERE a.name='GV$PARAMETER';
SELECT * FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_name='GV$PARAMETER';
--GV$PARAMETER  VIEW
     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)))

5、查看gv$parameter同义词和gv_$parameter视图

--GV$PARAMETER  SYNONYM
CREATE OR REPLACE PUBLIC SYNONYM "GV$PARAMETER" FOR "SYS"."GV_$PARAMETER"
--GV_$PRAMETER VIEW
CREATE OR REPLACE FORCE VIEW "SYS"."GV_$PARAMETER" 
("INST_ID", "NUM", "NAME", "TYPE", "VALUE", "DISPLAY_VALUE", "ISDEFAULT", 
"ISSES_MODIFIABLE", "ISSYS_MODIFIABLE", "ISINSTANCE_MODIFIABLE", "ISMODIFIED",
 "ISADJUSTED", "ISDEPRECATED", "ISBASIC", "DESCRIPTION", "UPDATE_COMMENT", "HASH") AS 
  SELECT "INST_ID",
         "NUM",
         "NAME",
         "TYPE",
         "VALUE",
         "DISPLAY_VALUE",
         "ISDEFAULT",
         "ISSES_MODIFIABLE",
         "ISSYS_MODIFIABLE",
         "ISINSTANCE_MODIFIABLE",
         "ISMODIFIED",
         "ISADJUSTED",
         "ISDEPRECATED",
         "ISBASIC",
         "DESCRIPTION",
         "UPDATE_COMMENT",
         "HASH"
    FROM GV$PARAMETER

6、总结
x$(table)–>gv$(view)–>v$(view)–>v_$(view)–>v$(SYNONYM)
x$(table)–>gv$(view)–>gv_$(view)–>gv$(synonym)
1）sys的fixed table x$
2）sys的fixed view: GV$
3）sys的fixed view: V$
4）sys的普通view: V_$
5）public的synonym: v$
6) sys的fixed view: gv_$
7) public的synonym: gv$
如果用sys访问v$, 就是3=>2=>1,other user访问v$, 就是5=>4=>3=>2=>1.
如果用sys访问gv$, 就是6=>2=>1,other user访问gv$, 就是7=>6=>2=>1.