How to kill interrupted Oracle impdp/expdp job.

Hi Folks ,

Whenever we need to cancel the export or import jobs , we need to follow the below mention procedure :

[oracle@india ~]$ impdp full=Y directory=DATA_PUMP_DIR dumpfile=fullexport24042017.dmp logfile=fullexport24042017.log

Import: Release 11.2.0.1.0 - Production on Sun Apr 23 17:19:43 2017

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

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** full=Y directory=DATA_PUMP_DIR dumpfile=fullexport24042017.dmp logfile=fullexport24042017.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
ORA-31684: Object type USER:"SYSMAN" already exists
ORA-31684: Object type USER:"MGMT_VIEW" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists
ORA-31684: Object type USER:"APEX_030200" already exists
ORA-31684: Object type USER:"OWBSYS" already exists
ORA-31684: Object type USER:"OWBSYS_AUDIT" already exists
ORA-31684: Object type USER:"SCOTT" already exists
ORA-31684: Object type USER:"HAMEED" already exists
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DBFS_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"ADM_PARALLEL_EXECUTE_TASK" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_SELECT_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_EXECUTE_ROLE" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JMXSERVER" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"XDB_SET_INVOKER" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" already exists
ORA-31684: Object type ROLE:"XDB_WEBSERVICES_OVER_HTTP" already exists
ORA-31684: Object type ROLE:"ORDADMIN" already exists
ORA-31684: Object type ROLE:"OLAPI_TRACE_USER" already exists
ORA-31684: Object type ROLE:"OLAP_XS_ADMIN" already exists
ORA-31684: Object type ROLE:"OLAP_DBA" already exists
ORA-31684: Object type ROLE:"CWM_USER" already exists
ORA-31684: Object type ROLE:"OLAP_USER" already exists
ORA-31684: Object type ROLE:"SPATIAL_WFS_ADMIN" already exists
ORA-31684: Object type ROLE:"WFS_USR_ROLE" already exists
ORA-31684: Object type ROLE:"SPATIAL_CSW_ADMIN" already exists
ORA-31684: Object type ROLE:"CSW_USR_ROLE" already exists
ORA-31684: Object type ROLE:"MGMT_USER" already exists
ORA-31684: Object type ROLE:"APEX_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"OWB$CLIENT" already exists
ORA-31684: Object type ROLE:"OWB_DESIGNCENTER_VIEW" already exists
ORA-31684: Object type ROLE:"OWB_USER" already exists
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
^C

** We need to stop this import
( ctrl + c )
Import> ^C

Import> kill_job ( kill_job - > Use this Keyword )
Are you sure you wish to stop this job ([yes]/no): yes

Note : The most common error which DBA make is , when they press CTRL + C to terminate the job of export/import and think that it will eventually stop. But the fact of matter is it will be running untill we kill the job.

Query to check the job status :

SQL> select *from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE

DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

----------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------------ -----------------

SYSTEM SYS_IMPORT_FULL_01 IMPORT FULL EXECUTING 1

1 3

To kill the import/export job :

Ctrl + c

Import> ^C

Import> kill_job

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

MOHAMMED ABDUL HAMEED | Welcome - Geeksss