关于
案例诊断：Oracle ANYDATA 数据类型存储的自定义类型丢失后ORA-21700
提到的expdp 报错ORA-21700: object does not exist or is marked for delete错误.
通过重建type跟踪数据字典insert可知需插入到如下表涉及oid变更：
alter session set events '10046 trace name context forever, level 12';
CREATE OR REPLACE TYPE jyc.T_STU AS OBJECT (
stu_num VARCHAR2(10),
stu_name VARCHAR2(10)
);
/
alter session set events '10046 trace name context off';
将旧oid替换更新新建的type即可解决：
select TYPE_NAME,type_oid from sys.dba_types as of timestamp to_timestamp(‘2021-11-09 10:41:18’,‘YYYY-MM-DD HH24:MI:SS’) where type_name=‘XXX’; --old xxx
select TYPE_NAME,type_oid from sys.dba_types where type_name=‘XXX’;–new xxx
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';--无记录
10:25:52 SQL> select id,dump(msg,16) dump_v from jyc.test_anydata where id=5;
ID----------DUMP_V
5
Typ=58 Len=77: 0,1,0,0,0,0,0,1,0,0,0,13,62,58,0,37,48,90,0,31,0,0,2d,1,85,1,2d,1,1,2,4,0,6c,d0,51,f6,bf,86,6d,e,8e,e0,53,81,34,a8,c0,76,7,0,1,0,0,0,0,d,84,1,fe,0,0,0,d,1,31,3,6a,79
,63,0,0,0,0,0,0,0,0
5
Typ=58 Len=77: 0,1,0,0,0,0,0,1,0,0,0,13,62,59,0,37,48,90,0,31,0,0,2d,1,85,1,2d,1,1,2,4,0,6c,d0,51,f6,bf,86,6d,e,8e,e0,53,81,34,a8,c0,76,7,0,1,0,0,0,0,d,84,1,fe,0,0,0,d,1,31,3,6a,79
,63,0,0,0,0,0,0,0,0
10:26:01 SQL>
另外跟踪报错的方法参考:
09:46:20 SQL> alter session set events '21700 trace name errorstack forever,level 3';
Session altered.
09:47:56 SQL> select SYS.ANYDATA.getTypeName(msg) type_name,count(*) from jyc.test_anydata group by SYS.ANYDATA.getTypeName(msg);
select SYS.ANYDATA.getTypeName(msg) type_name,count(*) from jyc.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
09:48:11 SQL> alter session set events '21700 trace name errorstack off';
Session altered.
在$ORACLE_BASE/diag/rdbms/trace/下xxx.trc
完整恢复测试记录：
[oracle@oem ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 9 10:42:54 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set container=jyc;
Session altered.
SQL> set line 160
SQL> desc dba_types;
Name Null? Type
----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
OWNER VARCHAR2(128)
TYPE_NAME VARCHAR2(128)
TYPE_OID RAW(16)
TYPECODE VARCHAR2(128)
ATTRIBUTES NUMBER
METHODS NUMBER
PREDEFINED VARCHAR2(3)
INCOMPLETE VARCHAR2(3)
FINAL VARCHAR2(3)
INSTANTIABLE VARCHAR2(3)
PERSISTABLE VARCHAR2(3)
SUPERTYPE_OWNER VARCHAR2(128)
SUPERTYPE_NAME VARCHAR2(128)
LOCAL_ATTRIBUTES NUMBER
LOCAL_METHODS NUMBER
TYPEID RAW(16)
SQL> col TYPE_NAME for a10
SQL> select TYPE_NAME,type_oid from dba_types where owner='JYC';
TYPE_NAME TYPE_OID
---------- --------------------------------
T_STU1 D051F6BF86790E8EE0538134A8C07607
SQL> TYPE_NAMETYPE_NAMETYPE_NAMEselect TYPE_NAME,type_oid from dba_types where ^C
SQL> select TYPE_NAME,type_oid from dba_types where TYPE_OID='D051F6BF866D0E8EE0538134A8C07607';
no rows selected
SQL> select TYPE_NAME,type_oid from dba_types where TYPE_OID='D051F6BF86720E8EE0538134A8C07607';
no rows selected
SQL> select TYPE_NAME,type_oid from dba_types where TYPE_OID='D051F6BF86790E8EE0538134A8C07607';
TYPE_NAME TYPE_OID
---------- --------------------------------
T_STU1 D051F6BF86790E8EE0538134A8C07607
SQL>
SQL>
SQL>
SQL>
SQL> select oid$ from sys.oid$ where oid$='D051F6BF86790E8EE0538134A8C07607';
OID$
--------------------------------
D051F6BF86790E8EE0538134A8C07607
SQL> select oid$ from sys.oid$ where oid$='D051F6BF866D0E8EE0538134A8C07607';
OID$
--------------------------------
D051F6BF866D0E8EE0538134A8C07607
SQL> select oid$ from sys.oid$ where oid$='D051F6BF86720E8EE0538134A8C07607';
no rows selected
SQL> desc oid$
Name Null? Type
----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
USER# NOT NULL NUMBER
OID$ NOT NULL RAW(16)
OBJ# NOT NULL NUMBER
INDEX# NOT NULL NUMBER
SQL> select toid from attribute$ where toid='D051F6BF86720E8EE0538134A8C07607';
no rows selected
SQL> select toid from attribute$ where toid='D051F6BF866D0E8EE0538134A8C07607';
TOID
--------------------------------
D051F6BF866D0E8EE0538134A8C07607
D051F6BF866D0E8EE0538134A8C07607
SQL> select toid from attribute$ where toid='D051F6BF86790E8EE0538134A8C07607';
TOID
--------------------------------
D051F6BF86790E8EE0538134A8C07607
D051F6BF86790E8EE0538134A8C07607
SQL> select SYS.ANYDATA.getTypeName(msg) type_name,count(*) from jyc.test_anydata group by SYS.ANYDATA.getTypeName(msg);
TYPE_NAME COUNT(*)
---------- ----------
SYS.NUMBER 2
JYC.T_STU1 1
SYS.VARCHA 1
R2
SYS.DATE 2
JYC.T_STU 2
SQL> drop type jyc.T_STU1;
Type dropped.
SQL> select SYS.ANYDATA.getTypeName(msg) type_name,count(*) from jyc.test_anydata group by SYS.ANYDATA.getTypeName(msg);
select SYS.ANYDATA.getTypeName(msg) type_name,count(*) from jyc.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 jyc.test_anydata;
ERROR:
ORA-21700: object does not exist or is marked for delete
no rows selected
SQL> select toid from attribute$ where toid='D051F6BF86790E8EE0538134A8C07607';
no rows selected
SQL> CREATE OR REPLACE TYPE jyc.T_STU1 AS OBJECT (
2 stu_num VARCHAR2(10),
3 stu_name VARCHAR2(10)
4 );
5 /
Type created.
SQL> select TYPE_NAME,type_oid from dba_types where owner='JYC';
TYPE_NAME TYPE_OID
---------- --------------------------------
T_STU1 D0532D42897B493BE0538134A8C01BD4
SQL> select TYPE_NAME,type_oid from dba_types where TYPE_OID='D0532D42897B493BE0538134A8C01BD4';
TYPE_NAME TYPE_OID
---------- --------------------------------
T_STU1 D0532D42897B493BE0538134A8C01BD4
SQL> select TYPE_NAME,type_oid from sys.dba_types as of timestamp to_timestamp('2021-11-09 10:41:18','YYYY-MM-DD HH24:MI:SS') where type_name='T_STU1';
TYPE_NAME TYPE_OID
---------- --------------------------------
T_STU1 D051F6BF86790E8EE0538134A8C07607
SQL> select TYPE_NAME,type_oid from sys.dba_types where type_name='T_STU1';
TYPE_NAME TYPE_OID
---------- --------------------------------
T_STU1 D0532D42897B493BE0538134A8C01BD4
SQL> update sys.type$ set toid='D051F6BF86790E8EE0538134A8C07607',tvoid='D051F6BF86790E8EE0538134A8C07607' where toid='D0532D42897B493BE0538134A8C01BD4';
1 row updated.
SQL> update sys.oid$ set oid$='D051F6BF86790E8EE0538134A8C07607' where oid$='D0532D42897B493BE0538134A8C01BD4';
1 row updated.
SQL> update SYS.KOTTD$ set SYS_NC_OID$='D051F6BF86790E8EE0538134A8C07607' where SYS_NC_OID$='D0532D42897B493BE0538134A8C01BD4';
1 row updated.
SQL> update SYS.KOTTB$ set SYS_NC_OID$='D051F6BF86790E8EE0538134A8C07607' where SYS_NC_OID$='D0532D42897B493BE0538134A8C01BD4';
0 rows updated.
SQL> update SYS.KOTAD$ set SYS_NC_OID$='D051F6BF86790E8EE0538134A8C07607' where SYS_NC_OID$='D0532D42897B493BE0538134A8C01BD4';
0 rows updated.
SQL> update SYS.KOTTBX$ set SYS_NC_OID$='D051F6BF86790E8EE0538134A8C07607' where SYS_NC_OID$='D0532D42897B493BE0538134A8C01BD4';
0 rows updated.
SQL> commit;
Commit complete.
SQL> update attribute$ set toid='D051F6BF86790E8EE0538134A8C07607' where toid='D0532D42897B493BE0538134A8C01BD4';
2 rows updated.
SQL> commit;
Commit complete.
SQL> select * from jyc.test_anydata;
ID
----------
MSG()
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1
ANYDATA()
2
ANYDATA()
3
ANYDATA()
ID
----------
MSG()
----------------------------------------------------------------------------------------------------------------------------------------------------------------
4
ANYDATA()
5
ANYDATA()
5
ANYDATA()
ID
----------
MSG()
----------------------------------------------------------------------------------------------------------------------------------------------------------------
4
ANYDATA()
7
ANYDATA()
8 rows selected.
SQL> select SYS.ANYDATA.getTypeName(msg) type_name,count(*) from jyc.test_anydata group by SYS.ANYDATA.getTypeName(msg);
TYPE_NAME COUNT(*)
---------- ----------
SYS.NUMBER 2
JYC.T_STU1 1
SYS.VARCHA 1
R2
SYS.DATE 2
JYC.T_STU 2
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oem ~]$ expdp jyc/jyc@jyc dumpfile=t2.dmp logfile=t2.log directory=dmp tables=jyc.test_anydata
Export: Release 19.0.0.0.0 - Production on Tue Nov 9 11:16:25 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
FLASHBACK automatically enabled to preserve database integrity.
Starting "JYC"."SYS_EXPORT_TABLE_01": jyc/********@jyc dumpfile=t2.dmp logfile=t2.log directory=dmp tables=jyc.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/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "JYC"."TEST_ANYDATA" 6.109 KB 8 rows
Master table "JYC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/t2.dmp
Job "JYC"."SYS_EXPORT_TABLE_01" successfully completed at Tue Nov 9 11:16:35 2021 elapsed 0 00:00:09
最后修改时间：2021-11-09 15:13:45
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
