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

我们平时访问的动态性能视图表其实是真正视图通过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博客 ” ，链接：http://blog.itpub.net/30236014/viewspace-1674026/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30236014/viewspace-1674026/