Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Unanswered: problem importing materialized view in a different schema

    I have some problems while importing materialized view from one schema to another.I think it is because of difference in schema.
    he schema in DB1 is user1 and the one in DB2 is user2

    Following is the scenario:

    I have a materialized view created with REFRESH FORCE ON COMMIT option. However when I create another schema importing all the objects from the current schema, I get an error. Following is the statement i'm using to create materialized view:

    CREATE MATERIALIZED VIEW MV_TEST
    REFRESH FORCE ON DEMAND
    START WITH TO_DATE('05-06-2009 02:00:00', 'DD-MM-YYYY HH24:MIS') NEXT ROUND(SYSDATE + 1) + 2/24
    AS
    SELECT DISTINCT(A.COL1), A.COL2, B.COL3, B.COL4
    FROM TableA@dblink1 A, TableB@dblink1 B
    WHERE A.COL1=B.COL3 AND A.COL2=B.COL4;

    I get the following error in the log file for my import command:

    IMP-00017: following statement failed with ORACLE error 1031:
    "CREATE SNAPSHOT "MV_TEST" USING ("MV_TEST", (8, 'DB1.REGR"
    "ESS.RDBMS.DEV.US.ORACLE.COM', 1, 0, 0, "ARADMIN", "ORGANISATION", '2009-05-"
    "11:02:00:04', 0, 0, '2009-05-11:02:00:04', '', 0, 9204404780155, 0, NULL), "
    "1075839296, 5, ('2009-04-30:11:40:44', 0, 0, 0, 9206501216513, 0, 0, 2, NUL"
    "L, NULL), '@dblink1.REGRESS.RDBMS.DEV.US.ORACLE.COM') REFRESH FORCE AS"
    "SELECT DISTINCT(A.COL1), A.COL2, B.COL3, B.COL4"
    "FROM TableA@dblink1 A, TableB@dblink1 B"
    "WHERE A.COL1=B.COL3 AND A.COL2=B.COL4"
    ""
    IMP-00003: ORACLE error 1031 encountered
    ORA-01031: insufficient privileges
    IMP-00017: following statement failed with ORACLE error 12003:
    "ALTER SNAPSHOT "MV_TEST" COMPILE"
    IMP-00003: ORACLE error 12003 encountered
    ORA-12003: materialized view "DB2"."MV_TEST" does not exist

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Pay attention to
    Code:
    ORA-01031: insufficient privileges
    line.

  3. #3
    Join Date
    Jun 2009
    Posts
    6
    what about the first error?

    IMP-00017: following statement failed with ORACLE error 1031:
    "CREATE SNAPSHOT "MV_TEST" USING ("MV_TEST", (8, 'DB1.REGR"

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ORA-01031 was raised first, and IMP-00017 was just its consequence.

  5. #5
    Join Date
    Jun 2009
    Posts
    6
    isnt it..

    IMP-00017: following statement failed with ORACLE error 1031:"CREATE SNAPSHOT "MV_TEST" ....
    IMP-00003: ORACLE error 1031 encountered
    ORA-01031: insufficient privileges
    IMP-00017: following statement failed with ORACLE error 12003:
    "ALTER SNAPSHOT "MV_TEST" COMPILE"
    IMP-00003: ORACLE error 12003 encountered
    ORA-12003: materialized view "DB2"."MV_TEST" does not

    So 00017 was raised first, then 01031, then 00017 and then 12003. Right??

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Wrong.

    Besides, you forgot to mention IMP-00003.

  7. #7
    Join Date
    Jun 2009
    Posts
    6
    oh sorry. i did not realize.
    But what is the meaning of the first error then?

    IMP-00017: following statement failed with ORACLE error 1031:"CREATE SNAPSHOT "MV_TEST" ....


    And are these all errors (from the first one till the last) caused due to insufficient privileges?

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Due to lack of privileges, you weren't able to create a materialized view nor alter/compile it (because it didn't exist as you didn't create it because you didn't have required privileges).

  9. #9
    Join Date
    Jun 2009
    Posts
    6
    I checked in the database and the user has the create materialized view previlege

  10. #10
    Join Date
    Jun 2009
    Posts
    6
    If I give "Create any Materialized Views" and "Create any Tables" privilege to the user2, does it make sure that it will not take the schema name user1 while importing the data for the materialized views. Currently I have these 2 schema's and i do not face any problem in the import of the tables. But i do get the above errors in the materialized views. Why does it work with tables and not MVs?

Posting Permissions

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