errorstack ORA-01466 flashback query expdp scn 都有这个问题

LAST_DDL_TIME 可以是truncate ，add column， enable constraints ，grant

Set an errorstack event using:

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

Export using EXPDP like below is failing for a table with following error.

EXPDP syntax :

expdp userid=<LOGIN>
     dumpfile=<DUMP_NAME>_%U.dmp
     logfile=<LOG_NAME>.log
     filesize=28000m
     flashback_scn=$SCN
     parallel=5
     cluster=N
     full=y

Error Message :

ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>" 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

Changes

The value of SCN supplied to parameter flashback_scn does not correspond to the time of LAST_DDL_TIME from DBA_OBJECTS for the specific table.

A table's DDL may have changed due to a truncate job and LAST_DDL_TIME can end up ahead of the flashback_scn time supplied in the parameter.

Cause

In our example, a developer created a job that ran a truncate command while datapump job was running.

Solution

Check if there is any DDL job which is causing LAST_DDL_TIME to be modified to newer value. If so, change the time of execution of that job.

As a workaround you can remove the FLASHBACK_SCN parameter until you get the solution for the DDL job.

This error can also be generated for FLASHBACK_TIME.

To determine if you are facing this issue, execute following steps:

Check dba_objects for LAST_DDL_TIME :
SQL> select object_name,last_ddl_time from dba_objects where owner='<SCHEMA_NAME>' and object_name='<TABLE_NAME>';
Collect SCN timestamp
SQL> select SCN_TO_TIMESTAMP(SCN_specified_with_export) from dual;

If the LAST_TIME is newer than the scn time, you are facing this issue.
Remove the job which is running any DDL command on the failing object.

-----------------grant 也不行

Symptoms

Data Pump Export (Expdp) returns an ORA-1466 when FLASHBACK_SCN is being used. This occurs when a privilege is granted while Expdp is running.

grant <privilege> on <object> to <role>;

Once Expdp attempts to exports the object the ORA-1466 is reported.
expdp tc/tc DIRECTORY=tc_datapump DUMPFILE=export_schema.dmp flashback_scn=2252162

EXPDP LOG
---------
...
DUMPFILE=export_schema.dmp flashback_scn=2252162
...
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
ORA-31693: Table data object "TC"."T1" failed to load/unload and is being skipped due to error:
ORA-2354: error in exporting/importing data
ORA-1466: unable to read data - table definition has changed
Master table "TC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

REPRODUCIBILITY

Permanently reproducible with Data Pump Export (Expdp)
Verified on 10g and 11g.
Doesn't reproduce with traditional export, even when using FLASHBACK_SCN.

TEST CASE

Test case is using tablespace named 'TESTCASE'.
Open 2 separate sqlplus sessions

Session #1: Creates relevant objects and runs expdp with flashback_scn
Session #2: Grants system privileges to a rule while expdp is running.

SESSION 1
-- Create a tablespace
drop tablespace testcase including contents and datafiles;
create tablespace testcase datafile '/tmp/testcase.dbf' size 10m autoextend on maxsize unlimited;

-- Create user and test objects
drop user tc cascade;
create user tc identified by tc default tablespace testcase;
grant dba to tc;

-- Create directory
create or replace directory tc_datapump as '/tmp';
grant read, write on directory tc_datapump to tc;

-- Create a test table
create table tc.t1 as select * from dba_source;
select count(*) from tc.t1;

-- Determine current SCN
set numwidth 15
select dbms_flashback.get_system_change_number from dual;

-- Example:

GET_SYSTEM_CHANGE_NUMBER
------------------------
2252162

-- Initiate expdp
host rm /tmp/export_schema.dmp
host expdp tc/tc DIRECTORY=tc_datapump DUMPFILE=export_schema.dmp flashback_scn=2252162

SESSION 2

-- Execute immediately after the expdp has been started:

connect / as sysdba
grant select on tc.t1 to public;

-- Once the expdp attempts export the table ORA-1466 will be reported by session #1.

DIAGNOSTIC ANALYSIS:

Create a trace file on the ORA-1466 occurrence and retry Expdp
Once the ORA-01466 occurs a trace file is created by the Data Pump Worker process (DWxx)

Set an errorstack event using:

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

TRACE FILE

The trace file content shows information like:

ksedmp: internal or fatal error
ORA-1466: unable to read data - table definition has changed

Current SQL statement for this session:
SELECT * FROM RELATIONAL("TC"."T1")

----- PL/SQL Call Stack -----
object line object
handle number name
0x30161d98 14 package body SYS.KUPD$DATA_INT
0x301d4138 1313 package body SYS.KUPD$DATA
0x301d7af8 10826 package body SYS.KUPW$WORKER
0x301d7af8 2637 package body SYS.KUPW$WORKER
0x301d7af8 6958 package body SYS.KUPW$WORKER
0x301d7af8 1314 package body SYS.KUPW$WORKER
0x301af7c4 2 anonymous block

STACK TRACE:
------------
... kpodpp opiodr kpoodr upirtrc kpurcsc kpudpxp_ctxPrepare ...

Changes

Sequence of events leading to the problem

Run Data Pump Export (Expdp) with parameter flashback_scn
Grant any privileges to any role while expdp is running.

Example:
SQL> grant select on tc.t1 to public;

Cause

The issue is discussed in
Bug 8534161 - ORA-1466 REPORTED BY EXPDP WHEN USING FLASHBACK_SCN AND PRIVILEGES WERE GRANTED

Oracle Development confirmed this is expected behavior and not a bug.