Materialized View does not import properly when importing a db for a second time in another schema

importoracle

When I import a database with materialized view mv_mt in just one schema I get no errors.

create materialized view mv_mt
refresh complete
next trunc( sysdate ) + 1
as
SELECT sysdate, media_type.*
from media_type;

But when I try to import the same database to a copy in another schema in the same tablespace I get the following errors:

IMP-00017: following statement failed with ORACLE error 1:
 "BEGIN   DBMS_JOB.ISUBMIT(JOB=>438,WHAT=>'dbms_refresh.refresh(''"ALEXANDRA""
 "."MV_MT"'');',NEXT_DATE=>TO_DATE('2012-07-02:14:22:36','YYYY-MM-DD:HH24:MI:"
 "SS'),INTERVAL=>'sysdate + 1 / 24 / 60 / 6  ',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 23421:
 "BEGIN   dbms_refresh.make('"ALEXANDRA"."MV_MT"',list=>null,next_date=>null,"
 "interval=>null,implicit_destroy=>TRUE,lax=>FALSE,job=>438,rollback_seg=>NUL"
 "L,push_deferred_rpc=>TRUE,refresh_after_errors=>FALSE,purge_option => 1,par"
 "allelism => 0,heap_size => 0); END;"
IMP-00003: ORACLE error 23421 encountered
ORA-23421: job number 438 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 793
ORA-06512: at "SYS.DBMS_REFRESH", line 86
ORA-06512: at "SYS.DBMS_REFRESH", line 62
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 23410:
 "BEGIN   dbms_refresh.add(name=>'"ALEXANDRA"."MV_MT"',list=>'"ALEXANDRA"."MV"
 "_MT"',siteid=>0,export_db=>'ORCL01'); END;"
IMP-00003: ORACLE error 23410 encountered
ORA-23410: materialized view "ALEXANDRA"."MV_MT" is already in a refresh group
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_IREFRESH", line 484
ORA-06512: at "SYS.DBMS_REFRESH", line 140
ORA-06512: at "SYS.DBMS_REFRESH", line 125
ORA-06512: at line 1

Anyone any ideas?

Best Answer

This is happening because it's trying to create a duplicate refresh DBMS_JOB with the ID 438.

Just re-run the failing statements manually and specify an unused job ID.

SELECT MAX(JOB)+1 FROM DBA_JOBS;

... will get you the next available ID.

(You may be able to omit the JOB=> parameter and have Oracle auto-generate the ID, but I can't remember off-hand if that'll work).