v$和gv$

来源：互联网发布：ui设计知乎编辑：程序博客网时间：2024/04/17 04:46

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

SELECT* FROMall_objects a WHEREa.object_name='V$PARAMETER';
select dbms_metadata.get_ddl(object_type=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC')fromdual;
--V$PARAMETER SYNONYN
CREATEOR REPLACE PUBLIC SYNONYM "V$PARAMETER"FOR "SYS"."V_$PARAMETER"

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

SELECT*　FROMall_objects a WHEREa.object_name='V_$PARAMETER';
select dbms_metadata.get_ddl(object_type=>'VIEW',NAME=>'V_$PARAMETER',SCHEMA=>'SYS')fromdual;
--V_$PARAMETER VIEW
  CREATEOR 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"
    FROMV$PARAMETER;

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

SELECT* FROMv$fixed_table a WHEREa.name='V$PARAMETER';
SELECT* FROMV$FIXED_VIEW_DEFINITION WHEREVIEW_name='V$PARAMETER';
 --V$PARAMETER VIEW
    SELECTNUM,
           NAME,
           TYPE,
           VALUE,
           DISPLAY_VALUE,
           ISDEFAULT,
           ISSES_MODIFIABLE,
           ISSYS_MODIFIABLE,
           ISINSTANCE_MODIFIABLE,
           ISMODIFIED,
           ISADJUSTED,
           ISDEPRECATED,
           ISBASIC,
           DESCRIPTION,
           UPDATE_COMMENT,
           HASH
      FROMGV$PARAMETER
     WHEREINST_ID = USERENV('Instance')

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

SELECT* FROMv$fixed_table a WHEREa.name='GV$PARAMETER';
SELECT* FROMV$FIXED_VIEW_DEFINITION WHEREVIEW_name='GV$PARAMETER';
--GV$PARAMETER  VIEW
     SELECTX.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
       FROMX$KSPPI X, X$KSPPCV Y
      WHERE(X.INDX = Y.INDX)
        ANDBITAND(KSPPIFLG, 268435456) = 0
        AND((TRANSLATE(KSPPINM, '_','#')NOTLIKE '##%')AND
            ((TRANSLATE(KSPPINM,'_','#')NOTLIKE '#%')OR
            (KSPPSTDF = 'FALSE')OR(BITAND(KSPPSTVF, 5) > 0)))

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

--GV$PARAMETER  SYNONYM
CREATEOR REPLACE PUBLIC SYNONYM "GV$PARAMETER"FOR "SYS"."GV_$PARAMETER"
--GV_$PRAMETER VIEW
CREATEOR 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"
    FROMGV$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.