[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也僅僅查詢到當前的版本.