Managing datapump export jobs

Datapump is really a versatile tool for managing schema level backups from a DBA perspective. Now DBAs can easily create duplicate schemas with less effort if you compare the job needed to perform same with old export/import. But there is a small learning curve involved with datapump implementation especially if you kill a datapump export script due to some space issues or any other reasons.
Even if job is killed and corresponding dmp files are deleted, you won't start seeing the free space from file system level. If job is running and you wish to abort it, best way to perform is through datapump commands.


So first check datapump job status

SQL> select * from dba_datapump_jobs;
OWNER_NAME  JOB_NAME  OPERATION   JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
OPS$ORACLE SYS_EXPORT_FULL_01EXPORTFULL NOT RUNNING 0 0 0

Now if you wish to stop or kill this job, attach to this job session using following command

[oracle@prdpxdb003:/backup/mad1/exp] $ expdp / attach=SYS_EXPORT_FULL_01
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 06 May, 2010 9:11:50
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_EXPORT_FULL_01 Owner: OPS$ORACLE Operation: EXPORT Creator Privs: FALSE GUID: 858D4FB8E1B280C4E0430A9A1D5980C4 Start Time: Thursday, 06 May, 2010 9:11:53 Mode: FULL Instance: pjda1 Max Parallelism: 1 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND parfile=/********backup/********pjda1/********exp/********pjda1_dp_export_05012010_111300.par State: IDLING Bytes Processed: 102,211,472,336 Percent Done: 79 Current Parallelism: 1 Job Error Count: 0 Dump File: /backup/mad1/exp/mad1_dp_export_05012010_111300.dmp bytes written: 105,503,084,544 Worker 1 Status: State: UNDEFINED Object Schema: JDAINTFC Object Name: INITIALLOAD_HIST Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Completed Objects: 7 Total Objects: 3,760 Completed Rows: 49,405,939 Worker Parallelism: 1

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

This will delete datapump dmp file created as part of expdp and will free up disk space.
In case you did not follow this method and manually killed running script and delete dump files, you will not be able to reclaim disk space until you delete datapump related tables as follows.


Here are the steps to perform that
1) Query dba_datapump_jobs table to find job_name

select a.OWNER_NAME,a.JOB_NAME,b.sid,b.serial#,username from dba_datapump_sessions a, v$session b where a.saddr=b.saddr;
OWNER_NAME JOB_NAME SID SERIAL# USERNAME
OPS$ORACLE SYS_EXPORT_FULL_06 465 54925 OPS$ORACLEOPS$ORACLE SYS_EXPORT_FULL_06 478 50840 OPS$ORACLE


select * from dba_objects where object_name='SYS_EXPORT_FULL_06';
OWNER OBJECT_NAME  SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
OPS$ORACLE SYS_EXPORT_FULL_06 76558 76558 TABLE 30-APR-10 30-APR-10 2010-04-30:11:13:07 VALID N N N

2) For every job_name there is associated table in oracle dictionary. Drop that table.
SQL> drop table SYS_EXPORT_FULL_06 purge;
Table dropped.