Oracle – Why CTX_DDL.SYNC_INDEX Procedure Doesn’t Work

data synchronizationindexoracleoracle-11g-r2

I'm following the Oracle documentation to create indexes with Oracle Text. The problem is, when I update my data and want to synchronize my index with the CTX_DDL.SYNC_INDEX procedure (according to the step 4 of the documentation), there is no change.

I don't post my code because it is exactly the same as the documentation.

An idea about the source of this behavior?

Best Answer

To perform this operation, the user who executes the SYNC_INDEX procedure has to be the owner of the index. So, in my case, I had to give the execution rights on the package with the CTXSYS user:

GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;

Now, I can synchronize my index with myuser:

EXEC CTX_DDL.SYNC_INDEX('idx_docs', '2M');
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;

I got the same result as the documentation:

  SCORE(1)         ID TEXT
---------- ---------- -------------------------------------------------
         4          2 <HTML>Paris is a city in France.</HTML>
         4          4 <HTML>Los Angeles is a city in California.</HTML>
         4          5 <HTML>Mexico City is big.</HTML>
Related Question