title: Can't create Oracle Text index with sync (every...)
body: <p>I'm trying to create an Oracle text index on a CLOB column in a table but whenever I try to specify the sync parameter as 'SYNC (EVERY "(sysdate+30/24/60/60)")' it fails with some errors about having insufficient privileges.</p>
  
  <p>The tables is defined as:</p>
  
  <pre><code>CREATE TABLE MESSAGE 
  (
      MESSAGE_ID      NUMBER(19,0), 
      FROM            NUMBER(19,0), 
      TO              NUMBER(19,0), 
      RECEIVED_AT     TIMESTAMP (6), 
      MESSAGE_TYPE_ID NUMBER(19,0), 
      MESSAGE_DATA    CLOB, 
      IS_SEARCHABLE   CHAR(1 BYTE)
  )
  </code></pre>
  
  <p>So when I try to create the text index on the message_data column as:</p>
  
  <pre><code>BEGIN
      CTX_DDL.CREATE_PREFERENCE('msg_data_lexer', 'BASIC_LEXER');
      CTX_DDL.SET_ATTRIBUTE('msg_data_lexer', 'skipjoins', '_-');
  
      CTX_DDL.CREATE_PREFERENCE('msg_data_wordlist', 'BASIC_WORDLIST');
      CTX_DDL.SET_ATTRIBUTE('msg_data_wordlist', 'SUBSTRING_INDEX', 'YES');
      CTX_DDL.SET_ATTRIBUTE('msg_data_wordlist', 'PREFIX_INDEX', 'TRUE');
      CTX_DDL.SET_ATTRIBUTE('msg_data_wordlist', 'PREFIX_MIN_LENGTH', '3');
      CTX_DDL.SET_ATTRIBUTE('msg_data_wordlist', 'PREFIX_MAX_LENGTH', '6');
  END;
  /
  CREATE INDEX message_msg_data_txt_idx on message(message_data)
      INDEXTYPE IS CTXSYS.CONTEXT
      FILTER BY message_type_id, from_entity, to_entity, is_searchable
      PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE
                   FILTER CTXSYS.NULL_FILTER
                   LEXER msg_data_lexer
                   SYNC (EVERY "SYSDATE + (30/24/60/60)")
                   WORDLIST msg_data_wordlist');
  </code></pre>
  
  <p>So basically I want to create an index that syncs every 30 seconds because this is a table where lots of new messages will be added all the time (potentially hundreds to thousands of messages per second). </p>
  
  <p>When the create index is run, it outputs:</p>
  
  <pre><code>Error at Command Line:1 Column:13
  Error report:
  SQL 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"
  </code></pre>
  
  <p>The user has all the necessary privileges as far as I know. If I either remove the 'sync' parameter or change it to 'SYNC (ON COMMIT)', then the index gets created properly. </p>
  
  <p><strong>Why can't I set the sync parameter to 'every'? What am I doing wrong?</strong></p>
  
  <p>The version of Oracle I'm using is: </p>
  
  <pre><code>SQL&gt; select * from v$version;
  
  BANNER
  --------------------------------------------------------------------------------
  Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
  PL/SQL Release 11.2.0.1.0 - Production
  CORE    11.2.0.1.0      Production
  TNS for Linux: Version 11.2.0.1.0 - Production
  NLSRTL Version 11.2.0.1.0 - Production
  </code></pre>