Forum Stats

  • 3,815,817 Users
  • 2,259,093 Discussions
  • 7,893,255 Comments

Discussions

ORA-29897 when indexing multiple Column s

mpatzwahl
mpatzwahl Member Posts: 279 Bronze Badge
edited Dec 7, 2019 4:17AM in Text

Hi, i´m new to Context and i couldn´t find a solution to my problem:

i want to set the context index on 3 Cols (2 Clob one varchar2) on Oracle xe 18c:

BEGIN

ctx_ddl.drop_preference('muso_multi_idx');

ctx_ddl.create_preference('muso_multi_idx', 'MULTI_COLUMN_DATASTORE');

ctx_ddl.set_attribute('muso_multi_idx', 'COLUMNS', 'tipp_text, tipp_ueberschrift, tipp_text_plsql');

END;

CREATE INDEX msix_tipp_plsql_context

ON muniq.msta_tipp_text(tipp_text)

INDEXTYPE IS CTXSYS.CONTEXT

PARAMETERS ('DATASTORE muso_multi_idx SYNC ( ON COMMIT )');

But i  get the error:

CREATE INDEX muniq.msix_tipp_plsql_context

ON muniq.msta_tipp_text(tipp_text)

INDEXTYPE IS CTXSYS.CONTEXT

PARAMETERS ('DATASTORE muso_multi_idx SYNC ( ON COMMIT )')

Fehlerbericht -

ORA-29879: Mehrere Domainindizes können nicht mit demselben Indextyp auf einer Spaltenliste erstellt werden

29879. 00000 -  "cannot create multiple domain indexes on a column list using same indextype"

*Cause:    An attempt was made to define multiple domain indexes on the same

           column list using identical indextypes.

*Action:   Check to see if a different indextype can be used or if the index

           can be defined on another column list.

Has anybody  a solution for this ?

Thanks

Marco

Best Answers

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Dec 6, 2019 9:10AM Answer ✓

    Looks like an ownership issue. DRG-10700: preference does not exist: XXX

    I see you're creating the index with a schema prefix: muniq.msix_tipp_plsql_context

    Which user did you create the preference as?

    If it was a different user then you'll need to specify the schema on the preference. Either create the preference as :

    ctx_ddl.create_preference('muniq.muso_multi_idx', 'MULTI_COLUMN_DATASTORE');

    Or use the schema that created it in the CREATE INDEX:

    PARAMETERS ('DATASTORE <schema>.muso_multi_idx SYNC ( ON COMMIT )');

    Generally it's much simpler to create everything as the table/index owner, though I appreciate that some customers prefer not to do that for various reasons.

  • mpatzwahl
    mpatzwahl Member Posts: 279 Bronze Badge
    edited Dec 7, 2019 4:17AM Answer ✓

    Roger you are absolutly right, thanks a lot, i did it with another user !!

    Greetings from Munich !

Answers

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Dec 6, 2019 8:30AM

    That means you've already created an Oracle Text index on that same column. You'll need to drop the existing index, or perhaps create the new index on one of the other columns used in your MULTI_COLUMN_DATASTORE.

    You can check what indexes already exist with either


    SELECT index_name FROM user_indexes WHERE table_name = 'MSTA_TIP_TEXT' AND index_type = 'DOMAIN'

    or

    SELECT idx_name FROM ctx_user_indexes WHERE idx_table = 'MSTA_TIP_TEXT' AND idx_text_name = 'TIPP_TEXT'

    The first will give you all DOMAIN indexes on the table, the second will give you only Oracle Text indexes on that specific column.  Both must be run as the index owner - presumably MUNIQ in this case.

  • mpatzwahl
    mpatzwahl Member Posts: 279 Bronze Badge
    edited Dec 6, 2019 8:41AM

    Thanks Roger, for fast reply....

    Yes that was also my first thought, ok i found the problem: SQL * Developer didn´t showed me the index on the Table, but as always, SELECT by myself is always better... :-)

    but now i ´m running in the next problem:

    drop index muniq.MSIX_TIPP_PLSQL_CONTEXT;=> OK

    BEGIN

    ctx_ddl.drop_preference('muso_multi_idx');

    ctx_ddl.create_preference('muso_multi_idx', 'MULTI_COLUMN_DATASTORE');

    ctx_ddl.set_attribute('muso_multi_idx', 'COLUMNS', 'tipp_text, tipp_ueberschrift, tipp_text_plsql');

    END;

    /

    CREATE INDEX muniq.msix_tipp_plsql_context

    ON muniq.msta_tipp_text(tipp_text)

    INDEXTYPE IS CTXSYS.CONTEXT

    PARAMETERS ('DATASTORE muso_multi_idx SYNC ( ON COMMIT )');

    ORA-29855: Fehler bei Ausführung der Routine ODCIINDEXCREATE

    ORA-20000: Oracle Text-Fehler:

    DRG-10700: Voreinstellung ist nicht vorhanden: muso_multi_idx

    ORA-06512: in "CTXSYS.DRUE", Zeile 183

    ORA-06512: in "CTXSYS.TEXTINDEXMETHODS", Zeile 320

    29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Dec 6, 2019 9:10AM Answer ✓

    Looks like an ownership issue. DRG-10700: preference does not exist: XXX

    I see you're creating the index with a schema prefix: muniq.msix_tipp_plsql_context

    Which user did you create the preference as?

    If it was a different user then you'll need to specify the schema on the preference. Either create the preference as :

    ctx_ddl.create_preference('muniq.muso_multi_idx', 'MULTI_COLUMN_DATASTORE');

    Or use the schema that created it in the CREATE INDEX:

    PARAMETERS ('DATASTORE <schema>.muso_multi_idx SYNC ( ON COMMIT )');

    Generally it's much simpler to create everything as the table/index owner, though I appreciate that some customers prefer not to do that for various reasons.

  • mpatzwahl
    mpatzwahl Member Posts: 279 Bronze Badge
    edited Dec 7, 2019 4:17AM Answer ✓

    Roger you are absolutly right, thanks a lot, i did it with another user !!

    Greetings from Munich !

  • kutrovsky
    kutrovsky Member Posts: 23 Blue Ribbon

    Rogers, it would be great if all the preference create functions respected "CURRENT_SCHEMA" - like regular DDL.

    Currently they do not.