CODEWITHSUNDEEP
sqloracle11goracle-text
Andi Heusser
Andi Heusser

Reputation: 301

Can't create Oracle Text index with sync (every...)

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.

The tables is defined as:

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)
)

So when I try to create the text index on the message_data column as:

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');

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).

When the create index is run, it outputs:

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"

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.

Why can't I set the sync parameter to 'every'? What am I doing wrong?

The version of Oracle I'm using is: 

SQL> 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

Upvotes: 1

Views: 6410

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

After digging through Google, that's what I have finally come across:

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.

Taken from here: about Oracle Text

I tried to run your code and got the same error as you did. However, after:

GRANT CREATE JOB TO my_user;

I was able to create the index without any errors.

Upvotes: 3

Related Questions