delete jobs

Managing and Deleting Oracle Data Pump Jobs and Tables

Managing Oracle Data Pump jobs and tables is an essential task for database administrators to maintain a clean and efficient database environment. Whether you’re troubleshooting issues, cleaning up after failed exports, or removing unnecessary tables, understanding how to manage and delete Data Pump jobs and tables is crucial. In this article, we’ll walk you through the steps to manage and delete Oracle Data Pump jobs and tables using various methods like expdp, STOP_JOB, KILL_JOB, and dbms_job.remove.

Viewing Data Pump Jobs

1. Viewing Data Pump Jobs

To view active Data Pump jobs, you can use the following SQL query:

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs;

Deleting Data Pump Tables

2. Deleting Data Pump Tables

To delete specific Data Pump tables, you can use the DROP TABLE command:

DROP TABLE system.SYS_EXPORT_FULL_01;
DROP TABLE system.SYS_EXPORT_FULL_02;
DROP TABLE system.SYS_EXPORT_FULL_03;
DROP TABLE system.SYS_EXPORT_FULL_04;
DROP TABLE system.SYS_EXPORT_FULL_05;
DROP TABLE system.SYS_EXPORT_FULL_07;

Purging Recycle Bin

3. Purging Recycle Bin

To purge the recycle bin and permanently remove deleted objects, use the PURGE DBA_RECYCLEBIN command:

PURGE DBA_RECYCLEBIN;

Managing Data Pump Jobs Using expdp

4. Managing Data Pump Jobs

To manage Data Pump jobs using expdp, you can attach to a specific job using the ATTACH parameter:

expdp system/manager attach=SYS_EXPORT_FULL_01

5. Killing Data Pump Job

Once attached to the job, you can switch to the export> prompt using CTRL+C and use the KILL_JOB command to kill the running expdp job:

export>KILL_JOB

Stopping Data Pump Jobs Using STOP_JOB

6. Stopping Data Pump Job

Alternatively, you can stop a Data Pump job using the STOP_JOB command:

export>STOP_JOB

Removing DBMS Jobs

7. Removing DBMS Jobs

To remove specific DBMS jobs, you can use the dbms_job.remove procedure:

EXEC DBMS_JOB.REMOVE(2142);

Conclusion

Managing and deleting Oracle Data Pump jobs and tables involves various methods and commands, from viewing active jobs and deleting tables to purging the recycle bin and stopping jobs using expdp, STOP_JOB, KILL_JOB, and dbms_job.remove. Understanding these methods and commands is crucial for maintaining a clean and efficient database environment and troubleshooting issues related to Data Pump jobs and tables.

By following the comprehensive guide provided in this article, you can successfully manage and delete Oracle Data Pump jobs and tables tailored to your specific requirements.