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:
EVERY interval-string
– automatically synchronize the index at a regular interval specified by the value of interval-string
. interval-string
takes the same syntax as that for scheduler jobs. Automatic synchronization using EVERY requires that the index creator have CREATE JOB privileges.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:
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.