DataPump Export (EXPDP) Terminates Due To ORA-1466 When Using FLASHBACK_TIME

(Doc ID 1165825.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 19-Aug-2013***

Symptoms

expdp tc/tc DIRECTORY=test_dp DUMPFILE=export_schemas.dmp FLASHBACK_TIME="TO_TIMESTAMP(SYSDATE)"

...
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
ORA-31693: Table data object "TC"."RE$ACTION_IMP_TAB" failed to load/unload and is being skipped due to error:
ORA-01466: unable to read data - table definition has changed
...



TEST CASE

The following test case will illustrate the scenario:

-- drop and create a test user
drop user tc cascade;
create user tc identified by tc default tablespace users;
grant dba to tc;

connect tc/tc
alter session set nls_language = american;
alter session set nls_date_format= 'DD-MM-YY HH24:MI:SS';
execute DBMS_SCHEDULER.PURGE_LOG;

-- create two programs
exec DBMS_SCHEDULER.CREATE_PROGRAM ( -
      program_name => 'my_program1', -
      program_type => 'PLSQL_BLOCK', -
      program_action => 'BEGIN null; END;' ,enabled=>TRUE);

exec DBMS_SCHEDULER.CREATE_PROGRAM ( -
      program_name => 'my_program2', -
      program_type => 'PLSQL_BLOCK', -
      program_action => 'BEGIN null; END;' ,enabled=> TRUE);

-- create the chain
exec DBMS_SCHEDULER.CREATE_CHAIN ( chain_name =>'my_chain');

--- define corresponding rules and the steps for the chain.
exec DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain', 'TRUE', 'START stepA','chain_rule1',NULL);

exec DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain', 'stepA', 'my_program1');
exec DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain', 'stepA COMPLETED', 'START stepB','chain_rule2',NULL);

exec DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain', 'stepB', 'my_program2');
exec DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain', 'stepB COMPLETED','END', 'chain_rule3',NULL);

-- enable the chain
exec DBMS_SCHEDULER.ENABLE ('my_chain');

--- create a job with included schedule
BEGIN
  DBMS_SCHEDULER.create_job (
  job_name => 'runchain_job',
  job_type => 'CHAIN',
  job_action => 'my_chain',
  start_date => SYSDATE,
  repeat_interval => 'SYSDATE + 1' ,
  enabled => TRUE);
End;
/

SELECT job_name, enabled, LAST_START_DATE, next_run_date , state
FROM dba_scheduler_jobs
where job_name='RUNCHAIN_JOB';

-- Prepare Data Pump Export
create or replace directory test_dp as '/tmp';

host rm /tmp/export_schemas.dmp
host expdp tc/tc DIRECTORY=test_dp DUMPFILE=export_schemas.dmp FLASHBACK_TIME="TO_TIMESTAMP(SYSDATE)"

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms