案例診斷：Oracle ANYDATA 數據類型存儲的自定義類型丟失后ORA-21700

原創 Anbob 2021-11-09

6330

Oracle ANYDATA數據類型幾年前在做expdp遷移時接觸過，類型XML,LOB導出速度是慢之非常，今天又一個客戶在expdp時一個interval partition表時，部分分區導出報錯ORA-21700: object does not exist or is marked for delete錯誤，依賴的對象不存在，查看表定義存在anydata列，也就是可能anydata這種任何數據類型都可以存儲(但還是建議用lob類型代替)，但是存儲的自定義類型又不存在了，這種情況很危險，下面演示一下這種情況。

```

CREATE OR REPLACE TYPE anbob.t_stu AS OBJECT (
  stu_num VARCHAR2(10),
  stu_name VARCHAR2(10)
);
/

create table anbob.test_anydata(id int,msg anydata);

INSERT INTO anbob.test_anydata (id, msg) VALUES (1, SYS.ANYDATA.convertVarchar2('This is varchar2'));
INSERT INTO anbob.test_anydata (id, msg) VALUES (2, SYS.ANYDATA.convertNumber(999));
INSERT INTO anbob.test_anydata (id, msg) VALUES (3, SYS.ANYDATA.convertNumber(1));
INSERT INTO anbob.test_anydata (id, msg) VALUES (4, SYS.ANYDATA.convertDate(sysdate));

COMMIT;


DECLARE
  l_obj     anbob.t_stu := anbob.t_stu('1','anbob');
  l_anydata SYS.ANYDATA;
BEGIN
  -- Convert Object to ANYDATA and back.
  l_anydata := SYS.ANYDATA.convertObject(l_obj);

  INSERT INTO anbob.test_anydata (id, msg) VALUES (5, l_anydata);

  IF l_anydata.getObject(l_obj) = DBMS_TYPES.SUCCESS
  THEN
    DBMS_OUTPUT.put_line('T_MY_TYPE : ' || l_obj.stu_num || ' : ' || l_obj.stu_name);
  END IF;
END;
 15  /
T_MY_TYPE : 1 : anbob

commit;

SQL> select * from anbob.test_anydata;

        ID MSG()
---------- ------------------------------
         1 ANYDATA()
         2 ANYDATA()
         3 ANYDATA()
         4 ANYDATA()
         5 ANYDATA()

SQL> @dep  anbob test_anydata % %

OWNER            DEPENDENT_NAME                 DEPENDENT_TY REF_OWNER        REF_NAME                       REF_TYPE     DEP_
---------------- ------------------------------ ------------ ---------------- ------------------------------ ------------ ----
ANBOB            TEST_ANYDATA                   TABLE        SYS              STANDARD                       PACKAGE      HARD
ANBOB            TEST_ANYDATA                   TABLE        SYS              ANYDATA                        TYPE         HARD

SQL>  select   SYS.ANYDATA.getTypeName(msg) type_name,count(*) from anbob.test_anydata group by SYS.ANYDATA.getTypeName(msg)

TYPE_NAME                        COUNT(*)
------------------------------ ----------
SYS.NUMBER                              2
ANBOB.T_STU                             1
SYS.VARCHAR2                            1
SYS.DATE                                1

```

TIP：
ANYDATA.getTypeName 可以取到ANYDATA數據類型的實際datatype, 但是在查看依賴對象是并不會提示表依賴我們開始創建的自定義對象，這種情況如果刪除了自定義對象就糟糕了,下面繼續。

SQL> drop type anbob.t_stu ;

Type dropped.

SQL> select   SYS.ANYDATA.getTypeName(msg) type_name,count(*) from anbob.test_anydata group by SYS.ANYDATA.getTypeName(msg);
select   SYS.ANYDATA.getTypeName(msg) type_name,count(*) from anbob.test_anydata group by SYS.ANYDATA.getTypeName(msg)
                                                                                          *
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SYS.ANYDATA", line 174


SQL> select * from anbob.test_anydata;
ERROR:
ORA-21700: object does not exist or is marked for delete



no rows selected

[oracle@oel7db1 ~]$ expdp system/oracle@cdb1pdb1 directory=DATAPUMP dumpfile=anydata.dump tables=anbob.test_anydata

Export: Release 19.0.0.0.0 - Production on Sat Nov 6 19:58:07 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@cdb1pdb1 directory=DATAPUMP dumpfile=anydata.dump tables=anbob.test_anydata
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "ANBOB"."TEST_ANYDATA" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-21700: object does not exist or is marked for delete

TIP：
讀取到表里anydata包含已刪除的自定義對象的行時，anydata無法轉換才有了這個錯誤，同時影響EXPDP.

SQL>  DECLARE
  2    l_anydata SYS.ANYDATA;
  3    my_code varchar2(100);
  4    l_typename varchar2(100);
  5  BEGIN
  6     for i in (select * from anbob.test_anydata) loop
  7      begin
  8   l_typename:=SYS.ANYDATA.getTypeName(i.msg);
  9   EXCEPTION
 10             WHEN OTHERS THEN
 11         begin
 12           my_code := SQLCODE;
 13              --DBMS_OUTPUT.put_line(my_code);
 14       if my_code in ('-21700')  then
 15        DBMS_OUTPUT.put_line('miss datatype id:'||i.id );
 16               END IF;
 17        end;
 18  end;
 19     end loop;
 20* END;
miss datatype id:5

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE TYPE anbob.t_stu AS OBJECT (
  stu_num VARCHAR2(10),
  stu_name VARCHAR2(10)
);
 /

Type created.

SQL> select * from anbob.test_anydata;
ERROR:
ORA-21700: object does not exist or is marked for delete

SQL> INSERT INTO anbob.test_anydata (id, msg) VALUES (6, SYS.ANYDATA.convertObject(anbob.t_stu('2','anbob.com')));
1 row created.

SQL> select * from anbob.test_anydata where id=5;
ERROR:
ORA-21700: object does not exist or is marked for delete

no rows selected

SQL> select * from anbob.test_anydata where id=6;

        ID MSG()
---------- ----------------------------------------
         6 ANYDATA()

note：重建對象后，對象重建之前的數據依舊無法讀取，新錄的數據可以讀，看來是有先后時間依賴。

解決方法，暫時發現是delete或update對象的列，再手動補，也就是數據丟失了，如果有更好的方法，請聯系我。

SQL> delete anbob.test_anydata where id=5;
1 row deleted.

SQL> commit;
Commit complete.

SQL> col msg for a30
SQL> select * from anbob.test_anydata;

        ID MSG()
---------- ------------------------------
         1 ANYDATA()
         2 ANYDATA()
         3 ANYDATA()
         4 ANYDATA()

SQL> DECLARE
  l_obj     anbob.t_stu := anbob.t_stu('1','anbob');
  l_anydata SYS.ANYDATA;
BEGIN
  -- Convert Object to ANYDATA and back.
  l_anydata := SYS.ANYDATA.convertObject(l_obj);

  INSERT INTO anbob.test_anydata (id, msg) VALUES (5, l_anydata);

  IF l_anydata.getObject(l_obj) = DBMS_TYPES.SUCCESS
  THEN
    DBMS_OUTPUT.put_line('T_MY_TYPE : ' || l_obj.stu_num || ' : ' || l_obj.stu_name);
  END IF;
END;
 15  /

PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

SQL> select * from anbob.test_anydata;
        ID MSG()
---------- ------------------------------
         1 ANYDATA()
         2 ANYDATA()
         3 ANYDATA()
         4 ANYDATA()
         5 ANYDATA()

請注意，ANYDATA 列中的依賴關系不是元數據級別的數據，因此不會創建從 ANYDATA 到類型的依賴關系, 如果創建的表列是自定義數據類型，那會有強依賴關系，在刪除自定義datatype時會檢查是否有表定義使用了該類型。在 oracle 中，依賴項總是在元數據級別而不是數據級別進行跟蹤，ANYDATA 就是這種情況。這是 ANYDATA 的預期行為。

SQLcl
注意使用SQLcl工具顯示更加直觀

[oracle@oel7db1 bin]$ ./sql

SQLcl: Release 21.2 Production on Sun Nov 07 10:52:28 2021
Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Username? (''?) system
Password? (**********?) ******
Last Successful login time: Sun Nov 07 2021 10:52:36 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> alter session set container=pdb1;
Session altered.

SQL> select * from anbob.test_anydata where id=6;
   ID                                                                                     MSG
_____ _______________________________________________________________________________________
    6 OPAQUE(ANYDATA TypeCode: "TYPECODE_JDBC_STRUCT" - ANYDATA Value: "ANBOB.T_STU(...)")

SQL> select * from anbob.test_anydata where id=5;
Error starting at line : 1 in command -
select * from anbob.test_anydata where id=5
Error report -
ORA-21700: object does not exist or is marked for delete

查找自定義對象類型

SQL> select id,dump(msg,16) dump_v from anbob.test_anydata where id=6;
   ID                                                                                                                                                                                                                          DUMP_V
_____ _______________________________________________________________________________________________________________________________________________________________________________________________________________________________
    6 Typ=58 Len=83: 0,1,0,0,0,0,0,1,0,0,0,f1,47,cc,0,3d,48,90,0,37,0,0,33,1,85,1,33,1,1,2,4,0,6c,d0,2a,9d,88,e0,a5,7,b0,e0,55,36,b7,9b,c2,72,9a,0,1,0,0,0,0,13,84,1,fe,0,0,0,13,1,32,9,61,6e,62,6f,62,2e,63,6f,6d,0,0,0,0,0,0,0,0

SQL> select id,dump(msg,16) dump_v from anbob.test_anydata where id=5;
   ID                                                                                                                                                                                                           DUMP_V
_____ ________________________________________________________________________________________________________________________________________________________________________________________________________________
    5 Typ=58 Len=79: 0,1,0,0,0,0,0,1,0,0,0,f1,47,31,0,39,48,90,0,33,0,0,2f,1,85,1,2f,1,1,2,4,0,6c,d0,1f,4c,90,2,1a,d,ac,e0,55,36,b7,9b,c2,72,9a,0,1,0,0,0,0,f,84,1,fe,0,0,0,f,1,31,5,61,6e,62,6f,62,0,0,0,0,0,0,0,0

TIP :
數據類型 58 是不透明的 (DTYOPQ)，它可以保存 ANYDATA、ANYTYPE、XMLTYPE、ANYDATASET（可能還有其他）。但是如何準確區分哪個，或者如何解釋我從 dump() 得到的字節，我在某處找不到。但是我們可以仔細對象類型的OID.

SQL> @printtab 'select * from dba_types where owner="ANBOB"';
OWNER                         : ANBOB
TYPE_NAME                     : T_STU
TYPE_OID                      : D02A9D88E0A507B0E05536B79BC2729A
TYPECODE                      : OBJECT
ATTRIBUTES                    : 2
METHODS                       : 0
PREDEFINED                    : NO
INCOMPLETE                    : NO
FINAL                         : YES
INSTANTIABLE                  : YES
PERSISTABLE                   : YES
SUPERTYPE_OWNER               :
SUPERTYPE_NAME                :
LOCAL_ATTRIBUTES              :
LOCAL_METHODS                 :
TYPEID                        :
-----------------

PL/SQL procedure successfully completed.

TIP:
注意 type的OID為 D02A9D88E0A507B0E05536B79BC2729A,和DUMP的數據類型對應部分匹配0,1,0,0,0,0,0,1,0,0,0,f1,47,cc,0,3d,48,90,0,37,0,0,33,1,85,1,33,1,1,2,4,0,6c,d0,2a,9d,88,e0,a5,7,b0,e0,55,36,b7,9b,c2,72,9a,0,1,0,0,0,0,13,84,1,fe,0,0,0,13,1,32,9,61,6e,62,6f,62,2e,63,6f,6d,0,0,0,0,0,0,0,0

那就可以從dba_type找報錯的那行對象的type是不是丟了

id=5 那行的dump 找相同位置type oid為“d0,1f,4c,90,2,1a,d,ac,e0,55,36,b7,9b,c2,72,9a”

select toid,versions_endscn,versions_operation
 from sys.type$ versions between scn minvalue and maxvalue
 where ',d0,2a,9d,88,e0,a5,7,b0,e0,55,36,b7,9b,c2,72,9a,'
 like '%,'||regexp_replace(dump(type$.toid,16),'^.* ')||',%'
  5  ;
TOID                             VERSIONS_ENDSCN V
-------------------------------- --------------- -
D02A9D88E0A507B0E05536B79BC2729A                 I

select toid,versions_endscn,versions_operation
 from sys.type$ versions between scn minvalue and maxvalue
 where ',d0,1f,4c,90,2,1a,d,ac,e0,55,36,b7,9b,c2,72,9a,'
 like '%,'||regexp_replace(dump(type$.toid,16),'^.* ')||',%'
  5  ;
TOID                             VERSIONS_ENDSCN V
-------------------------------- --------------- -
D01F4C90021A0DACE05536B79BC2729A                 D
D01F4C90021A0DACE05536B79BC2729A        17989537



SQL> col type_name for a30
SQL> select owner,type_name   from dba_types  where rawtohex(type_oid)= 'D02A9D88E0A507B0E05536B79BC2729A';
OWNER                          TYPE_NAME
------------------------------ ------------------------------
ANBOB                          T_STU

SQL> r
  1  with function try(x anydata,d varchar2) return varchar2
  2  as
  3   l_toid varchar2(1000);                                                                                                                                                        l_scn number;
  4   l_name varchar2(1000);
  5   begin
  6   return anydata.getTypeName(x);
  7   exception when others then
  8   select rawtohex(toid),versions_endscn into l_toid,l_scn
  9   from sys.type$ versions between scn minvalue and maxvalue
 10   where d like '%,'||regexp_replace(dump(type$.toid,16),'^.* ')||',%'
 11   order by versions_endscn fetch first 1 rows only;
 12   select owner||'.'||type_name into l_name
 13   from dba_types as of scn (l_scn -1)
 14   where rawtohex(type_oid)=l_toid;
 15   return sqlerrm||' -> '||l_name;
 16   end;
 17   select id,try(msg,dump(msg,16)) res  from anbob.test_anydata
 18*

        ID RES
---------- ----------------------------------------------------------------------------------------------------
         1 SYS.VARCHAR2
         2 SYS.NUMBER
         3 SYS.NUMBER
         4 SYS.DATE
         5 ORA-21700: object does not exist or is marked for delete -> ANBOB.T_STU
         6 ANBOB.T_STU

Note:
我的測試案例中可以看出對象雖然重建但是OID變了，ID=5的行記錄對應的數據類型已被刪除,從flashback query中可以得到（因為我的undo 還沒有被覆蓋掉）。也可以對比對象的值

SQL> select dump('2',16) from dual;
DUMP('2',16)
----------------
Typ=96 Len=1: 32

SQL> select dump('anbob.com',16) from dual;
DUMP('ANBOB.COM',16)
----------------------------------------
Typ=96 Len=9: 61,6e,62,6f,62,2e,63,6f,6d

對應用的是anydata dump中的0,1,0,0,0,0,13,84,1,fe,0,0,0,13,1,32,9,61,6e,62,6f,62,2e,63,6f,6d,0,0,0

— enjob —

「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」

關注作者

特色一级强游戏,海奥华预言免费阅读,51漫画兑换码,美女裸体无遮挡永久免费观看网站,lubuntu线路检测入口

案例診斷：Oracle ANYDATA 數據類型存儲的自定義類型丟失后ORA-21700

評論