我们平时访问的动态性能视图表其实是真正视图通过V_$视图和SYNONYM同义词转变而来。

下面通过实验以平时最常用的V$PARAMETER视图来进一步阐述

第一步：查看关于 V$PARAMETER视图的OWNER 和TYPE，通过dbms_metadata.get_ddl查询创建语句

SQL> select owner,object_name,object_type from dba_objects where object_name like 'V$PARAMETER';

OWNER OBJECT_NAME OBJECT_TYPE

------------------------------ -------------------- -------------------

PUBLIC V$PARAMETER SYNONYM

SQL> select dbms_metadata.get_ddl('SYNONYM','V$PARAMETER','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('SYNONYM','V$PARAMETER','PUBLIC')

--------------------------------------------------------------------------------

CREATE OR REPLACE PUBLIC SYNONYM "V$PARAMETER" FOR "SYS"."V_$PARAMETER"

第二步：同上方法查看V_$PARAMETER的定义

SQL> col object_name for a20

SQL> select object_name,object_type from dba_objects where object_name='V_$PARAMETER';

OBJECT_NAME OBJECT_TYPE

-------------------- -------------------

V_$PARAMETER VIEW

SQL> set long 1000

SQL> select dbms_metadata.get_ddl('VIEW','V_$PARAMETER','SYS') from dual;

DBMS_METADATA.GET_DDL('VIEW','V_$PARAMETER','SYS')

--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."V_$PARAMETER" ("NUM", "NAME", "TYPE", "VAL

UE", "DISPLAY_VALUE", "ISDEFAULT", "ISSES_MODIFIABLE", "ISSYS_MODIFIABLE", "ISIN

STANCE_MODIFIABLE", "ISMODIFIED", "ISADJUSTED", "ISDEPRECATED", "DESCRIPTION", "

UPDATE_COMMENT", "HASH") AS

select "NUM","NAME","TYPE","VALUE","DISPLAY_VALUE","ISDEFAULT","ISSES_MODIFIAB

LE","ISSYS_MODIFIABLE","ISINSTANCE_MODIFIABLE","ISMODIFIED","ISADJUSTED","ISDEPR

ECATED","DESCRIPTION","UPDATE_COMMENT","HASH" from v$parameter

第三步：同上方法

SQL> select view_definition from v$fixed_view_definition where view_name='V$PARAMETER';

VIEW_DEFINITION

--------------------------------------------------------------------------------

select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE

, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECAT

ED, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id = USERENV

('Instance')

第四步：同上

SQL> select view_definition from v$fixed_view_definition where view_name='GV$PARAMETER';

VIEW_DEFINITION

--------------------------------------------------------------------------------

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,'I

MMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),

decode(bitand(ksppiflg,4),4,'FALSE', decod

e(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'), ksppdesc, ksppstcmn

t, ksppihash from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ((translat

e(ksppinm,'_','#') not like '##%') and ((translate(ksppinm,'_','#') not like

'#%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0)))

然而类似于x$ksppi 的X$表则是在数据库启动时，oracle动态创建的。

ITPub博客

动态性能视图创建过程探索

最新文章