Stop A Running Datapump (expdp/impdp) Job Started In the Background

Stop A Running Datapump (expdp/impdp) Job Started In the Background

This article demonstrates how to stop a running datapump (expdp/impdp) job started in the background.

Assuming a job was started in the background on Linux this way:

$ nohup ./imp_SCHEMAS_script.sh > SCHEMA_Import.log 2>&1 &

To stop the running job, obtain the job name.

SQL> select owner_name, job_name,STATE from dba_datapump_jobs;

OWNER_NAME           JOB_NAME                       STATE
-------------------- ------------------------------ --------------------
SYSTEM               UATDB_2024_09_10_07_57_00_IMP  EXECUTING

Once the job name is known, the job can be stopped by interactively attaching to it using the ATTACH
command line option of Datapump and then stop the job.

In this case, it is an IMPDP job. From the output above, the owner of the job is SYSTEM and the job name is “UATDB_2024_09_10_07_57_00_IMP”, so in this case IMPDP must connect to the SYSTEM schema and attach to the “UATDB_2024_09_10_07_57_00_IMP” job.

Enter password when prompted.

$ impdp system attach=UATDB_2024_09_10_07_57_00_IMP

Import: Release 19.0.0.0.0 - Production on Sun Jun 23 02:59:45 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Job: UATDB_2024_09_10_07_57_00_IMP
  Owner: SYSTEM
  Operation: IMPORT
  Creator Privs: TRUE
  GUID: 1B86A1031A6E726FE0633303120AB2A5
  Start Time: Sunday, 23 June, 2024 2:43:39
  Mode: SCHEMA
  Instance: UATDB
  Max Parallelism: 20
  Timezone: +00:00
  Timezone version: 32
  Endianness: LITTLE
  NLS character set: AL32UTF8
  NLS NCHAR character set: AL16UTF16
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** FULL=Y DIRECTORY=BACKUP_DUMP DUMPFILE=EXP_20240601_%U.DMP LOGFILE=EXP_2024_06_01_04_48_22.log COMPRESSION=ALL PARALLEL=20 job_name=EXP_2024_06_01_04_48_22
     COMPRESSION           ALL
     TRACE                 0
  IMPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** DIRECTORY=DIR_DUMP DUMPFILE=EXP_20240601_%U.DMP parallel=20 logfile=IMP_UATDB_2024_06_23_02_43_17.log SCHEMAS=TEST  EXCLUDE=STATISTICS job_name=UATDB_2024_09_10_07_57_00_IMP
     TRACE                 0
  State: EXECUTING
  Bytes Processed: 116,276,928
  Current Parallelism: 20
  Job Error Count: 0
  Job heartbeat: 5
  Dump File: /u01/backup/EXP_20240601_%u.DMP
  Dump File: /u01/backup/EXP_20240601_01.DMP
  Dump File: /u01/backup/EXP_20240601_02.DMP
  Dump File: /u01/backup/EXP_20240601_03.DMP
  Dump File: /u01/backup/EXP_20240601_04.DMP
  Dump File: /u01/backup/EXP_20240601_05.DMP
  Dump File: /u01/backup/EXP_20240601_06.DMP
  Dump File: /u01/backup/EXP_20240601_07.DMP
  Dump File: /u01/backup/EXP_20240601_08.DMP
  Dump File: /u01/backup/EXP_20240601_09.DMP
  Dump File: /u01/backup/EXP_20240601_10.DMP
  Dump File: /u01/backup/EXP_20240601_11.DMP
  Dump File: /u01/backup/EXP_20240601_12.DMP
  Dump File: /u01/backup/EXP_20240601_13.DMP
  Dump File: /u01/backup/EXP_20240601_14.DMP
  Dump File: /u01/backup/EXP_20240601_15.DMP
  Dump File: /u01/backup/EXP_20240601_16.DMP
  Dump File: /u01/backup/EXP_20240601_17.DMP
  Dump File: /u01/backup/EXP_20240601_18.DMP
  Dump File: /u01/backup/EXP_20240601_19.DMP
  Dump File: /u01/backup/EXP_20240601_20.DMP
  Dump File: /u01/backup/EXP_20240601_21.DMP
  Dump File: /u01/backup/EXP_20240601_22.DMP

Worker 1 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:38
  Object status at: Sunday, 23 June, 2024 2:55:40
  Process Name: DW00
  State: WORK WAITING

Worker 2 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:37
  Object status at: Sunday, 23 June, 2024 2:55:38
  Process Name: DW01
  State: WORK WAITING

Worker 3 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:32
  Object status at: Sunday, 23 June, 2024 2:55:32
  Process Name: DW02
  State: EXECUTING
  Object Schema: TEST
  Object Name: CSPKS_INTEREST
  Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
  Completed Objects: 320
  Total Objects: 13,736
  Worker Parallelism: 1

Worker 4 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:38
  Object status at: Sunday, 23 June, 2024 2:55:40
  Process Name: DW03
  State: WORK WAITING

Worker 5 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:37
  Object status at: Sunday, 23 June, 2024 2:55:38
  Process Name: DW04
  State: WORK WAITING

Worker 6 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:37
  Object status at: Sunday, 23 June, 2024 2:55:38
  Process Name: DW05
  State: WORK WAITING

Worker 7 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:38
  Object status at: Sunday, 23 June, 2024 2:55:40
  Process Name: DW06
  State: WORK WAITING

Worker 8 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:37
  Object status at: Sunday, 23 June, 2024 2:55:40
  Process Name: DW07
  State: WORK WAITING

Worker 9 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:37
  Object status at: Sunday, 23 June, 2024 2:55:38
  Process Name: DW08
  State: WORK WAITING

Worker 10 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:29
  Object status at: Sunday, 23 June, 2024 2:55:29
  Process Name: DW09
  State: EXECUTING
  Object Schema: TEST
  Object Name: AEPKS_AEDEDMTR_MAIN
  Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
  Completed Objects: 160
  Total Objects: 13,736
  Worker Parallelism: 1

Worker 11 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:25
  Object status at: Sunday, 23 June, 2024 2:55:25
  Process Name: DW0A
  State: EXECUTING
  Object Schema: TEST
  Object Name: IAPKS_IARACVER_MAIN
  Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
  Total Objects: 13,736
  Worker Parallelism: 1

Worker 12 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:38
  Object status at: Sunday, 23 June, 2024 2:55:38
  Process Name: DW0B
  State: WORK WAITING

Worker 13 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:38
  Object status at: Sunday, 23 June, 2024 2:55:40
  Process Name: DW0C
  State: WORK WAITING

Worker 14 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:38
  Object status at: Sunday, 23 June, 2024 2:55:38
  Process Name: DW0D
  State: WORK WAITING

Worker 15 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:37
  Object status at: Sunday, 23 June, 2024 2:55:38
  Process Name: DW0E
  State: WORK WAITING

Worker 16 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:38
  Object status at: Sunday, 23 June, 2024 2:55:40
  Process Name: DW0F
  State: WORK WAITING

Worker 17 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:56:03
  Object status at: Sunday, 23 June, 2024 2:56:04
  Process Name: DW0G
  State: WORK WAITING

Worker 18 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:36
  Object status at: Sunday, 23 June, 2024 2:55:36
  Process Name: DW0H
  State: WORK WAITING

Worker 19 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:37
  Object status at: Sunday, 23 June, 2024 2:55:39
  Process Name: DW0I
  State: WORK WAITING

Worker 20 Status:
  Instance ID: 1
  Instance name: UATDB
  Host name: UATDB-0004
  Object start time: Sunday, 23 June, 2024 2:55:40
  Object status at: Sunday, 23 June, 2024 2:55:40
  Process Name: DW0J
  State: WORK WAITING
Import>

At the prompt, issue the stop_job=immediate command and answer “yes” to stop the job.

Import> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): yes

$
[1]+  Exit 1            nohup ./imp_SCHEMAS_script.sh > SCHEMA_Import.log 2>&1

Confirm the status of the job in DBA_DATAPUMP_JOBS view

SQL> select owner_name, job_name,STATE from dba_datapump_jobs;

OWNER_NAME           JOB_NAME                       STATE
-------------------- ------------------------------ --------------------
SYSTEM               UATDB_2024_09_10_07_57_00_IMP  NOT RUNNING
Tags: , ,