Overview

When migrating data using tbexport / tbimport, if the tbexport backup file does not contain jobs, you can extract the JOB creation commands by executing the query below and then create the jobs.

Method

1. Query DBA_JOBS

SET LINE 200
COL USERNAME FOR A15
COL WHAT FOR 100
COL INTERVAL FOR A30
COL WHAT FOR A50
SELECT JOB AS JOB_NO, B.USERNAME, WHAT, TO_CHAR(NEXT_DATE, 'YYYY/MM/DD HH24:MI:SS') AS NEXT_DATE, TO_CHAR(LAST_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LAST__DATE, INTERVAL, BROKEN
FROM DBA_JOBS A, DBA_USERS B
WHERE A.SCHEMA_USER=B.USER_ID;

2. Extract JOB Creation Commands

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

SET LINE 200
SET PAGES 100
SELECT 'DECLARE job_no number;'|| chr(10) ||  'begin ' || chr(10) || ' dbms_job.submit (job_no,' || '''' || what||''',' || '''' || next_date || ''','|| '''' || interval || ''');' || chr(10) || ' end;' || chr(10) || '/' as "CREATE_JOB" FROM dba_jobs;