Skip to Main Content
  • Questions
  • Trying to stop export import job and get an error

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, jessy.

Asked: October 05, 2021 - 4:56 am UTC

Last updated: October 08, 2021 - 2:43 am UTC

Version: 19.3.0.0

Viewed 1000+ times

You Asked

hi tom i wanted to ask about interactive command(CTRL+C) when using datapump import export


when i use an export utility and trying to kill the export jobs it works with no problem


expdp test/asdzxc@pdb22 directory=asdzxc dumpfile=countries.dump reuse_dumpfiles=y schemas=test

Export: Release 19.0.0.0.0 - Production on Tue Oct 5 11:46:24 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
Starting "TEST"."SYS_EXPORT_SCHEMA_02":  test/********@pdb22 directory=asdzxc dumpfile=countries.dump reuse_dumpfiles=y schemas=test
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes



but when i try to stop the export job using stop_job i get an error
expdp test/asdzxc@pdb22 directory=asdzxc dumpfile=countries.dump reuse_dumpfiles=y schemas=test

Export: Release 19.0.0.0.0 - Production on Tue Oct 5 11:50:08 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
Starting "TEST"."SYS_EXPORT_SCHEMA_02":  test/********@pdb22 directory=asdzxc dumpfile=countries.dump reuse_dumpfiles=y schemas=test
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Export> stop_job

UDE-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1849
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4791
ORA-06512: at "SYS.DBMS_DATAPUMP", line 6204
ORA-06512: at line 1


how does this error happen and how to fix it



and Connor said...

When you interrupt an import, it does NOT stop, it simply has given you a chance to interact with the *running* job.

For example I started my import and then Ctrl-C

C:\Users\hamcdc>impdp userid=system/admin@db19_pdb1 dumpfile=scott.dmp directory=temp schemas=scott

Import: Release 19.0.0.0.0 - Production on Fri Oct 8 10:39:04 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  userid=system/********@db19_pdb1 dumpfile=scott.dmp directory=temp schemas=scott
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Import>


In another session, I look at the tables in the SCOTT schema

SQL> select table_name
  2  from   dba_tables
  3  where  owner = 'SCOTT';

TABLE_NAME
------------------------------
OLD_EMP
NEW_EMP
AUD$MAINT_LOG
AUD$AUDIT_UTIL_UPDATE_TRIG
DEPT
AUD$SCHEMA_LIST

(wait a few secs)

SQL> select table_name
  2  from   dba_tables
  3  where  owner = 'SCOTT';

TABLE_NAME
------------------------------
OLD_EMP
NEW_EMP
AUD$MAINT_LOG
AUD$AUDIT_UTIL_UPDATE_TRIG
DEPT
AUD$SCHEMA_LIST
T1
EMP2
T
EMP
SALGRADE
BONUS
ANYTHING_I_WANT
AUD$AUDIT_HEADER
AUD$EMP
AUD$EMP2


So when I type "stop_job" the job might have already finished... hence

Import> stop_job

UDI-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1849
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4791
ORA-06512: at "SYS.DBMS_DATAPUMP", line 6204
ORA-06512: at line 1



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.