• 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.