DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: problem importing materialized view

  1. #1
    Join Date
    Nov 2008
    Posts
    9

    problem importing materialized view

    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
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I remember bumping into that same issue several Oracle versions ago -not sure if it is supposed to work now.

    As far as I remember Oracle didn't allowed to import a Materialized View into a different schema -this was enforced by Oracle during export by fully qualifying the object creation which would conflict with to_schema option during import.

    Being a materialized view I would manually create it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by needy View Post
    IMP-00003: ORACLE error 1031 encountered
    ORA-01031: insufficient privileges
    IMP-00017: following statement failed with ORACLE error 12003:
    I have not tried importing an MV into another schema but what I want to emphasize is that error list signifies the lack of privilege for creating materialized view. Did u give 'create materialized view' or 'create any materialized view' privilege to user2?
    lucky

  4. #4
    Join Date
    Nov 2008
    Posts
    9
    what about the first error?

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

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Have u given privilege 'create any materialized view' or 'create materialized view' to user2?

    As far as I know, snapshot syntax is also like MV. So there may be any relation b/w them..But I dont know much abt snapshot
    lucky

  6. #6
    Join Date
    Nov 2008
    Posts
    9
    Yes the user has the "Create Materialized View Privilege"

  7. #7
    Join Date
    Nov 2008
    Posts
    9
    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?

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by needy View Post
    Why does it work with tables and not MVs?
    I told you why 14 days ago today; check Metalink Doc ID: 294625.1
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Nov 2008
    Posts
    9
    Yes I know you gave me the answer. But now I want to know if i grant the second user the privileges to "Create any Materialized Views" and "Create any Tables" and then create the materialized view manually and then i import the database from one schema to the other. Does it work? I have to explain this to the DBA before he can grant the desired permissions to the user. (you see he is a little more difficult :P)

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Tell your DBA to check Metalink Doc ID: 294625.1 then ask him to provide/suggest a suitable process to solve business requirements.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width