[20210420]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211022]ORA-00904 REF invalid identifier 19c dba_obj_audit_opts(補充).txtIDEOBJ
- [20200312]ORA-00904 POLTYP invalid identifier.txtIDE
- 【LISTAGG】 ORA-00904: "WM_CONCAT": invalid identifier (DocIDE
- ORA-00904: "wm_concat":invalid identifier錯誤如何解決？IDE
- Python 錯誤 SyntaxError: invalid character in identifierPythonErrorIDE
- [20210420]19c奇怪的過濾條件.txt
- ref
- PostgreSQL DBA(80) - Object Identifier TypesSQLObjectIDE
- Spring中ref local=""與ref bean=""的區別SpringBean
- vue --ref用法Vue
- ref屬性
- ref和reactiveReact
- Rust 中 *、&、mut、&mut、ref、ref mut 的用法和區別Rust
- examples for oracle ref cursorsOracle
- React ref的用法React
- 使用 ref 引用值
- Vue 學習 Ref shallowRef triggerRef customRef （Ref 和 Reactive的對比）VueReact
- JavaScript invalid 事件JavaScript事件
- found an invalid color
- Uncaught SyntaxError: Identifier 'Geometry' has already been declaredErrorIDE
- 關於hibernate的 No row with the given identifier existsIDE
- [20210114]理解DBMS_SESSION.set_identifier.txtSessionIDE
- 關於React的refReact
- React ref 的前世今生React
- Difference between cursor and a ref cursor
- React ref的基本使用React
- stm32 use of undeclared identifier GPIO_InitTypeDefIDE
- StreamCorruptedException: invalid stream headerExceptionHeader
- Invalid time zone indicator ‘ ‘Indicator
- vue中的 ref 和 $refsVue
- python ref counting based garbage collectionPython
- python: invalid value encountered in divide以及invalid value encountered in double_scalars報錯PythonIDE
- OSError: [Errno 22] Invalid argumentError
- out,ref,params引數傳遞
- 組合API-ref屬性API
- 組合API-ref函式API函式
- std::bind與std::ref, why and how
- [Error Code: 904, SQL State: 42000] ORA-00904 : 識別符號無效ErrorSQL符號