Materialized View does not import properly when importing a db for a second time in another schema [closed]
- by marinus
When I import a database with materialized view mv_mt in just one schema there are 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?
