Oracle Text ctx_ddl.sync_index with different schema

Question

I am creating a batch job for sync oracle text ctx index.

The situation is as follow:

I have two Oracle account, account A is the schema owner and account B is an Oracle account used in batch job server. The context index is created in account A. I want to use Oracle account B to execute a batch job to sync context index. .

Oracle account A (Schema owner)

Context Index -> myindex

Oracle account B

Batch Job script

begin
  ctx_ddl.sync_index('myindex', '2M');
end;

The above script return myIndex not found.

I would like to ask is there any way to use Oracle account B execute ctx_ddl.sync_index for the context index stored in Oracle account A?

I have tried to use A.myIndex, create allias and etc, it still return not found

Please help:(

Answer 1

The error message is lying - this is a privilege issue, not a name issue. The synchronization should work if you preface the schema name and have the ALTER ANY INDEX privilege.

Run this as user A or any admin account:

grant alter any index to B;

Run this as user B:

begin
  ctx_ddl.sync_index('a.myindex', '2M');
end;
/

Indexes don't have object privileges so you must grant an ANY system privilege. ANY privileges are often powerful and dangerous, but creating indexes can't do too much damage so hopefully your administrators won't complain. If the DBAs won't allow this, you can create a procedure on schema A that does the synchronization and then grant execute on that privilege to schema B.