Clean Oracle Data Pump Jobs

Posted on Posted in Oracle DB

Occasionally we need to kill a long running Oracle import jobs ,we do it using kill -9 the job finish but this kill doesn’t completely kill the job and locks up the underlying db objects, increasing the tablespace for the user that ran the job. To resolve this, you need to run the impdp utility and kill the job.

A normal impdp process:

  • Impdp statement
impdp system/******* SCHEMAS=SCHEMA DIRECTORY=BCK FILE=export_SCHEMA.dmp log=import_SCHEMA.log
  • Check the job is running:
SQL> select job_name, state from dba_datapump_jobs;

JOB_NAME		       STATE
------------------------------ ------------------------------
SYS_IMPORT_SCHEMA_02	       EXECUTING
SYS_EXPORT_SCHEMA_01	       NOT RUNNING
SYS_IMPORT_SCHEMA_01	       NOT RUNNING
SYS_EXPORT_SCHEMA_02	       NOT RUNNING
  • The running impdp process

A impdp process but for some reasson you need to kill

Import: Release 11.2.0.3.0 - Production on Sab May 19 21:42:54 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=export_SCHEMA.dmp" Location: Command Line, Replaced with: "dumpfile=export_SCHEMA.dmp"
Legacy Mode Parameter: "log=import_SCHEMA.log" Location: CommandSYS_IMPORT_SCHEMA_02 Line, Replaced with: "logfile=import_SCHEMA.log"
La tabla maestra "system"."SYS_IMPORT_SCHEMA_02" se ha cargado/descargado correctamente
Iniciando "system"."SYS_IMPORT_SCHEMA_02":  system/******** SCHEMAS=SCHEMA DIRECTORY=BCK dumpfile=export_SCHEMA.dmp logfile=import_SCHEMA.log
Procesando el tipo de objeto SCHEMA_EXPORT/USER
Procesando el tipo de objeto SCHEMA_EXPORT/system_GRANT
Procesando el tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Procesando el tipo de objeto SCHEMA_EXPORT/TABLESPACE_QUOTA
Procesando el tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Procesando el tipo de objeto SCHEMA_EXPORT/SYNONYM/SYNONYM
Procesando el tipo de objeto SCHEMA_EXPORT/TYPE/TYPE_SPEC
Procesando el tipo de objeto SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Procesando el tipo de objeto SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Procesando el tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Procesando el tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
. . "SCHEMA"."TABLE1"		19.83 MB  222633 filas importadas
. . "SCHEMA"."TABLE2"		12.90 MB  117795 filas importadas
. . "SCHEMA"."TABLE3"		11.24 MB   82927 filas importadas
. . "SCHEMA"."TABLE4"		7.148 MB   84871 filas importadas
. . "SCHEMA"."TABLE5"		7.119 MB   84544 filas importadas
. . "SCHEMA"."TABLE6"		4.203 MB   30933 filas importadas
. . "SCHEMA"."TABLE7"		4.934 MB   62680 filas importadas
. . "SCHEMA"."TABLE8"		4.972 MB   51650 filas importadas
. . "SCHEMA"."TABLE9"		1.743 MB   26435 filas importadas
. . "SCHEMA"."TABLE10"		863.2 KB    5931 filas importadas
. . "SCHEMA"."TABLE11"		1.501 MB   16086 filas importadas
KilledSYS_IMPORT_SCHEMA_02
  • The impdp is running

Like you see the impdp process don’t finished.

SQL> select job_name, state from dba_datapump_jobs;

JOB_NAME		       STATE
------------------------------ ------------------------------
SYS_IMPORT_SCHEMA_02	       EXECUTING
SYS_EXPORT_SCHEMA_01	       NOT RUNNING
SYS_IMPORT_SCHEMA_01	       NOT RUNNING
SYS_EXPORT_SCHEMA_02	       NOT RUNNING
  • Run Impdp utility

Run the impdp utility and kill the process:

impdp system/******* attach=system.SYS_IMPORT_SCHEMA_02
Import: Release 11.2.0.3.0 - Production on Sab May 19 21:55:38 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Job: SYS_IMPORT_SCHEMA_02
  Owner: system
  Operation: IMPORT
  Creator Privs: TRUE
  GUID: C06A6B4EAEB4122ER0434EDH74850F
  Start Time: Sabado, 19 Mayo, 2012 21:55:04
  Mode: SCHEMA
  Instance: BD1
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** SCHEMAS=SCHEMA DIRECTORY=BCK dumpfile=export_SCHEMA.dmp logfile=export.SCHEMA.log reuse_dumpfiles=true
  IMPORT Job Parameters:
     CLIENT_COMMAND        system/******** SCHEMAS=SCHEMA DIRECTORY=BCK dumpfile=export_SCHEMA.dmp logfile=import_SCHEMA.log
  State: EXECUTING
  Bytes Processed: 90.681.272
  Percent Done: 98
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /bck/export_SCHEMA.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: SCHEMA
  Object Name: TABLE_EXAMPLE
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 28
  Completed Bytes: 90.832
  Worker Parallelism: 1

Import>

You can see the status of the job:

Import> STATUS
Job: SYS_IMPORT_SCHEMA_02
  Operation: IMPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 91.676.344
  Percent Done: 99
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /bck/export_SCHEMA.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: SCHEMA
  Object Name: TABLE_OBJECTS_EXAMPLE
  Object Type: SCHEMA_EXPORT/TABLE/INDEX/INDEX
  Completed Objects: 38
  Worker Parallelism: 1

Kill the job:

Import> kill_job
Are you sure you wish to stop this job ([yes]/no):
  • Remove temporary table

When data pump run the metadata is saved in a temporary Table, connect with the user that ran the job and drop the table:

sqlplus / as sysdba
SQL> drop table SYS_IMPORT_SCHEMA_02;
Table dropped.

One thought on “Clean Oracle Data Pump Jobs

  1. Very useful information, especially when the impdp command seems to hang for nearly hours. There we can see that it’s still inserting rows. If the shell running the command is killed, CONTINUE_CLIENT command may be the good one.

Leave a Reply

Your email address will not be published. Required fields are marked *