[SOLVED] Oracle context index: doesn't work over dblink

Oracle context index: doesn't work over dblink

There are two db's, table test_table on db1 with context index by field a. Query:

select *
  from test_table t
 where contains(t.a, 'str') > 0

It works fine on db1. But when I try execute same query over dblink from other database:

select *
  from test_table@db1 t
 where contains(t.a, 'str') > 0

I get this error:

ora-20000: Oracle Text Error: DRG-10599: column is not indexed

Solution

You have to add dblink to function. https://docs.oracle.com/cd/E11882_01/text.112/e24436/csql.htm#CCREF0104

The CONTAINS operator also supports database links. You can identify a remote table or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table or materialized view. (Querying of remote views is not supported.)

select *
  from test_table@db1 t
 where contains(t.a, 'str')@db1 > 0.