Script to Find and Cleanup Orphaned Datapump Jobs

advertisements

_____________________________________________________________________________________________________________________

Orphaned Data Pump jobs are jobs that have been left behind due to various reasons, such as an interruption or error during the execution of the job. These orphaned jobs are not actively running but are still present in the database. To clean up these orphaned Data Pump jobs, you can use the following script

DECLARE
  job_state VARCHAR2(30);
BEGIN
  FOR rec IN (SELECT job_name, state FROM dba_datapump_jobs WHERE state != 'COMPLETED') LOOP
    job_state := DBMS_DATAPUMP.ATTACH( rec.job_name,'SYSTEM');
    DBMS_OUTPUT.PUT_LINE('Attaching job ' || rec.job_name || ' with state ' || job_state);
    DBMS_DATAPUMP.STOP_JOB(handle => job_state);
    DBMS_OUTPUT.PUT_LINE('Stopping job ' || rec.job_name);
  END LOOP;
END;
/

advertisements

 

This PL/SQL script uses the Data Pump API to clean up orphaned Data Pump jobs. Here's what the script does: The script loops through the dba_datapump_jobs view to retrieve the names and states of Data Pump jobs that are not in the 'COMPLETED' state. For each job that is not completed, it attaches to the job using DBMS_DATAPUMP.ATTACH with the force parameter set to TRUE. It then stops the attached job using DBMS_DATAPUMP.STOP_JOB, specifying the job's handle, an abort step (set to 1), and force set to TRUE. Throughout the loop, the script uses DBMS_OUTPUT to display messages about attaching and stopping jobs. Data Pump is an Oracle utility for fast data and metadata movement between databases. It provides high-speed movement of data and can be used for both large-scale data migration and smaller, routine tasks like backups and restores. Orphaned Data Pump jobs may occur due to various reasons, including network failures, database crashes, or manual intervention. These orphaned jobs can consume system resources and impact the performance of your database.

Attaching job SYS_EXPORT_TABLE_01 with state EXECUTING
Stopping job SYS_EXPORT_TABLE_01

In this sample output, you see messages indicating that the script attached to and stopped an orphaned Data Pump job named SYS_EXPORT_TABLE_01. Please ensure that you have the necessary privileges to execute the Data Pump API procedures and access the dba_datapump_jobs view. Test the script in a controlled environment before applying it to your production database to avoid unintended consequences.

_____________________________________________________________________________________________________________________

Website Stats