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