v$和gv$来源

来源：互联网发布：淘宝卖家几点上班编辑：程序博客网时间：2024/04/19 09:54

本篇文章以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

CREATEORREPLACEPUBLICSYNONYM "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

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_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),

'IMMEDIATE',

'DEFERRED',

'IMMEDIATE',

'FALSE'),

DECODE(BITAND(KSPPIFLG, 4),

'FALSE',

DECODE(BITAND(KSPPIFLG / 65536, 3), 0, 'FALSE', 'TRUE')),

DECODE(BITAND(KSPPSTVF, 7),

'MODIFIED',

'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

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

--GV_$PRAMETER VIEW

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",