oracle expdp 没反应,oracle10g expdp数据泵的bug,按schema导出，导入impdp时无job

首先参考MOS bug说明，该bug没有提到补丁，并且提供的方法不太方便，如果job少的话手动建上就可以了，但如果多的话实在麻烦

验证步骤

1、源端10g环境执行expdp导出

expdp usera/passwd directory=dump dumpfile=usear.dmp logfile=usera.log schemas=usera

2、目标端提前创建好用户

create user usera identified by usera;

3、目标执行导入

impdp usera/passwd directory=dump dumpfile=usear.dmp logfile=usera.log schemas=usera

4、目标端查看job

select * from dba_jobs

5、解决办法，单独导入job

经过分析日志发现，expdp导出时dmp文件中是包含job的，只是由于bug导致导入时忽略了而已

impdp usera/passwd directory=dump dumpfile=usear.dmp logfile=usera.log include=job

总结：该bug的触发条件

1、源端10g环境按schema以schema方式导出

2、目标端导入环境用户提前创建

3、目标端数据库版本10g、11g都存在该问题

4、该bug不容易被发现，需要更细心

------------------------MOS文章参考--------------------

Bug 5063330 : DATAPUMP DOESN'T CREATE USER JOBS THROUGH IMPORT

Hdr: 5063330 10.1.0.4 RDBMS 10.1.0.4 DATA PUMP IMP PRODID-5 PORTID-100 3489195

Abstract: DATAPUMP DOESN'T CREATE USER JOBS THROUGH IMPORT

*** 02/26/06 10:10 am ***

TAR:

----

5160977.993

5160977.993

PROBLEM:

--------

DATAPUMP DOESN'T CREATE USER JOBS THROUGH IMPORT

DIAGNOSTIC ANALYSIS:

--------------------

1-Create any JOB like the following one

var jobnumber NUMBER

BEGIN

DBMS_JOB.SUBMIT

(job => :jobnumber

,what =>'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''LOAD1'',''TENK'',''ESTIMATE''

,null,estimate_percent=>50);'

,next_date => TRUNC(SYSDATE+1)

,interval => 'TRUNC(SYSDATE+1)'

);

END;

/

print jobnumber

2-  expdp scott/tiger directory=my_dir dumpfile=exp_scott.dmp

logfile=exp_scott.log

3- Drop user scott

4- Create empty schema

5- impdp scott/tiger directory=my_dir dumpfile=exp_scott.dmp

logfile=imp_scott.log

WORKAROUND:

-----------

Recrerate the Jobs manually

RELATED BUGS:

-------------

REPRODUCIBILITY:

----------------

TEST CASE:

----------

1-Create any JOB like the following one

var jobnumber NUMBER

BEGIN

DBMS_JOB.SUBMIT

(job => :jobnumber

,what =>'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''LOAD1'',''TENK'',''ESTIMATE''

,null,estimate_percent=>50);'

,next_date => TRUNC(SYSDATE+1)

,interval => 'TRUNC(SYSDATE+1)'

);

END;

/

print jobnumber

2-  expdp scott/tiger directory=my_dir dumpfile=exp_scott.dmp

logfile=exp_scott.log

3- Drop user scott

4- Create empty schema

5- impdp scott/tiger directory=my_dir dumpfile=exp_scott.dmp

logfile=imp_scott.log

STACK TRACE:

------------

SUPPORTING INFORMATION:

-----------------------

24 HOUR CONTACT INFORMATION FOR P1 BUGS:

----------------------------------------

DIAL-IN INFORMATION:

--------------------

IMPACT DATE:

------------

*** 03/05/06 09:14 am *** (CHG: Sta->16)

*** 03/06/06 01:31 pm ***

*** 04/04/06 01:30 pm ***

*** 05/12/06 03:06 am ***

*** 05/12/06 03:37 am ***

*** 10/13/06 06:15 pm ***

*** 10/13/06 06:42 pm *** (CHG: Sta->36)