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

Thread: Insufficient Priveleges to Create Materialized Views

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    I am trying to create a materialized view and it is giving me insufficient privileges.

    SQL> /
    create materialized view testview
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    I have assigned the following roles to the user
    Resource, Connect and DBA all of them with admin option. Also I have assigned the create any view to the user and it still does not seem to work.

    Any Ideas Why????

    Thanks
    Anurag

  2. #2
    Join Date
    Sep 2000
    Posts
    362

    Exclamation

    Just an additional note,

    I am using Oracle 8.1.7 Enterprise edition and I dont see create any materialzed view system privilege in Enterprise manager which I need to grant according to the documentation.

    Thanks
    Anurag

  3. #3
    Join Date
    Sep 2000
    Posts
    362
    I was able to make it work by granting query rewrite and global query rewrite system priveleges to the user.

    But the user had DBA rights and shouldnt that mean that he has all the system priveleges assigned by default.

    Thanks
    Anurag

  4. #4
    Join Date
    Mar 2001
    Posts
    188
    Hi,
    this question have i also placed here and that is to say 20 questions before your and the answer is also there.
    What happends:
    You must have the rights to create a snapsshot.

    Now a question to you or provocation:

    I've got the following situation: a star schema with
    one fact table and a couple of dimension tables.

    I'm filling in new data into the fact table and deleting
    old data periodically from the fact table (that is, I'm
    implementing some kind of ring buffer).

    Also, there are MVs defined on the star schema.
    The problem is to retain aggregated data in the MVs,
    even after the corresponding detail data in the fact table
    has been deleted.

    The MV should aquire new data arriving in the fact table,
    but should NOT "synchronize" with the deleted data.

    Can this be achieved?

    Any help would be greatly appreciated.

    Greets,
    Thomas Schmidt

    [Edited by tomate on 06-01-2001 at 01:36 AM]

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