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

Thread: Refresh MV on demand

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    Refresh MV on demand

    Hi,

    When I am trying to refresh a MV on demand, I am getting the below error.

    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
    ORA-06512: at line 1
    Here is the back ground.

    master tables are owned by schema A

    A view "V" is created in schema B using the tables in schema A

    MV is owned by schema B

    CREATE MATERIALIZED VIEW B.MV
    REFRESH COMPLETE ON DEMAND
    AS
    SELECT *
    FROM B.V
    Now, I am using schema C to refresh the MV.

    Schema B has select privilege on tables used in view with grant option. Schema C has alter any materialized view privilege, privilege on B.V.

    When I execute only view it is fine. When I try to refresh MV I am getting the above error.

    I am clueless at this point. Any help is highly appreciated.

    Oracle - 10G R2

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    grant SELECT on table (owned by A ) to 'C' and try again.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks for reply Thomas.

    I have already tried that with no luck.

    I have recalled that, after the MV is created couple of master tables are moved from one TS to other TS. I am not sure if that creates any problem. So, planned to drop / recreate the MV tomorrow then try refresh again.

    I will update the forum after testing.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    CREATE MATERIALIZED VIEW B.MV
    REFRESH COMPLETE ON DEMAND
    AS
    SELECT *
    FROM B.V
    as the tables are owned by schema 'A' why it is "... FROM B.V", it should be 'A.V' ?
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Thomas,

    View is already created in schema B.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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