[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下尝试看看. --//先改名视图,再建立新的视图,也许这样会安全一些,不行再修改回来.
[20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txt
来源：这里教程网
时间：2026-03-03 16:37:55
作者：
编辑推荐：
- [20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txt03-03
- [20210421]12c以上版本增加字段与缺省值.txt03-03
- [20210421]分析会话占用的共享内存段2.txt03-03
- qq相册照片怎么批量下载到手机，qq相册批量下载功能03-03
- [20210421]如何使用dumpsga转储sga.txt03-03
- ORA-1619103-03
- 【RAT】Oracle Real Application Testing（真用应用测试）介绍03-03
- 【INSTALL】Oracle12c 在centos8.3安装报错“no oraInstaller in java.library.path”03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- qq相册照片怎么批量下载到手机，qq相册批量下载功能
qq相册照片怎么批量下载到手机，qq相册批量下载功能
26-03-03 - 【RAT】Oracle Real Application Testing（真用应用测试）介绍
- Oracle数据库宕机案例分享
Oracle数据库宕机案例分享
26-03-03 - Oracle 11.2.0.4 本地/远程登录慢的问题
Oracle 11.2.0.4 本地/远程登录慢的问题
26-03-03 - 从Oracle 11.2.0.4 BUG到Oracle子查询展开分析
从Oracle 11.2.0.4 BUG到Oracle子查询展开分析
26-03-03 - Oracle学习路线
Oracle学习路线
26-03-03 - 设置SSH信任关系
设置SSH信任关系
26-03-03 - 怎样下载小品视频到手机，教你快捷方法，批量下载各种视频
怎样下载小品视频到手机，教你快捷方法，批量下载各种视频
26-03-03 - 一条SQL引起的ORA-04031错误
一条SQL引起的ORA-04031错误
26-03-03 - 自媒体技巧之一，一键批量获取无水印的西瓜、抖音短视频
自媒体技巧之一，一键批量获取无水印的西瓜、抖音短视频
26-03-03