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

Thread: dblink materialized view

  1. #1
    Join Date
    Nov 2000
    Posts
    175

    dblink materialized view

    Hi,

    Having trouble with privleges while creating the MV that uses a dblink. I have went back and checked all the privs that were given, seems it should work.

    db1
    grant select on table_one to role_name.
    granted role_name to user1

    db2
    created public dblink linking at user1
    granted role_name to user2
    granted create materialized view to user2
    granted query rewrite to user2

    as user2 - This works:
    desc owner.table_one@dblink_name

    as user2 - This doesn't work: insufficent privs,

    create materialized view results_mv
    build immediate
    refresh on demand
    enable query rewrite
    as select * from owner.table_one@dblink_name;

    Error Message:
    select * from owner.table_one@dblink_name
    *
    ERROR at line 6:
    ORA-01031: insufficient privileges

    Any ideas what I'm missing here.
    Thanks,
    Kathy

  2. #2
    Join Date
    Nov 2000
    Posts
    175
    GOT it!

    I needed to grant global query rewrite, since using remote db.

    Thanks,
    Kathy

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