[20241010]视图定义查询问题.txt

--//别人问的问题，在pdb下使用tpt的v2.sql脚本无法查询视图定义，测试验证为什么。

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
SYS@book01p> @ v2 DBA_EXPRESSION_STATISTICS
Show SQL text of views matching "DBA_EXPRESSION_STATISTICS"...

V_OWNER                   VIEW_NAME                      TEXT
------------------------- ------------------------------ -------------------------------------------------
SYS                       DBA_EXPRESSION_STATISTICS
no rows selected

--//确实看不到该视图的定义。查看v2.sql脚本，发现查询的是text字段。
select owner v_owner, view_name, text from dba_views
.....

$ ocol.sh sys.DBA_views text
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    3      TEXT_LENGTH                              NUMBER
    4      TEXT                                     LONG
    5      TEXT_VC                                  VARCHAR2(4000)
    6      TYPE_TEXT_LENGTH                         NUMBER
    7      TYPE_TEXT                                VARCHAR2(4000)
    8      OID_TEXT_LENGTH                          NUMBER
    9      OID_TEXT                                 VARCHAR2(4000)
--//text字段类型是long类型。

SYS@book01p> select text c100 from dba_views where view_name='DBA_EXPRESSION_STATISTICS';
C100
----------------------------------------------------------------------------------------------------

--//确实没有！

SYS@book01p> select text_vc c100 from dba_views where view_name='DBA_EXPRESSION_STATISTICS';
C100
----------------------------------------------------------------------------------------------------
select u.name, o.name, v.expid, decode(v.snapshot_id, 0, 'CUMULATIVE',
                                                      1, 'LATEST',
                                                      'WINDOW'),
       v.evaluation_count, h.fixed_cost,
       v.dynamic_cost, h.text, h.ctime, v.last_modified
from obj$ o, user$ u, exp_head$ h,
  -- latest expressions
  ((select nvl(ds.exp_id, ms.expid) expid,
           nvl(ds.objn, ms.objnum) objn, 1 snapshot_id,
           (nvl(ds.eval_count, 0) + nvl(ms.evalcnt, 0)) evaluation_count,
           nvl(ms.dyncost, ds.dynamic_cost) dynamic_cost,
           decode(ms.expid, null, ds.last_modified, systimestamp) last_modified
  from (select * from exp_stat$ where snapshot_id = 1) ds full outer join
  (select expid, objnum, dyncost, evalcnt
   from gv$exp_stats where evalcnt > 0) ms
  on ds.exp_id = ms.expid and ds.objn = ms.objnum)
  union all
  -- window expression if window capture mode is OPEN
  (select nvl(ds.exp_id, ms.expid) expid,
           nvl(ds.objn, ms.objnum) objn, 2 snapshot_id,
           (nvl(ds.eval_count, 0) + nvl(ms.evalcnt, 0)) evaluation_count,
           nvl(ms.dyncost, ds.dynamic_cost) dynamic_cost,
           decode(ms.expid, null, ds.last_modified, systimestamp) last_modified
  from (select * from exp_stat$ where snapshot_id = 2) ds full outer join
  (select expid, objnum, dyncost, evalcnt
   from gv$exp_stats where evalcnt > 0) ms
  on ds.exp_id = ms.expid and ds.objn = ms.objnum
  where dbms_stats_internal.check_window_capture_mode() = 1)
  union all
  -- window expressions if window capture mode is CLOSED
  (select exp_id expid, objn, snapshot_id, eval_count evaluation_count,
          dynamic_cost, last_modified
   from exp_stat$ where snapshot_id = 2 and
        dbms_stats_internal.check_window_capture_mode() = 0)
  union all
  -- cumulative expressions
  (select exp_id expid, objn, snapshot_id, eval_count evaluation_count,
          dynamic_cost, last_modified
  from exp_stat$ where snapshot_id = 0)) v
where v.objn = o.obj# and v.expid = h.exp_id and o.owner# = u.user#
  and o.subname is null
  -- check for dba privileges
  and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL

--//很明显现在的oracle版本已经不在text保存定义内容。

--//而以sys用户登录：
SYS@book> select text c100 from dba_views where view_name='DBA_EXPRESSION_STATISTICS' ;
C100
----------------------------------------------------------------------------------------------------
select u.name, o.name, v.expid, decode(v.snapshot_id, 0, 'CUMULATIVE',
                                                      1, 'LATEST',
                                                      'WINDOW'),
       v.evaluation_count, h.fixed_cost,
       v.dynamic_cost, h.text, h.ctime, v.last_modified
from obj$ o, user$ u, exp_head$ h,
  -- latest expressions
  ((select nvl(ds.exp_id, ms.expid) expid,
           nvl(ds.objn, ms.objnum) objn, 1 snapshot_id,
           (nvl(ds.eval_count, 0) + nvl(ms.evalcnt, 0)) evaluation_count,
           nvl(ms.dyncost, ds.dynamic_cost) dynamic_cost,
           decode(ms.expid, null, ds.last_modified, systimestamp) last_modified
  from (select * from exp_stat$ where snapshot_id = 1) ds full outer join
  (select expid, objnum, dyncost, evalcnt
   from gv$exp_stats where evalcnt > 0) ms
  on ds.exp_id = ms.expid and ds.objn = ms.objnum)
  union all
  -- window expression if window capture mode is OPEN
  (select nvl(ds.exp_id, ms.expid) expid,
           nvl(ds.objn, ms.objnum) objn, 2 snapshot_id,
           (nvl(ds.eval_count, 0) + nvl(ms.evalcnt, 0)) evaluation_count,
           nvl(ms.dyncost, ds.dynamic_cost) dynamic_cost,
           decode(ms.expid, null, ds.last_modified, systimestamp) last_modified
  from (select * from exp_stat$ where snapshot_id = 2) ds full outer join
  (select expid, objnum, dyncost, evalcnt
   from gv$exp_stats where evalcnt > 0) ms
  on ds.exp_id = ms.expid and ds.objn = ms.objnum
  where dbms_stats_internal.check_window_capture_mode() = 1)
  union all
  -- window expressions if window capture mode is CLOSED
  (select exp_id expid, objn, snapshot_id, eval_count evaluation_count,
          dynamic_cost, last_modified
   from exp_stat$ where snapshot_id = 2 and
        dbms_stats_internal.check_window_capture_mode() = 0)
  union all
  -- cumulative expressions
  (select exp_id expid, objn, snapshot_id, eval_count evaluation_count,
          dynamic_cost, last_modified
  from exp_stat$ where snapshot_id = 0)) v
where v.objn = o.obj# and v.expid = h.exp_id and o.owner# = u.user#
  and o.subname is null
  -- check for dba privileges
  and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL

--//而以sys用户登录能够查询到相关定义。也就是以pdb登录使用原来的脚本查询视图定义查询不到信息。
--//而查询text_vc的缺点是仅仅显示4000字符。

--//无法写成如下，因为text是long类型。
SYS@book01p> select decode(text,null,text_vc) text from dba_views where view_name='DBA_EXPRESSION_STATISTICS' ;
select decode(text,null,text_vc) text from dba_views where view_name='DBA_EXPRESSION_STATISTICS'
              *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

--//直接改写如下：
$ cat v2.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

col view_name for a30
col text for a100 word_wrap
col text_vc for a100 word_wrap
col v_owner for a25

prompt Show SQL text of views matching "&1"...

select owner v_owner, view_name, text,text_vc from dba_views
where
  upper(view_name) LIKE
        upper(CASE
          WHEN INSTR('&1','.') > 0 THEN
              SUBSTR('&1',INSTR('&1','.')+1)
          ELSE
              '&1'
          END
             ) ESCAPE '\'
AND owner LIKE
    CASE WHEN INSTR('&1','.') > 0 THEN
      UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
    ELSE
      user
    END ESCAPE '\'
/

select view_name, view_definition text from v$fixed_View_definition where upper(view_name) like upper('&1');

--//不知道为什么我自己内心特别不喜欢pdb，搞得oracle异常复杂。