Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: Problem while importing Materialized View

    Hello All..

    I have some problems while importing materialized view from one schema to another. Following is the scenario:

    Database: Oracle 10.2.0
    OS: Windows 2003 Server

    I have a materialized view created with REFRESH FORCE ON COMMIT option. The view is getting refreshed automatically when ever any updations happen in the base tables. However when i create another schema importing all the objects from the current schema, changes done on the base tables in the new schema are not visible in the materialized view automatically. Please let me know whether we can import materialized view from one schema to another. Following is the statement i'm using to create materialized view:

    CREATE MATERIALIZED VIEW test_mv
    TABLESPACE TS_A
    REFRESH FORCE ON COMMIT
    AS
    SELECT A.COL1, A.COL2, B.COL3, B.COL4
    FROM TABLE1 A,TABLE2 B
    WHERE A.COL1=B.COL3 AND A.COL2=B.COL4;

    Also i'm seeing the following error in the log file for my import command:

    IMP-00017: following statement failed with ORACLE error 23421:
    "BEGIN dbms_refresh.make('"TEST_SCHEMA"."TEST_MV"',list=> null,next"
    "_date=>null,interval=>null,implicit_destroy=>TRUE ,lax=>FALSE,job=>941,rollb"
    "ack_seg=>NULL,push_deferred_rpc=>TRUE,refresh_aft er_errors=>FALSE,purge_opt"
    "ion => 1,parallelism => 0,heap_size => 0); END;"
    IMP-00003: ORACLE error 23421 encountered
    ORA-23421: job number 941 is not a job in the job queue
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
    ORA-06512: at "SYS.DBMS_IJOB", line 529
    ORA-06512: at "SYS.DBMS_REFRESH", line 86
    ORA-06512: at "SYS.DBMS_REFRESH", line 62
    ORA-06512: at line 1

    Thanks in Advance
    Amith Vemuganti

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    just recreate manually.
    it looks like the refresh job is causing problems.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I agree ... from my experience (I really wouldn't know whether it is described in documentation or not), some views tend to be imported in invalid state. The simplest way to solve this problem is to let import utility do its job and then recompile invalid views in order to make them valid.

    Snapshots are also views, materialized ones. Perhaps that's the issue with them as well. I believe that the simplest way is to recreate them manually.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by Amith Vemuganti
    Also i'm seeing the following error in the log file for my import command:

    IMP-00017: following statement failed with ORACLE error 23421:
    "BEGIN dbms_refresh.make('"TEST_SCHEMA"."TEST_MV"',list=> null,next"
    "_date=>null,interval=>null,implicit_destroy=>TRUE ,lax=>FALSE,job=>941,rollb"
    "ack_seg=>NULL,push_deferred_rpc=>TRUE,refresh_aft er_errors=>FALSE,purge_opt"
    "ion => 1,parallelism => 0,heap_size => 0); END;"
    IMP-00003: ORACLE error 23421 encountered
    ORA-23421: job number 941 is not a job in the job queue
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
    ORA-06512: at "SYS.DBMS_IJOB", line 529
    ORA-06512: at "SYS.DBMS_REFRESH", line 86
    ORA-06512: at "SYS.DBMS_REFRESH", line 62
    Are you also importing the job queue in which the refresh was defined?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •