客户疑问，11.2.0.3expdp按照用户进行导出，导入到11.2.0.4环境下，确认JOB是否导出；
JOB测试
1.疑问,什么情况下JOB是正常的;
2.数据泵expdp,impdp是否会迁移JOB;
3.如何手工迁移JOB
一、 创建测试JOB,模拟正常现象
create table TT(C_DATE DATE); create or replace procedure PRO_TT is begin insert into TT values(sysdate); commit; end; / exec PRO_TT; SQL> select * from tt; declare job number; begin dbms_job.submit(job,'PRO_TT;',sysdate,'sysdate+1/1440'); end; /
SQL> col what for a20
SQL> select JOB,WHAT,BROKEN from user_jobs
JOB WHAT B
---------- -------------------- -
3 PRO_TT; N
begin
dbms_job.run(3);
end;
/
SQL> select * from tt;
C_DATE
------------
08-JAN-20
08-JAN-20
二 、模拟数据泵导出，观察是否能迁移JOB
$expdp scott/tiger directory=dump dumpfile=scott.dmp
. . exported "SCOTT"."T_PART_TABLE":"PART_P180613" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/scott.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.265 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
. . exported "SCOTT"."A1":"P1" 3.876 MB 9575 rows
impdp scott/tiger directory=dump dumpfile=scott.dmp sqlfile=scott.sql
-- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
CREATE procedure PRO_TT is
begin
insert into TT values(sysdate);
commit;
end;
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ALTER PROCEDURE "SCOTT"."PRO_TT"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED
PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
REUSE SETTINGS TIMESTAMP '2020-01-08 03:13:00'
/
-- new object type path: SCHEMA_EXPORT/JOB
BEGIN SYS.DBMS_IJOB.SUBMIT(
JOB=> 3,
LUSER=> 'SCOTT',
PUSER=> 'SCOTT',
CUSER=> 'SCOTT',
NEXT_DATE=> TO_DATE('2020-01-08 03:19:32', 'YYYY-MM-DD:HH24:MI:SS'),
INTERVAL=> 'sysdate+1/1440',
BROKEN=> FALSE,
WHAT=> 'PRO_TT;',
NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$''
NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,''
NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY''',
ENV=> '0102000200000000');
END;
/
发现存在BEGIN SYS.DBMS_IJOB.SUBMIT调用这个包的过程,但是如果再新环境存在这个JOB编号，可能会导致创建失败。（未测试)
手工删除JOB后，实际导入，JOB正常创建，无异常现象。
begin
dbms_job.remove(3);
commit;
end;
select JOB,WHAT,BROKEN from user_jobs;
impdp scott/tiger directory=dump dumpfile=scott.dmp INCLUDE=JOB
SQL> select JOB,WHAT,BROKEN from user_jobs
JOB WHAT B
---------- -------------------- -
3 PRO_TT; N
说明数据泵可以迁移JOB，如果需要单独迁移可以使用数据泵参数 --导出也可以指定用户类型对象JOB导出
expdp scott/tiger directory=dump dumpfile=scott.dmp
impdp scott/tiger directory=dump dumpfile=scott.dmp INCLUDE=JOB
三、手工迁移数据泵JOB
1.尽可能精选化JOB需要迁移的范围，直说结论，排除NEXT_DATE=4000/1/1的，因为JOB不会再执行了，并且排除BROKEN=Y的损坏的JOB，以及排除用户SYS,SYSMAN等系统用户
2.对比JOB调用的NEXT_DATE时间，INTERVAL，SCHEMA_USER用户完全一致的情况下，存在无需迁移，不存在需要手工迁移
3.迁移方式expdp，按照上述expdp导出JOB，然后导入
手工，根据调用的what 存储过程，以及NEXT_DATE下一次的执行时间，SCHEMA_USER用户,INTERVAL间隔调用参数，手工重建。
查询DBA_JOBS视图，官方文档视图定义。
BROKENY: no attempt is made to run this job YES这个损坏了，不会再尝试运行此作业
N: an attempt is made to run this job NO还没怀！
Is the job BROKEN?
select job,broken from dba_jobs where job=<job_number>;
If broken, then check the alert log and trace files to diagnose the issue.
To remove broken status
EXEC DBMS_JOB.BROKEN(<job_no>,FALSE);
Oracle官方定义未找到，找到一个帖子，说明粘贴如下
https://community.oracle.com/thread/435918
The job gets broken, if it encounters 16 contiguous failures (which is this case) or you explicitly
call the dbms_job.broken(job_id, true). In both cases, the atribute broken is set to Y and next_date is set to 1-1-4000.
The next_date will be reset back when you reset the broken state using dbms_job.broken(job_id, false).
However, the failure counter is not reset back to 0 until succesfull completion of the job. That means,
if there is an error in the job procedure (the one specified in what attribute), the job will get broken again
after the first attempt to execute it.
You can manualy force the job to run by calling dbms_job.run(job_id). Look in your alert log,
if there is an error reported for the procedure executed by this job.
经过查找了一些资料，了解到，假如某一个JOB执行的失败次数，连续超过了16次，那么该JOB的NEXT_TIME就会变成了4000/1/1日，即不再让该JOB执行了，
也即该JOB已被数据库认定为失效JOB。
实际测试，删除存储过程，让JOB跑失败。但是失败8次后，JOB=3的，Broken=Y，不清楚是否会计算累加,并且NEXT_DATE正常。 JOB LOG_USER PRIV_USER SCHEMA_USE INTERVAL WHAT B NEXT_DATE ---------- ---------- ---------- ---------- ------------------------------ -------------------- - ------------ 23 SCOTT SCOTT SCOTT sysdate+3/1440 PRO_TT; N 08-JAN-20 3 SCOTT SCOTT SCOTT sysdate+1/1440 PRO_TT; Y 01-JAN-00 经过查找了一些资料，了解到，假如某一个JOB执行的失败次数，连续超过了16次，那么该JOB的NEXT_TIME就会变成了4000/1/1日，
即不再让该JOB执行了，也即该JOB已被数据库认定为失效JOB。 Errors in file /11.2.0.4/app/oracle/diag/rdbms/gbk11/gbk11/trace/gbk11_j000_11578.trc: ORA-12012: error on auto execute of job 23 ORA-06550: line 1, column 96: PLS-00905: object SCOTT.PRO_TT is invalid ORA-06550: line 1, column 96: PL/SQL: Statement ignored
--JOB创建中调用的WHAT=PRO_TT, 创建JOB用户是SCOTT,当前执行时间SYSDATE立即，下一次间隔时间=INTERVAL
declare job number; begin dbms_job.submit(job,'PRO_TT;',sysdate,'sysdate+1/1440'); end; /