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)