Materialized View does not import properly when importing on a second instance of a database
- by marinus
When I import a database with materialized view mv_mt in just one database (Oracle) everything is ok.
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 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?
Regards,
Marinus
