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
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""
 "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,"
 "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?


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.


... 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).

  • Is there a way to import the database without having to manually add a job? I just want to import the same db with the materialized view into another schema. – marinus Jul 9 '12 at 11:05
  • Remove the next keyword and it will work. Don't forget to add the job manually!!! – Ohadi Jul 9 '12 at 14:19

To complete Phil's answer, the only workaround I found is to recreate manually the jobs using DBMS_JOB.SUBMIT() instead of DBMS_JOB.ISUBMIT() + a job ID.

Example for the 1st job:

INTERVAL=>'sysdate + 1 / 24 / 60 / 6',NO_PARSE=>TRUE); END;

I suggest to save all those job definitions using DBMS_JOB.SUBMIT() in a yourschema_jobs.sql file, and run it manually after each future schema import showing such errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.