Hi, when trying to create a local text index on an interval-partitioned table in Oracle DB v19c, we are getting an error.
SQL:
CREATE INDEX TEXT_INDEX ON BOTS_CONVERSATION_UTTERANCE(UTTERANCE) INDEXTYPE IS CTXSYS.CONTEXT LOCAL
Error report -
ORA-29940: User-managed domain indexes are not allowed on interval-partitioned tables.
29940. 00000 - "User-managed domain indexes are not allowed on interval-partitioned tables."
*Cause: An attempt was made to create a local user-managed domain index on
an interval-partitioned table.
*Action: Convert the underlying index type to system-managed.
How can the index type be converted to system-managed as suggested in the error report? What are the steps involved to do so?
Oracle Text only supports range partitioning; interval partitioning is unsupported.
To make this index, either you need to define it as non-partitioned:
CREATE INDEX TEXT_INDEX ON BOTS_CONVERSATION_UTTERANCE(UTTERANCE) INDEXTYPE IS CTXSYS.CONTEXT
Or remove interval partitioning from the table:
ALTER TABLE BOTS_CONVERSATION_UTTERANCE
SET INTERVAL ();
HT to Roger Ford, Oracle Text PM