Oracle Error :: DRG-10758

Index owner does not have the privilege to use file or URL datastore Cause.

Error details:

When you tried to create an index, if the index owner does not have the privilege to use file or URL datastore, it will occur the following error stack:

IMP-00017: following statement failed with ORACLE error 29855:
"CREATE INDEX "FILES_CONTENT_IDX" ON "FILES" ("CONTENT_URL" )  INDEXTYPE IS "
""CTXSYS"."CONTEXT" PARAMETERS ('DATASTORE CTXSYS.FILES_CONTENT_PREF FILTER "
"CTXSYS.INSO_FILTER LEXER CTXSYS.ATTACHMENT_LEXER STORAGE CTXSYS.FILES_CONTE"
"NT_STORAGE ')"
IMP-00003: ORACLE error 29855 encountered
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10758: index owner does not have the privilege to use file or URL datastore

Cause:

Index owner does not have the role defined in FILE_ACCESS_ROLE.

Solution/Action:

Grant index owner the appropriate role

How to do with Solution/Action in detail:

To determine if the ability to use an Oracle Text URL datastore is already granted to a database role:

  1. 1.Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
    On Windows:

    SYSTEM_DRIVE:\ sqlplus /nolog
    SQL> CONNECT SYS as SYSDBA
    Enter password: SYS_password
    *

    On UNIX and Linux:

    $ sqlplus /nolog
    SQL> CONNECT SYS as SYSDBA
    Enter password: SYS_password
  2. Run the following command:
    SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = ‘FILE_ACCESS_ROLE’;
    This returns either NULL or the database role which is granted the ability to use an Oracle Text URL datastore.
  3. If no value is returned by step 2, then create a new database role as shown in the following example:
    CREATE ROLE APEX_URL_DATASTORE_ROLE;
  4. Grant this role to the database user APEX_040000 with the following statement:
    GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;
    If step 2 returned a value, use this database role name instead of the example APEX_URL_DATASTORE_ROLE.
  5. Lastly, if step 2 did not return a value, then use the Oracle Text API to grant permission to the newly created database role with the following statement:
    EXEC ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘APEX_URL_DATASTORE_ROLE’);

If you executed above steps and still got this error,You probably need to grant “admin” and “default” privileges on Apex_Url_Datastore_Role for this particular user APEX_040000.