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';--无记录