本篇文章以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.
SELECT DBMS_METADATA.get_ddl(‘VIEW’,’TAB’,’SYS’) FROM DUAL;