Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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:

BEGIN  
DBMS_JOB.SUBMIT(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;

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.

share|improve this answer
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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