Pavan DBA's Blog

The DBA Knowledge Store

How to delete/remove non executing datapump jobs?

Posted by Pavan DBA on July 12, 2011


Sometimes, we may get a requirement to delete datapump jobs which are stopped abruptly due to some reason. The following steps will actually help us to do that

1. First we need to identify which jobs are in NOT RUNNING status. For this, we need to use below query (basically we are getting this info from dba_datapump_jobs)

SET lines 200

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;

The above query will give the datapump jobs information and it will look like below

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
———- ——————- ——— ——— ———– ——–
SCOTT      SYS_EXPORT_TABLE_01 EXPORT    TABLE     NOT RUNNING        0
SCOTT      SYS_EXPORT_TABLE_02 EXPORT    TABLE     NOT RUNNING        0
SYSTEM     SYS_EXPORT_FULL_01  EXPORT    FULL      NOT RUNNING        0

In the above output, you can see state is showing as NOT RUNNING and those jobs need to be removed.

Note: Please note that jobs state will be showing as NOT RUNNING even if a user wantedly stopped it. So before taking any action, consult the user and get confirmed

2. we need to now identify the master tables which are created for these jobs. It can be done as follows

SELECT o.status, o.object_id, o.object_type,
       o.owner||’.’||object_name “OWNER.OBJECT”
  FROM dba_objects o, dba_datapump_jobs j
 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
   AND j.job_name NOT LIKE ‘BIN$%’ ORDER BY 4,2;

STATUS   OBJECT_ID OBJECT_TYPE  OWNER.OBJECT
——- ———- ———— ————————-
VALID        85283 TABLE        SCOTT.EXPDP_20051121
VALID        85215 TABLE        SCOTT.SYS_EXPORT_TABLE_02
VALID        85162 TABLE        SYSTEM.SYS_EXPORT_FULL_01

3. we need to now drop these master tables in order to cleanup the jobs

SQL> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;
SQL> DROP TABLE SCOTT.SYS_EXPORT_TABLE_02 ;
SQL> DROP TABLE SCOTT.EXPDP_20051121;

4. Re-run the query which is used in step 1 to check if still any jobs are showing up. If so, we need to stop the jobs once again using STOP_JOB parameter in expdp or DBMS_DATAPUMP.STOP_JOB package

Some imp points:

1. Datapump jobs that are not running doesn’t have any impact on currently executing ones.
2. When any datapump job (either export or import) is initiated, master and worker processes will be created.
3. When we terminate export datapump job, master and worker processes will get killed and it doesn’t lead to data courrption.
4. But when import datapump job is terminated, complete import might not have done as processes(master & worker)  will be killed.

8 Responses to “How to delete/remove non executing datapump jobs?”

  1. simphiwe said

    Kudos to you Pavan. You are a star..

  2. Cole said

    Thanks.

    Just what i was looking for

  3. DocSparkle said

    Hi, Don’t forget to purge the recycle bin after dropping the tables. Oracle’s document 336014.1 is an excellent reference too.

  4. muralikrishna said

    hi pavan

    i am muralikrishna, could you please explain me about streams pool and how it is related to export & import process

    • Pavan DBA said

      Data Pump uses the Oracle Streams function. If the Streams Pool is not explicitly configured with the STREAMS_POOL_SIZE parameter, Oracle tries to allocate a Streams Pool of 10% of the SGA size from the buffer cache if you use Streams for the first time in SGA. Therefore, the buffer cache then shrinks accordingly. If you want to shift parts of the SGA memory dynamically from one pool to another, you need a dynamic SGA (see Note 617416). If the size of the buffer cache is specified using the obsolete DB_BLOCK_BUFFERS parameter, SGA is not dynamic and the system issues error ORA-00832.

  5. anil said

    While taking advantage of parallel option in expdp dont use .dmp on the command line i.e

    expdp system/********@dbprod1 directory=expdp dumpfile=scott%U logfile=scottexp.log parallel=4 schemas=scott

    here by giving scott%U with parallel=4

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: