Oracle Text Index with SYNC (EVERY …) Parameter

Just a quick tip for today. Oracle allows us to create special kind of indexes on columns containing long texts. One of them is the CONTEXT type. You don’t need any particular privileges to create an index of this type (other than being able to create indexes in you schema). At least, that’s what the documentation says.

There was a question about creating a CONTEXT index on StackOverflow:

Can’t create oracle text index with sync every

where the user was getting the following „insufficient privileges” error:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexCreate
ORA-27486: insufficient privileges
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"

As it turned out, the problem was the SYNC (EVERY ) parameter. Before I found the solution, I had dug through Google for quite a long time. I’ve checked a few Oracle’s documentation pages, but none was of any help. Finally, I’ve come across yet another one, but this time, there was the following piece of information that solved the riddle:

You can find the source here:

Documentation of Oracle Text SQL Statements and Operators

So, the trick was to grant the CREATE JOB privilege to the user creating the index. Below you’ll find an example which throws the error and then, after the privilege is granted, creates the index successfully.

-- as SYS
CREATE USER ctx_ix_test
  IDENTIFIED BY ctx_ix_test
  DEFAULT TABLESPACE users
  QUOTA 1M ON USERS
;

> user CTX_IX_TEST created.

GRANT CREATE SESSION TO ctx_ix_test;
GRANT CREATE TABLE TO ctx_ix_test;

> GRANT succeeded.
> GRANT succeeded.

-- as CTX_IX_TEST

CREATE TABLE my_test_table (message_content CLOB);

> table MY_TEST_TABLE created.

CREATE INDEX message_content_ctx_ix ON my_test_table(message_content)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('SYNC (EVERY "SYSDATE + 15/24")');

Error report:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexCreate
ORA-27486: insufficient privileges
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

-- as SYS again - let's grant the CREATE JOB privilege
GRANT CREATE JOB TO ctx_ix_test;

-- as CTX_IX_TEST
DROP INDEX message_content_ctx_ix;

> index MESSAGE_CONTENT_CTX_IX dropped.

CREATE INDEX message_content_ctx_ix ON my_test_table(message_content)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('SYNC (EVERY "SYSDATE + 15/24")');

> index MESSAGE_CONTENT_CTX_IX created.

Further Reading & Useful Links

Below you’ll find links to some useful resources on the Internet about topics covered in this article:

• Documentation of Oracle Text SQL Statements and Operators

I hope you enjoyed my article. If you have found any errors in it (even typos), you think that I haven’t explained anything clearly enough or you have an idea how I could make the article better – please, do not hesitate to contact me, or leave a comment.