Check datapump expdp/impdp job status

 As we learned how to use data pump export/import utilities here, now we will discuss checking the datapump job and taking action on it like terminating the job or resuming, etc.

We will take backup of full database, there is a way to add job name to backup using JOB_NAME parameter, if do not use this parameter, the oracle will automatically generate one job in the database which can be checked under dba_datapump_jobs table or can be checked at server level logs of the expdp/impdp

Below is the command I am using to take export of full database 

expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Here,

I have created directory export_dir pointing to a physical location having read write access, from the command prompt, I executed the command to take export of full database

C:\Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:44:50 2022

Version 19.3.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

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/STATISTICS/MARKER

While this was running, I opened a new command prompt and logged in to the database and checked view dba_datapump_jobs

SQL> select owner_name, job_mode, operation,job_name from dba_datapump_jobs;

OWNER_NAME  JOB_MODE  OPERATION  JOB_NAME

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

TECHNO_USER  FULL      EXPORT     SYS_EXPORT_FULL_01

here I noted job name SYS_EXPORT_SCHEMA_01 and the same can be found from the export command log

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

 

Once you get the job name, use the below command to check the status of the export taken using expdp

 

C:\Users\shaik>expdp userid =techno_user@technopdb attach=SYS_EXPORT_FULL_01

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:45:38 2022

Version 19.3.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: SYS_EXPORT_FULL_01

  Owner: TECHNO_USER

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 19C1A2C6616645CDB516452D40951467

  Start Time: Tuesday, 14 June, 2022 20:44:54

  Mode: FULL

  Instance: technodb

  Max Parallelism: 1

  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        userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

     TRACE                 0

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Job heartbeat: 6

  Dump File: C:\DOWNLOADS\ARCH\EXPDP_FULL_DB.DMP

    bytes written: 135,168

Here you can see almost every detail of the export job running. Using the help command, you can get a list of activities you can perform

the job can be killed using the kill immediate command

Export> kill job=immediate;

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

In this way, you can do a lot of things with export attach command.