Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    How to stop Oracle Data Pump job

    • Article Type: General
    • Product: Aleph
    • Product Version: 19.01

    Description:
    Is there a way of killing Oracle Data Pump job, once it has started, in case of problems - running out of space is most likely?

    Resolution:
    To kill the job:

    ps -ef|grep dp
    kill [pid number]

    This will stop the job. Then you will need to clear the traces of the job from the database:

    1. Determine in SQL*Plus which Data Pump jobs exist in the database:
    -- locate Data Pump jobs:

    aleph_admin@ALEPH1> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;
    **** Hit return to continue ****

    OWNER_NAME JOB_NAME
    ------------------------------ ------------------------------
    OPERATION
    ------------------------------------------------------------------------------------------
    JOB_MODE STATE
    ------------------------------------------------------------------------------------------ ---------------------- --------
    ATTACHED_SESSIONS
    -----------------
    ALEPH_ADMIN ABC01
    EXPORT
    TABLE NOT RUNNING
    0


    1 row selected.


    2. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be 'NOT RUNNING'.

    3. Check with the job owner that the job with status 'NOT RUNNING' in dba_datapump_jobs is not an export/import Data Pump job that has been temporary stopped, but is actually a job that failed.

    4. Determine in SQL*Plus the related master tables:

    aleph_admin@ALEPH1> 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;
    **** Hit return to continue ****

    STATUS OBJECT_ID OBJECT_TYPE
    ------- ---------- -------------------
    OWNER.OBJECT
    -----------------------------------------------------------------------------------------------------------------------------------
    VALID 110176 TABLE
    ALEPH_ADMIN.ABC01

    1 row selected.

    5. For jobs that were stopped in the past and won't be restarted anymore, delete the master table. E.g.:

    aleph_admin@ALEPH1> DROP TABLE ALEPH_ADMIN.ABC01;

    Table dropped.

    Additional Information

    Oracle Data Pump kill dp


    • Article last edited: 10/8/2013