Stop data pump jobs in SQL Developer
Some important points should make sure before starting activity:
1. Data pump jobs that are not running doesn’t have any impact on currently executing ones.
2. When any data pump job (either export or import) is initiated, master and worker processes will be created.
3. When we terminate export data pump job, master and worker processes will get killed and it doesn’t lead to data corruption.
4. But when import data pump job is terminated, complete import might not have done as processes(master & worker) will be killed.
You can drop export/import job using the following command First of all find the name of the job table under ‘DataPump>Import Jobs or Export Jobs’.
- First of all we need to identify which jobs are in NOT RUNNING status with below query (basically we are getting this info from dba_datapump_jobs)
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;
the output of above query is as below table:
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
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
the output of above query will show as below table:
3. now we need to now drop these master tables in order to cleanup/remove jobs the jobs
SQL> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;
SQL> DROP TABLE SCOTT.SYS_EXPORT_TABLE_02 ;
SQL> DROP TABLE SCOTT.EXPDP_20051121;
or owner object can also be written in double quotes “”
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 the below query to verify 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;
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs ORDER BY 1,2;
See Also
