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

[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异常复杂。