以前查询视图的定义,总是通过v$fixed_view_definition来查找,不过有时候也蛮郁闷的,比如有的视图嵌套好几层,这样查询起来那叫一个痛苦啊,比如我们要查询V$PARAMETER的定义.则我们要执行下列查询.

SQL> select view_name,VIEW_DEFINITION from v$fixed_view_definition where view_name = 'V$PARAMETER';
VIEW_NAME
------------------------------
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------
V$PARAMETER
select    NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED ,
ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id = USERENV('Instance')

找出V$PARAMETER的定义,来自于GV$PARAMETER,然后查找GV$PARAMETER的定义

SQL> select view_name,VIEW_DEFINITION from v$fixed_view_definition where view_name ='GV$PARAMETER';
VIEW_NAME
------------------------------
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------
GV$PARAMETER
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'), decode(bitand(ksppiflg/655
36,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(bita
nd(ksppstvf,2),2,'TRUE','FALSE'), decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash from x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and ((translate(ksppinm,'_','#') not like '##%') and      ((translate(ksppinm,'_','#') not like '#%')       or (ksppstdf = 'FAL
SE') or      (bitand(ksppstvf,5) > 0)))

最终找到了x$ksppi ,x$ksppcv两个表.试着想想要是那个开发把视图嵌套个很多层,你要找到底层视图的定义.是不是会很麻烦啊,还好,oracle考虑到了这个问题, 它有个隐含参数"_dump_qbc_tree"帮我们解决了这样一个问题,不过这个参数只有在oracle 10gR2版本上才有呢.以下是这个参数的说明.

Oracle 10.2.0:
Parameter Name:    _dump_qbc_tree
Description:    dump top level query parse tree to trace
Type:    NUMBER     Obsoleted:    FALSE
Can ALTER SESSION:    TRUE     Can ALTER SYSTEM:    FALSE

下面是一个示例:

SQL> alter session set "_dump_qbc_tree"=1;
Session altered.

SQL> select * from V$PARAMETER where rownum<=1;
----------------内容省略------------------

SQL> alter session set "_dump_qbc_tree"=0;
Session altered.

然后我们可以查dump的文件,内容如下

[oracle@www udump]$ more jane_ora_7062.trc
/opt/oracle/admin/Jane/udump/jane_ora_7062.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/10.2.0/db_1
System name:    Linux
Node name:
Release:    2.6.18-194.el5
Version:    #1 SMP Fri Apr 2 14:58:35 EDT 2010
Machine:    i686
Instance name: Jane
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 7062, image: oracle@ (TNS V1-V3)

*** 2010-08-29 21:45:56.050
*** ACTION NAME:() 2010-08-29 21:45:56.050
*** MODULE NAME:(sqlplus@ (TNS V1-V3)) 2010-08-29 21:45:56.050
*** SERVICE NAME:(SYS$USERS) 2010-08-29 21:45:56.050
*** SESSION ID:(137.122) 2010-08-29 21:45:56.050
QCSDMP: -------------------------------------------------------
QCSDMP: SELECT: (qbc=0xb7f7eb60)
QCSDMP:    V$PARAMETER.NUM
QCSDMP:    V$PARAMETER.NAME
QCSDMP:    V$PARAMETER.TYPE
QCSDMP:    V$PARAMETER.VALUE
QCSDMP:    V$PARAMETER.DISPLAY_VALUE
QCSDMP:    V$PARAMETER.ISDEFAULT
QCSDMP:    V$PARAMETER.ISSES_MODIFIABLE
QCSDMP:    V$PARAMETER.ISSYS_MODIFIABLE
QCSDMP:    V$PARAMETER.ISINSTANCE_MODIFIABLE
QCSDMP:    V$PARAMETER.ISMODIFIED
QCSDMP:    V$PARAMETER.ISADJUSTED
QCSDMP:    V$PARAMETER.ISDEPRECATED
QCSDMP:    V$PARAMETER.DESCRIPTION
QCSDMP:    V$PARAMETER.UPDATE_COMMENT
QCSDMP:    V$PARAMETER.HASH
QCSDMP: FROM:
QCSDMP:    .V$PARAMETER
QCSDMP:      VQB:
QCSDMP:        SELECT: (qbc=0xb7f76874)
QCSDMP:          .NUM
QCSDMP:          .NAME
QCSDMP:          .TYPE
QCSDMP:          .VALUE
QCSDMP:          .DISPLAY_VALUE
QCSDMP:          .ISDEFAULT
QCSDMP:          .ISSES_MODIFIABLE
QCSDMP:          .ISSYS_MODIFIABLE
QCSDMP:          .ISINSTANCE_MODIFIABLE
QCSDMP:          .ISMODIFIED
QCSDMP:          .ISADJUSTED
QCSDMP:          .ISDEPRECATED
QCSDMP:          .DESCRIPTION
QCSDMP:          .UPDATE_COMMENT
QCSDMP:          .HASH
QCSDMP:        FROM:
QCSDMP:          .GV$PARAMETER
QCSDMP:            VQB:
QCSDMP:              SELECT: (qbc=0xb7f75474)
QCSDMP:                X.INST_ID
QCSDMP:                . (NUM) (opntyp=2 pndty=0)
QCSDMP:                .KSPPINM (NAME)
QCSDMP:                .KSPPITY (TYPE)
QCSDMP:                .KSPPSTVL (VALUE)
QCSDMP:                .KSPPSTDVL (DISPLAY_VALUE)
QCSDMP:                .KSPPSTDF (ISDEFAULT)
QCSDMP:                . (ISSES_MODIFIABLE) (opntyp=2 pndty=0)
QCSDMP:                . (ISSYS_MODIFIABLE) (opntyp=2 pndty=0)
QCSDMP:                . (ISINSTANCE_MODIFIABLE) (opntyp=2 pndty=0)
QCSDMP:                . (ISMODIFIED) (opntyp=2 pndty=0)
QCSDMP:                . (ISADJUSTED) (opntyp=2 pndty=0)
QCSDMP:                . (ISDEPRECATED) (opntyp=2 pndty=0)
QCSDMP:                .KSPPDESC (DESCRIPTION)
QCSDMP:                .KSPPSTCMNT (UPDATE_COMMENT)
QCSDMP:                .KSPPIHASH (HASH)
QCSDMP:              FROM:
QCSDMP:                .X$KSPPCV (Y)
QCSDMP:                .X$KSPPI (X)

如这个dump出来的文件所示,我们从上往下看,首先是我们在sqlplus中执行的sql语句,如果该视图有上一层定义,它会显示一个VQB出来,然后就列出了这个视图上一层的定义,依次类推,直到寻找到视图的最底层的定义为止.对于多层嵌套的视图查询底层的定义,这个参数还真是提高了不少效率.就算以后你遇到一个开发,它把视图嵌套很多层,你也能迅速的找到最底层的表.

ITPub博客

利用_dump_qbc_tree隐藏参数查询底层视图的definition