ITPub博客

首页 > 数据库 > Oracle > [20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txt

[20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txt

原创 Oracle 作者:lfree 时间:2021-04-21 08:38:16 0 删除 编辑

[20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txt

--//今天使用toad的schema brower看表的script,出现如下错误:
ORA-00904 REF invalid identifier.

1.环境:
ZZZZ> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

--//使用toad自带的SQL Tracker跟踪发现执行如下时报错。
Select  *
from   sys.dba_obj_audit_opts
where  (SUBSTRB (alt, 1, 1) in ('-', 'A', 'S')) and
       ((alt <> '-/-') or (aud <> '-/-') or (com <> '-/-') or
        (del <> '-/-') or (gra <> '-/-') or (ind <> '-/-') or
        (ins <> '-/-') or (loc <> '-/-') or (ren <> '-/-') or
        (sel <> '-/-') or (upd <> '-/-') or (ref <> '-/-') or
        (exe <> '-/-') or (rea <> '-/-') or (wri <> '-/-') or (fbk <> '-/-'))
and    object_type in ('TABLE','INDEXTYPE','TYPE')
and    owner = 'XXXX'
and OBJECT_NAME = 'YYYYJ';
--//发现里面存在ref。
--//而对应的语句拿到11g上执行没有问题,执行通过。我开始以为是保留字的原因。

ZZZZ> column KEYWORD format a30
ZZZZ> select * from v$reserved_words where keyword='REF';
KEYWORD                            LENGTH R R R R D     CON_ID
------------------------------ ---------- - - - - - ----------
REF                                     3 N N N N N          0

--//我发现11g下ref也是。
SCOTT@book> select * from v$reserved_words where keyword='REF';
KEYWORD                            LENGTH R R R R D
------------------------------ ---------- - - - - -
REF                                     3 N N N N N

--//再仔细查看19c以上dba_obj_audit_opts视图根本没有ref字段。
ZZZZ> @ desc dba_obj_audit_opts
           Name         Null?    Type
           ------------ -------- --------------
    1      OWNER                 VARCHAR2(128)
    2      OBJECT_NAME           VARCHAR2(128)
    3      OBJECT_TYPE           VARCHAR2(23)
    4      ALT                   VARCHAR2(3)
    5      AUD                   VARCHAR2(3)
    6      COM                   VARCHAR2(3)
    7      DEL                   VARCHAR2(3)
    8      GRA                   VARCHAR2(3)
    9      IND                   VARCHAR2(3)
   10      INS                   VARCHAR2(3)
   11      LOC                   VARCHAR2(3)
   12      REN                   VARCHAR2(3)
   13      SEL                   VARCHAR2(3)
   14      UPD                   VARCHAR2(3)
   15      EXE                   VARCHAR2(3)
   16      CRE                   VARCHAR2(3)
   17      REA                   VARCHAR2(3)
   18      WRI                   VARCHAR2(3)
   19      FBK                   VARCHAR2(3)

--//以下是11g,即使是18c也有ref这个字段:
SCOTT@book>  @ desc dba_obj_audit_opts
           Name         Null?    Type
           ------------ -------- -------------
    1      OWNER                 VARCHAR2(30)
    2      OBJECT_NAME           VARCHAR2(30)
    3      OBJECT_TYPE           VARCHAR2(23)
    4      ALT                   VARCHAR2(3)
    5      AUD                   VARCHAR2(3)
    6      COM                   VARCHAR2(3)
    7      DEL                   VARCHAR2(3)
    8      GRA                   VARCHAR2(3)
    9      IND                   VARCHAR2(3)
   10      INS                   VARCHAR2(3)
   11      LOC                   VARCHAR2(3)
   12      REN                   VARCHAR2(3)
   13      SEL                   VARCHAR2(3)
   14      UPD                   VARCHAR2(3)
   15      REF                   CHAR(3)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   16      EXE                   VARCHAR2(3)
   17      CRE                   VARCHAR2(3)
   18      REA                   VARCHAR2(3)
   19      WRI                   VARCHAR2(3)
   20      FBK                   VARCHAR2(3)
--//很奇怪ref的定义突然跑出来CHAR(3),其它都是VARCHAR2(3)。如果在11g下看dba_obj_audit_opts视图定义可以发现:
.....
   SELECT u.name
         ,o.name
         ,'TABLE'
         ,SUBSTR (t.audit$, 1, 1) || '/' || SUBSTR (t.audit$, 2, 1)
         ,SUBSTR (t.audit$, 3, 1) || '/' || SUBSTR (t.audit$, 4, 1)
         ,SUBSTR (t.audit$, 5, 1) || '/' || SUBSTR (t.audit$, 6, 1)
         ,SUBSTR (t.audit$, 7, 1) || '/' || SUBSTR (t.audit$, 8, 1)
         ,SUBSTR (t.audit$, 9, 1) || '/' || SUBSTR (t.audit$, 10, 1)
         ,SUBSTR (t.audit$, 11, 1) || '/' || SUBSTR (t.audit$, 12, 1)
         ,SUBSTR (t.audit$, 13, 1) || '/' || SUBSTR (t.audit$, 14, 1)
         ,SUBSTR (t.audit$, 15, 1) || '/' || SUBSTR (t.audit$, 16, 1)
         ,SUBSTR (t.audit$, 17, 1) || '/' || SUBSTR (t.audit$, 18, 1)
         ,SUBSTR (t.audit$, 19, 1) || '/' || SUBSTR (t.audit$, 20, 1)
         ,SUBSTR (t.audit$, 21, 1) || '/' || SUBSTR (t.audit$, 22, 1)
         ,'-/-'
         ,                                              /* dummy REF column */
          SUBSTR (t.audit$, 25, 1) || '/' || SUBSTR (t.audit$, 26, 1)
         ,SUBSTR (t.audit$, 27, 1) || '/' || SUBSTR (t.audit$, 28, 1)
         ,SUBSTR (t.audit$, 29, 1) || '/' || SUBSTR (t.audit$, 30, 1)
         ,SUBSTR (t.audit$, 31, 1) || '/' || SUBSTR (t.audit$, 32, 1)
         ,SUBSTR (t.audit$, 23, 1) || '/' || SUBSTR (t.audit$, 24, 1)
     FROM sys.obj$ o, sys.user$ u, sys.tab$ t
    WHERE     o.type# = 2
          AND NOT (o.owner# = 0 AND o.name = '_default_auditing_options_')
          AND (INSTRB (t.audit$, 'S') != 0 OR INSTRB (t.audit$, 'A') != 0)
          AND o.owner# = u.user#
          AND o.obj# = t.obj#
--//太长节选其中一段,实际上ref起分割符的作用。定义的是一个常量,出现类型CHAR(3)就不奇怪了。

ZZZZ> spool 19c.txt
ZZZZ> @ ddl sys.dba_obj_audit_opts
ZZZZ> spool off

SCOTT@book> spool 11g.txt
SCOTT@book> @ ddl sys.dba_obj_audit_opts
SCOTT@book> spool off

--//对比分析:
 $ diff  -Nur 11g.txt 19c.txt
--- 11g.txt     2021-04-20 10:57:21.000000000 +0800
+++ 19c.txt     2021-04-20 10:57:36.000000000 +0800
@@ -1,6 +1,6 @@
-  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_OBJ_AUDIT_OPTS" ("OWNER", "OBJECT_NAME", "OBJECT_TYPE", "A
-LT", "AUD", "COM", "DEL", "GRA", "IND", "INS", "LOC", "REN", "SEL", "UPD", "REF", "EXE", "CRE", "REA
-", "WRI", "FBK") AS
+  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_OBJ_AUDIT_OPTS" ("OWNER", "OBJECT_NAME", "O
+BJECT_TYPE", "ALT", "AUD", "COM", "DEL", "GRA", "IND", "INS", "LOC", "REN", "SEL", "UPD", "EXE", "CR
+E", "REA", "WRI", "FBK") AS
   select u.name, o.name, 'TABLE',
        substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
        substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
@@ -13,7 +13,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
@@ -38,7 +37,6 @@
        substr(v.audit$, 17, 1) || '/' || substr(v.audit$, 18, 1),
        substr(v.audit$, 19, 1) || '/' || substr(v.audit$, 20, 1),
        substr(v.audit$, 21, 1) || '/' || substr(v.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(v.audit$, 25, 1) || '/' || substr(v.audit$, 26, 1),
        substr(v.audit$, 27, 1) || '/' || substr(v.audit$, 28, 1),
        substr(v.audit$, 29, 1) || '/' || substr(v.audit$, 30, 1),
@@ -62,7 +60,6 @@
        substr(s.audit$, 17, 1) || '/' || substr(s.audit$, 18, 1),
        substr(s.audit$, 19, 1) || '/' || substr(s.audit$, 20, 1),
        substr(s.audit$, 21, 1) || '/' || substr(s.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(s.audit$, 25, 1) || '/' || substr(s.audit$, 26, 1),
        substr(s.audit$, 27, 1) || '/' || substr(s.audit$, 28, 1),
        substr(s.audit$, 29, 1) || '/' || substr(s.audit$, 30, 1),
@@ -86,7 +83,6 @@
        substr(p.audit$, 17, 1) || '/' || substr(p.audit$, 18, 1),
        substr(p.audit$, 19, 1) || '/' || substr(p.audit$, 20, 1),
        substr(p.audit$, 21, 1) || '/' || substr(p.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(p.audit$, 25, 1) || '/' || substr(p.audit$, 26, 1),
        substr(p.audit$, 27, 1) || '/' || substr(p.audit$, 28, 1),
        substr(p.audit$, 29, 1) || '/' || substr(p.audit$, 30, 1),
@@ -110,7 +106,6 @@
        substr(p.audit$, 17, 1) || '/' || substr(p.audit$, 18, 1),
        substr(p.audit$, 19, 1) || '/' || substr(p.audit$, 20, 1),
        substr(p.audit$, 21, 1) || '/' || substr(p.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(p.audit$, 25, 1) || '/' || substr(p.audit$, 26, 1),
        substr(p.audit$, 27, 1) || '/' || substr(p.audit$, 28, 1),
        substr(p.audit$, 29, 1) || '/' || substr(p.audit$, 30, 1),
@@ -134,7 +129,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
@@ -158,7 +152,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 35, 1) || '/' || substr(t.audit$, 36, 1),
@@ -186,7 +179,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
@@ -210,7 +202,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
@@ -234,7 +225,6 @@
        substr(e.audit$, 17, 1) || '/' || substr(e.audit$, 18, 1),
        substr(e.audit$, 19, 1) || '/' || substr(e.audit$, 20, 1),
        substr(e.audit$, 21, 1) || '/' || substr(e.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(e.audit$, 25, 1) || '/' || substr(e.audit$, 26, 1),
        substr(e.audit$, 27, 1) || '/' || substr(e.audit$, 28, 1),
        substr(e.audit$, 29, 1) || '/' || substr(e.audit$, 30, 1),
@@ -246,7 +236,7 @@
   and (instrb(e.audit$,'S') != 0  or instrb(e.audit$,'A') != 0)
   and o.obj# = e.obj#
 union all
-select u.name, o.name, 'OLAP CUBE DIMENSION',
+select u.name, o.name, 'CUBE DIMENSION',
        substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
        substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
        substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1),
@@ -258,7 +248,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
@@ -270,7 +259,7 @@
   and (instrb(t.audit$,'S') != 0  or instrb(t.audit$,'A') != 0)
   and o.obj# = t.obj#
 union all
-select u.name, o.name, 'OLAP CUBE',
+select u.name, o.name, 'CUBE',
        substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
        substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
        substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1),
@@ -282,7 +271,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
@@ -294,7 +282,7 @@
   and (instrb(t.audit$,'S') != 0  or instrb(t.audit$,'A') != 0)
   and o.obj# = t.obj#
 union all
-select u.name, o.name, 'OLAP MEASURE FOLDER',
+select u.name, o.name, 'MEASURE FOLDER',
        substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
        substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
        substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1),
@@ -306,7 +294,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
@@ -318,7 +305,7 @@
   and (instrb(t.audit$,'S') != 0  or instrb(t.audit$,'A') != 0)
   and o.obj# = t.obj#
 union all
-select u.name, o.name, 'OLAP CUBE BUILD PROCESS',
+select u.name, o.name, 'CUBE BUILD PROCESS',
        substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
        substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
        substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1),
@@ -330,7 +317,6 @@
        substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
        substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
        substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
-       '-/-',                                            /* dummy REF column */
        substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
        substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
        substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
@@ -340,4 +326,96 @@
 where o.type# = 95
   and o.owner# = u.user#
   and (instrb(t.audit$,'S') != 0  or instrb(t.audit$,'A') != 0)
+  and o.obj# = t.obj#
+union all
+select u.name, o.name, 'SQL TRANSLATION PROFILE',
+       substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
+       substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
+       substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1),
+       substr(t.audit$, 7, 1) || '/' || substr(t.audit$, 8, 1),
+       substr(t.audit$, 9, 1) || '/' || substr(t.audit$, 10, 1),
+       substr(t.audit$, 11, 1) || '/' || substr(t.audit$, 12, 1),
+       substr(t.audit$, 13, 1) || '/' || substr(t.audit$, 14, 1),
+       substr(t.audit$, 15, 1) || '/' || substr(t.audit$, 16, 1),
+       substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
+       substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
+       substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
+       substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
+       substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
+       substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
+       substr(t.audit$, 31, 1) || '/' || substr(t.audit$, 32, 1),
+       substr(t.audit$, 23, 1) || '/' || substr(t.audit$, 24, 1)
+from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.sqltxl$ t
+where o.type# = 114
+  and o.owner# = u.user#
+  and (instrb(t.audit$,'S') != 0  or instrb(t.audit$,'A') != 0)
+  and o.obj# = t.obj#
+union all
+select u.name, o.name, 'ATTRIBUTE DIMENSION',
+       substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
+       substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
+       substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1),
+       substr(t.audit$, 7, 1) || '/' || substr(t.audit$, 8, 1),
+       substr(t.audit$, 9, 1) || '/' || substr(t.audit$, 10, 1),
+       substr(t.audit$, 11, 1) || '/' || substr(t.audit$, 12, 1),
+       substr(t.audit$, 13, 1) || '/' || substr(t.audit$, 14, 1),
+       substr(t.audit$, 15, 1) || '/' || substr(t.audit$, 16, 1),
+       substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
+       substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
+       substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
+       substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
+       substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
+       substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
+       substr(t.audit$, 31, 1) || '/' || substr(t.audit$, 32, 1),
+       substr(t.audit$, 23, 1) || '/' || substr(t.audit$, 24, 1)
+from sys.obj$ o, sys.user$ u, sys.hcs_dim$ t
+where o.type# = 151
+  and o.owner# = u.user#
+  and (instrb(t.audit$,'S') != 0  or instrb(t.audit$,'A') != 0)
+  and o.obj# = t.obj#
+union all
+select u.name, o.name, 'HIERARCHY',
+       substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
+       substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
+       substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1),
+       substr(t.audit$, 7, 1) || '/' || substr(t.audit$, 8, 1),
+       substr(t.audit$, 9, 1) || '/' || substr(t.audit$, 10, 1),
+       substr(t.audit$, 11, 1) || '/' || substr(t.audit$, 12, 1),
+       substr(t.audit$, 13, 1) || '/' || substr(t.audit$, 14, 1),
+       substr(t.audit$, 15, 1) || '/' || substr(t.audit$, 16, 1),
+       substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
+       substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
+       substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
+       substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
+       substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
+       substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
+       substr(t.audit$, 31, 1) || '/' || substr(t.audit$, 32, 1),
+       substr(t.audit$, 23, 1) || '/' || substr(t.audit$, 24, 1)
+from sys.obj$ o, sys.user$ u, sys.hcs_hierarchy$ t
+where o.type# = 150
+  and o.owner# = u.user#
+  and (instrb(t.audit$,'S') != 0  or instrb(t.audit$,'A') != 0)
+  and o.obj# = t.obj#
+union all
+select u.name, o.name, 'ANALYTIC VIEW',
+       substr(t.audit$, 1, 1) || '/' || substr(t.audit$, 2, 1),
+       substr(t.audit$, 3, 1) || '/' || substr(t.audit$, 4, 1),
+       substr(t.audit$, 5, 1) || '/' || substr(t.audit$, 6, 1),
+       substr(t.audit$, 7, 1) || '/' || substr(t.audit$, 8, 1),
+       substr(t.audit$, 9, 1) || '/' || substr(t.audit$, 10, 1),
+       substr(t.audit$, 11, 1) || '/' || substr(t.audit$, 12, 1),
+       substr(t.audit$, 13, 1) || '/' || substr(t.audit$, 14, 1),
+       substr(t.audit$, 15, 1) || '/' || substr(t.audit$, 16, 1),
+       substr(t.audit$, 17, 1) || '/' || substr(t.audit$, 18, 1),
+       substr(t.audit$, 19, 1) || '/' || substr(t.audit$, 20, 1),
+       substr(t.audit$, 21, 1) || '/' || substr(t.audit$, 22, 1),
+       substr(t.audit$, 25, 1) || '/' || substr(t.audit$, 26, 1),
+       substr(t.audit$, 27, 1) || '/' || substr(t.audit$, 28, 1),
+       substr(t.audit$, 29, 1) || '/' || substr(t.audit$, 30, 1),
+       substr(t.audit$, 31, 1) || '/' || substr(t.audit$, 32, 1),
+       substr(t.audit$, 23, 1) || '/' || substr(t.audit$, 24, 1)
+from sys.obj$ o, sys.user$ u, sys.hcs_analytic_view$ t
+where o.type# = 152
+  and o.owner# = u.user#
+  and (instrb(t.audit$,'S') != 0  or instrb(t.audit$,'A') != 0)
   and o.obj# = t.obj#;
--//自己重新修改视图定义应该可以通过,工程量有一些大,还存在一些风险,放弃!!

--//奇怪18c下执行报错。
YYYYY> @ ddl sys.dba_obj_audit_opts
ERROR:
ORA-31603: object "DBA_OBJ_AUDIT_OPTS" of type VIEW not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6681
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6668
ORA-06512: at "SYS.DBMS_METADATA", line 9672
ORA-06512: at line 1
--//昏不知道为什么不能在pdb下执行上述命令。在cdb下执行ok,我估计这个定义在cdb级别,而pdb是从cdb下继承下来的.

$ diff  19c.txt 18c.txt
2,3c2,3
< BJECT_TYPE", "ALT", "AUD", "COM", "DEL", "GRA", "IND", "INS", "LOC", "REN", "SEL", "UPD", "EXE", "CR
< E", "REA", "WRI", "FBK") AS
---
> BJECT_TYPE", "ALT", "AUD", "COM", "DEL", "GRA", "IND", "INS", "LOC", "REN", "SEL", "UPD", "REF", "EX
> E", "CRE", "REA", "WRI", "FBK") AS
15a16
>        '-/-',                                            /* dummy REF column */
39a41
>        '-/-',                                            /* dummy REF column */
62a65
>        '-/-',                                            /* dummy REF column */
85a89
>        '-/-',                                            /* dummy REF column */
108a113
>        '-/-',                                            /* dummy REF column */
131a137
>        '-/-',                                            /* dummy REF column */
154a161
>        '-/-',                                            /* dummy REF column */
181a189
>        '-/-',                                            /* dummy REF column */
204a213
>        '-/-',                                            /* dummy REF column */
227a237
>        '-/-',                                            /* dummy REF column */
250a261
>        '-/-',                                            /* dummy REF column */
273a285
>        '-/-',                                            /* dummy REF column */
296a309
>        '-/-',                                            /* dummy REF column */
319a333
>        '-/-',                                            /* dummy REF column */
342a357
>        '-/-',                                            /* dummy REF column */
365a381
>        '-/-',                                            /* dummy REF column */
388a405
>        '-/-',                                            /* dummy REF column */
411a429
>        '-/-',                                            /* dummy REF column */
--//很明显拿18c脚本执行就ok了。生产系统我不敢做,另外19c的定义里面出现了一个NONEDITIONABLE表示什么。
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW

--//先探究到这里,要重新建立该视图,生产系统还是小心再小心,没有测试环境,有机会在11g下尝试看看.
--//先改名视图,再建立新的视图,也许这样会安全一些,不行再修改回来.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2769007/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3081
  • 访问量
    6806396