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