[20181004]12c dba_source视图定义.txt

[20181004]12c dba_source视图定义.txt --//12c 引入插件数据库,dba_source定义与11g存在明显的不同. --//昨天测试报错: SCOTT@test01p> select text from dba_source as of scn 29431442 where owner=user and name='RETURN_SOMETHING' order by LINE asc; select text from dba_source as of scn 29431442 where owner=user and name='RETURN_SOMETHING' order by LINE asc                  * ERROR at line 1: ORA-01031: insufficient privileges --//简单探究为什么? 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0 CREATE OR REPLACE FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 1 BEGIN     IF ( i = 1 ) THEN         RETURN 'red';     ELSE         RETURN 'blue';     END IF; END return_something; / SCOTT@test01p> select current_scn,sysdate from v$database ; CURRENT_SCN SYSDATE ----------- -------------------    29436417 2018-10-03 21:44:37 2.测试: SCOTT@test01p> select object_id,data_object_id from dba_objects where object_name='RETURN_SOMETHING';  OBJECT_ID DATA_OBJECT_ID ---------- --------------     108189 SCOTT@test01p> column SOURCE format a100 SCOTT@test01p> select * from sys.source$ where obj#=108189;       OBJ#       LINE SOURCE ---------- ---------- -----------------------------------------------     108189          1 FUNCTION "RETURN_SOMETHING" (     108189          2     i NUMBER     108189          3 ) RETURN VARCHAR2 AS     108189          4 -- Version 1     108189          5 BEGIN     108189          6     IF ( i = 1 ) THEN     108189          7         RETURN 'red';     108189          8     ELSE     108189          9         RETURN 'blue';     108189         10     END IF;     108189         11 END return_something; 11 rows selected. --//OK正确. --//建立函数. CREATE OR REPLACE FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; / SCOTT@test01p> select * from sys.source$ as of scn 29436417 where obj#=108189; select * from sys.source$ as of scn 29436417 where obj#=108189                   * ERROR at line 1: ORA-01031: insufficient privileges --//奇怪scott用户无法查询. SCOTT@test01p> select * from sys.source$  where obj#=108189;       OBJ#       LINE SOURCE ---------- ---------- -------------------------------------------------     108189          1 FUNCTION "RETURN_SOMETHING" (     108189          2     i NUMBER     108189          3 ) RETURN VARCHAR2 AS     108189          4 -- Version 2     108189          5 BEGIN     108189          6     IF ( i = 1 ) THEN     108189          7         RETURN 'oans';     108189          8     ELSE     108189          9         RETURN 'zwoa';     108189         10     END IF;     108189         11 END return_something; 11 rows selected. --//换成sys用户查询: SYS@test01p> select * from sys.source$ as of scn 29436417 where obj#=108189;       OBJ#       LINE SOURCE ---------- ---------- ----------------------------------------------------------------------     108189          1 FUNCTION "RETURN_SOMETHING" (     108189          2     i NUMBER     108189          3 ) RETURN VARCHAR2 AS     108189          4 -- Version 1     108189          5 BEGIN     108189          6     IF ( i = 1 ) THEN     108189          7         RETURN 'red';     108189          8     ELSE     108189          9         RETURN 'blue';     108189         10     END IF;     108189         11 END return_something; 11 rows selected. --//为什么dba_source查询不到呢? 3.继续测试: SYS@test01p> select text_vc c100 from dba_views where view_name='DBA_SOURCE'; C100 --------------------------------------------------------------------------------- select OWNER, NAME, TYPE, LINE, TEXT, ORIGIN_CON_ID from INT$DBA_SOURCE --//基于INT$DBA_SOURCE视图. SYS@test01p> select text_vc c100 from dba_views where view_name='INT$DBA_SOURCE'; C100 ------------------------------------------------------------------------------------------------ select u.name, o.name, decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',                11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',                22, 'LIBRARY', 87, 'ASSEMBLY', 'UNDEFINED'), o.type#, s.line, s.source, decode(bitand(o.flags, 196608), 65536, 1, 131072, 1, 0), to_number(sys_context('USERENV', 'CON_ID')) from sys."_CURRENT_EDITION_OBJ" o, sys.source$ s, sys.user$ u where o.obj# = s.obj#   and o.owner# = u.user#   and ( o.type# in (7, 8, 9, 11, 12, 14, 22) OR        ( o.type# = 13 AND o.subname is null)) union all select u.name, o.name, 'JAVA SOURCE', o.type#, s.joxftlno, s.joxftsrc, decode(bitand(o.flags, 196608), 65536, 1, 131072, 1, 0), to_number(sys_context('USERENV', 'CON_ID')) from sys."_CURRENT_EDITION_OBJ" o, x$joxscd s, sys.user$ u where o.obj# = s.joxftobn   and o.owner# = u.user#   and o.type# = 28 --//可以发现INT$DBA_SOURCE除了访问sys.source$,第2部分访问 x$joxscd ,这也就是为什么查询dba_source不对的原因, --//因为里面有x$joxscd表.可以发现这部分内容属于JAVA SOURCE. SYS@test01p> select text c100  from dba_source as of scn 29436417 where owner='SCOTT' and name='RETURN_SOMETHING'; C100 ---------------------------------------------------------------------------------------------------- FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; 11 rows selected. --//新版本.无法使用as of scn方式查询旧版本. SYS@test01p> select * from sys.source$ as of scn 29436417 where obj#=108189;       OBJ#       LINE SOURCE ---------- ---------- -----------------------------------------------------------     108189          1 FUNCTION "RETURN_SOMETHING" (     108189          2     i NUMBER     108189          3 ) RETURN VARCHAR2 AS     108189          4 -- Version 1     108189          5 BEGIN     108189          6     IF ( i = 1 ) THEN     108189          7         RETURN 'red';     108189          8     ELSE     108189          9         RETURN 'blue';     108189         10     END IF;     108189         11 END return_something; 11 rows selected. --//旧版本.直接查询sys.source$使用as of scn方式ok. --//可以通过一个简单的方法验证: CREATE VIEW sys.dba_sourcez (    OWNER   ,NAME   ,TYPE   ,LINE   ,TEXT   ,ORIGIN_CON_ID ) AS    SELECT u.name          ,o.name          ,DECODE           (              o.type#             ,7, 'PROCEDURE'             ,8, 'FUNCTION'             ,9, 'PACKAGE'             ,11, 'PACKAGE BODY'             ,12, 'TRIGGER'             ,13, 'TYPE'             ,14, 'TYPE BODY'             ,22, 'LIBRARY'             ,87, 'ASSEMBLY'             ,'UNDEFINED'           )          --,o.type#          ,s.line          ,s.source          /*          ,DECODE           (              BITAND (o.flags, 196608)             ,65536, 1             ,131072, 1             ,0           ) */          ,TO_NUMBER (SYS_CONTEXT ('USERENV', 'CON_ID')) ORIGIN_CON_ID      FROM sys."_CURRENT_EDITION_OBJ" o, sys.source$ s, sys.user$ u     WHERE     o.obj# = s.obj#           AND o.owner# = u.user#           AND (   o.type# IN (7, 8, 9, 11, 12, 14, 22)                OR (o.type# = 13 AND o.subname IS NULL)); --//以sys用户建立视图dba_sourcez. SYS@test01p> select text c100  from dba_sourcez as of scn 29436417 where owner='SCOTT' and name='RETURN_SOMETHING'; C100 --------------------------------------------- FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 1 BEGIN     IF ( i = 1 ) THEN         RETURN 'red';     ELSE         RETURN 'blue';     END IF; END return_something; 11 rows selected. --//OK这样正确. SYS@test01p> select text c100  from dba_source as of scn 29436417 where owner='SCOTT' and name='RETURN_SOMETHING'; C100 -------------------------------------------- FUNCTION "RETURN_SOMETHING" (     i NUMBER ) RETURN VARCHAR2 AS -- Version 2 BEGIN     IF ( i = 1 ) THEN         RETURN 'oans';     ELSE         RETURN 'zwoa';     END IF; END return_something; 11 rows selected. --//正是dba_source包含X$,导致查询即使使用as of scn 29436417也仅仅查询到当前的版本.