Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    106

    Unanswered: Insufficient privileges error for functional index creation

    We are using oracle 9.2

    we have a table say
    create table test(col varchar2(30))

    now we we create an index
    create index test_index on test(upper(col))

    it gives insufficient provileges error.
    We have given the select_Catalogue_role to the user say X under which this commands are run.

    But if I login as sys user i am able to create the index
    create test_index on X.test(upper(col))

    any idea why it is not working for user X?
    Sachi

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Prerequisites

    To create an index in your own schema, one of the following conditions must be true:

    The table or cluster to be indexed must be in your own schema.

    You must have INDEX privilege on the table to be indexed.

    You must have CREATE ANY INDEX system privilege.

    To create an index in another schema, you must have CREATE ANY INDEX system privilege. Also, the owner of the schema to contain the index must have either space quota on the tablespaces to contain the index or index partitions, or UNLIMITED TABLESPACE system privilege.

    To create a domain index in your own schema, you must also have EXECUTE privilege on the indextype. If you are creating a domain index in another user's schema, the index owner also must have EXECUTE privilege on the indextype and its underlying implementation type. Before creating a domain index, you should first define the indextype.

    To create a function-based index in your own schema on your own table, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. The table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index
    Find out which privilege you're missing ...

Posting Permissions

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