Troubleshooting Oracle ANYDATA 数据类型存储的自定义类型丢失后ORA-21700

首页 » Cloud, ORACLE 9i-23ai » Troubleshooting Oracle ANYDATA 数据类型存储的自定义类型丢失后ORA-21700

Troubleshooting Oracle ANYDATA 数据类型存储的自定义类型丢失后ORA-21700

2021/11/06
Cloud, ORACLE 9i-23ai
216 views
Troubleshooting Oracle ANYDATA 数据类型存储的自定义类型丢失后ORA-21700已关闭评论

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

— update 2021/11/09 —
这位网友在“墨天轮社区”回复更新几个基表是可以在UDT 重建后，替换原来OID正常使用，思路上是可行的，但记住不受官方支持自行承担风险，昨天我是只更新了type$.toid和tvoid列所以没有成功。

update sys.attribute$ where toid='old xxx' where toid='new xxx';
update sys.type$ where toid='old xxx',tvoid='old xxx' where toid='new xxx';
update sys.oid$ set oid$='old xxx' where oid$='new xxx';
update SYS.KOTTD$ set SYS_NC_OID$='old xxx' where SYS_NC_OID$='new xxx';
update SYS.KOTTB$ set SYS_NC_OID$='old xxx' where SYS_NC_OID$='new xxx';--无记录
update SYS.KOTAD$ set SYS_NC_OID$='old xxx' where SYS_NC_OID$='new xxx';--无记录
update SYS.KOTTBX$ set SYS_NC_OID$='old xxx' where SYS_NC_OID$='new xxx';--无记录

打赏

anydata, ORA-21700

对不起，这篇文章暂时关闭评论。

上一篇： Oracle RAC 环境中配置DNS性能问题或故障对使用SCAN Listener的影响

下一篇： Oracle 21c 新特性： Automatic Materialized Views

ANBOB™

Troubleshooting Oracle ANYDATA 数据类型存储的自定义类型丢失后ORA-21700

对不起，这篇文章暂时关闭评论。

最新文章

标签云集

文章索引

MySql Link

ORACLE Link

Others Link

国内好友

管理功能

微信公众号/Wechat