0

on the database 10g we have both jobs created with the deprecated DBMS_JOB (select * from all_jobs;) and created with the new DBMS_SCHEDULER (select * from all_scheduler_jobs;). Is there a way to distinguish between the two kinds of jobs when exporting the whole database from production to test instances? Or anyway a way to not export only some particular jobs?

Thanks!

Mark

Mark
  • 9
  • 5
  • Does this answer your question? [Script to disable all jobs in Oracle (DBMS\_JOB package)?](https://stackoverflow.com/questions/5586051/script-to-disable-all-jobs-in-oracle-dbms-job-package). You could set the job_queue_processes to 0 before importing and then disable the jobs. That way it's not needed to exclude them from the export. – Koen Lostrie Oct 05 '21 at 18:12

1 Answers1

0

To Exclude DBMS_JOB:

During export : expdp ... EXCLUDE=job During import : impdp ... EXCLUDE=job

To exclude DBMS_SCHEDULER jobs

Create a parameter file with:

exclude=procobj:"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,49,50,66,67,68,69,71,72,74))"

Then execute Data Pump Export:

expdp ... parfile=expdp.par

Diego
  • 1
  • 2