Oracle Data Pump, pause a job

impdporacleoracle-11g-r2

I currently have a impdp job running for a fairly large schema (2TB) and ran into an error. The source database has one column that is larger then the destination database. This is causing ORA-02374 and ORA-12899 errors. This has caused the import job to come to a crawl on this one table.

I am trying to get past the errors, and the best solutions I can come up with are to either fix the table definition, or tell the import job to skip the current table. I can not currently fix the table definition because the data pump job has the table locked.

Is there a way to pause the job, make the column modification, and then resume the import? Alternatively is there a way to tell the job to skip the current table, and move on? This would allow to to come back once the job is finished, fix the table definition, and then re-import just the one table.

ETA: This is one of the last few tables in the import, I would rather not kill the whole thing and start over.

Best Answer

Well you can stop the job and make changes in the table and later continue the job either skipping the current object or not.

Demo:

 [oracle@localhost]$ impdp jay/password directory=dp_dir  tables=imptest, imptest2 content=data_only dumpfile=exp_jay_imptest.dmp
 ...

 ORA-02374: conversion error loading table "JAY"."IMPTEST"
 ORA-12899: value too large for column NAME (actual: 12, maximum: 10)

 ORA-02372: data for row: NAME : 'mysamplename'


 ^C--Press CTRL+C keys.
 Import> stop_job=immediate
 Are you sure you wish to stop this job ([yes]/no): yes

Now alter the table definition.

 SQL> alter table imptest modify name varchar2(20);

 Table altered.

 SQL> exit

 [oracle@localhost]$ impdp jay/password attach=SYS_IMPORT_TABLE_01--- you can get job name from log file.

 Import> start_job

 Import> continue_client
 Job SYS_IMPORT_TABLE_01 has been reopened at Tue Oct 11 07:39:12 2016 
 Restarting "JAY"."SYS_IMPORT_TABLE_01":  jay/password directory=dp_dir tables=imptest,imptest2 content=data_only dumpfile=exp_jay_imptest.dmp 
 . . imported "JAY"."IMPTEST"                             19.00 MB 1049211 out of 1048576 rows
 . . imported "JAY"."IMPTEST2"                            7.710 KB     396 rows
 Job "JAY"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 11 07:40:13 2016 elapsed 0 00:01:02
Related Question