Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Posts
    29

    Unanswered: very strange issue

    I have two databases ..I create two identical users in each . with just RESOURCE privilege granted .
    I can create a functional index with one user but I cant create it with other user I get error 'insufficient privileges' .. .

    any ideas?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by aliyesami
    I have two databases ..I create two identical users in each . with just RESOURCE privilege granted .
    I can create a functional index with one user but I cant create it with other user I get error 'insufficient privileges' .. .

    any ideas?
    Yes, your databases are not the same, maybe different version?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2003
    Posts
    29

    yes one is 9i and one is 8i

    in 9i it works. . in 8i doesnt .. so which privilige do i need to create functional index in 8i ?

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    For functional indexes on 8i you must have QUERY_REWRITE_ENABLED=TRUE and QUERY_REWRITE_INTEGRITY=TRUSTED in your init.ora, or have sufficient privileges to set them with an ALTER SESSION statement.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    Jun 2003
    Posts
    29

    doesnt work still .

    I did as you suggested ..see below :

    SQL> alter session set QUERY_REWRITE_ENABLED=TRUE;

    Session altered.

    SQL> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED ;

    Session altered.

    SQL> CREATE INDEX ABIDX ON AB(UPPER(NAME));
    CREATE INDEX ABIDX ON AB(UPPER(NAME))
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    So what happens if you issue

    CREATE INDEX ABIDX ON AB (NAME)

    This will atleast indicate if it is the Function Based Index which is the problem, or just indexes in general.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Jun 2003
    Posts
    29

    ok its working now

    problem was alterting the session did not work but granting the QUERY REWRITE priv to the user did.
    thanks for your help.
    Sami

  8. #8
    Join Date
    Jun 2003
    Posts
    29

    ok its working now

    problem was alterting the session did not work but granting the QUERY REWRITE priv to the user did.
    thanks for your help.
    Sami

Posting Permissions

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