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.