v$和gv$来源

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

SELECT*FROMall_objectsaWHEREa.object_name='V$PARAMETER';

selectdbms_metadata.get_ddl(object_type=>'SYNONYM',NAME=>'V$PARAMETER',SCHEMA=>'PUBLIC')fromdual;

--V$PARAMETERSYNONYN

CREATEORREPLACEPUBLICSYNONYM"V$PARAMETER"FOR"SYS"."V_$PARAMETER"

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

SELECT*　FROMall_objectsaWHEREa.object_name='V_$PARAMETER';

selectdbms_metadata.get_ddl(object_type=>'VIEW',NAME=>'V_$PARAMETER',SCHEMA=>'SYS')fromdual;

--V_$PARAMETERVIEW

CREATEORREPLACEFORCEVIEW"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_tableaWHEREa.name='V$PARAMETER';

SELECT*FROMV$FIXED_VIEW_DEFINITIONWHEREVIEW_name='V$PARAMETER';

--V$PARAMETERVIEW

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_tableaWHEREa.name='GV$PARAMETER';

SELECT*FROMV$FIXED_VIEW_DEFINITIONWHEREVIEW_name='GV$PARAMETER';

--GV$PARAMETERVIEW

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$KSPPIX,X$KSPPCVY

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$PARAMETERSYNONYM

CREATEORREPLACEPUBLICSYNONYM"GV$PARAMETER"FOR"SYS"."GV_$PARAMETER"

--GV_$PRAMETERVIEW

CREATEORREPLACEFORCEVIEW"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的fixedtablex$
2）sys的fixedview:GV$
3）sys的fixedview:V$
4）sys的普通view:V_$
5）public的synonym:v$
6)sys的fixedview:gv_$
7)public的synonym:gv$
如果用sys访问v$,就是3=>2=>1,otheruser访问v$,就是5=>4=>3=>2=>1.
如果用sys访问gv$,就是6=>2=>1,otheruser访问gv$,就是7=>6=>2=>1.