关于
案例诊断：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
米乐app官网下载 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
米乐app官网下载 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
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。