ORA-21700: object does not exist or is marked for delete

原创 jieguo 2021-11-09

2911

关于
案例诊断：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

复制

oracle 墨力计划

最后修改时间：2021-11-09 15:13:45

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

墨天轮福利君

3年前

您好，您的文章已入选合格奖，10墨值奖励已经到账请查收！ ❤️我们还会实时派发您的流量收益。

3年前

ORA-21700: object does not exist or is marked for delete

评论

相关阅读