首页 » ORACLE 9i-23ai » Troubleshooting ORA-01466: unable to read data – table definition has changed

Troubleshooting ORA-01466: unable to read data – table definition has changed

在oracle数据库中当事务查询或使用了flashback 选项,或expdp中指定了flashback_scn, 指定的时间或SCN小于表及附属对象的LAST_DDL_TIME时,会遇到ORA-01466: unable to read data – table definition has changed的报错,因为一些alter table 或truncate table等DDL操作会使表相关的UNDO数据失效,导致查询之前的数据镜像报错,错误文本提示很明显,表的结构发生了变化,常见EXPDP导出过程中,报错如下:

ORA-31693: Table data object ""."" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed

应用查询报错如下:

(RO TX began: T1 , Last DDL: T2, Curr Time: T3) T1<T2

分析方法
1,导出中是否使用了flashback_scn?
如expdp xxx flashback_scn=$SCN

2, 查询对象的LAST_DDL_TIME

SQL> select object_name,last_ddl_time from dba_objects where owner='' and object_name='';

3,比较两个时间点

SQL> select SCN_TO_TIMESTAMP(SCN_specified_with_export) from dual;

如果expdp的时间早于LAST_DDL_TIME就会报错。

4, expdp使用了flashback_scn,导出过程中有做grant授权操作.

grant xxx on object to  xxx;

5, 配置errorstack event

alter system set events = '1466 trace name ERRORSTACK level 3';

6, 使用dbms_scheduler创建expdp指定flashback选项(bug in 11.2.0.3)

7, 在12.1使用unplug,plugin升级PDB时(bug in 12.1+)
— over —

打赏

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