In this post, we will examine the root cause behind this error, how to identify the metadata discrepancy using a side-by-side environment comparison, and the official Oracle-supported de-installation and re-installation procedure.
Executing a standard domain index creation statement fails with the following stack:
CREATE INDEX app_owner.doc_xml_idx ON app_owner.doc_catalog(xml_data)
INDEXTYPE IS CTXSYS.PATH_SECTION_INDEX
PARAMETERS ('SECTION GROUP CTXSYS.XQUERY_SEC_GROUP');
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-12255: invalid data type for SDATA section XQUERY_SEC_GROUPTS*
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320
Technical Root Cause Analysis
The underlying issue is tracked under Unpublished Bug 39088026 (CREATING TEXT INDEX DRG-12255: INVALID DATA TYPE FOR SDATA SECTION XQUERY_SEC_GROUPTS*).
During database upgrades (e.g., from Oracle 12c to 19c), post-upgrade dictionary scripts (dr0upgrade.sql / ctxpatch.sql) can leave system section group metadata in CTXSYS.CTX_SECTION_GROUP_SECTIONS in an invalid state.
Specifically, the dictionary metric SEC_DATATYPE expects an internal datatype code of 187. In affected instances, this value is corrupted or improperly initialized during upgrade, and the internal section names fail to inherit the fully qualified CTXSYS. package namespace prefix.
To inspect the metadata and SEC_DATATYPE values for section groups in your database, run the following query as SYSDBA:
SELECT sec_owner,
sec_section_group,
sec_name,
sec_type,
sec_datatype
FROM ctxsys.ctx_section_group_sections
WHERE sec_section_group = 'XQUERY_SEC_GROUP';
Comparing a healthy instance against an affected instance highlights the missing schema prefixes in the SEC_NAME column:
Unaffected Environment:
SEC_OWNER SEC_SECTION_GROUP SEC_TYPE SEC_NAME SEC_TAG SEC_DATA
--------- ------------------------- ------------ ------------------------------ -------------------- ------
CTXSYS XQUERY_SEC_GROUP SDATA CTXSYS.XQUERY_SEC_GROUPDB* db*
CTXSYS XQUERY_SEC_GROUP SDATA CTXSYS.XQUERY_SEC_GROUPNUM* num* NUMBER
CTXSYS XQUERY_SEC_GROUP SDATA CTXSYS.XQUERY_SEC_GROUPTS* ts*
Affected Environment:
SEC_OWNER SEC_SECTION_GROUP SEC_TYPE SEC_NAME SEC_TAG SEC_DATA
----------------- ------------------------- ------------ -------------------- -------------------- --------
CTXSYS XQUERY_SEC_GROUP SDATA XQUERY_SEC_GROUPDB* db*
CTXSYS XQUERY_SEC_GROUP SDATA XQUERY_SEC_GROUPNUM* num* NUMBER
CTXSYS XQUERY_SEC_GROUP SDATA XQUERY_SEC_GROUPTS* ts*
Notice how the affected environment lacks the CTXSYS. prefix in the SEC_NAME column, which triggers the DRG-12255 invalid data type exception when parsed.
Pre-Requisite: Verifying Oracle Text Usage
Before attempting any component-level changes, DBAs must verify whether Oracle Text domain indexes or custom preferences are actively used by application schemas.
Run the following queries as SYSDBA:
-- 1. Check for Active Oracle Text Indexes
SELECT owner, index_name, table_owner, table_name, status
FROM dba_indexes
WHERE ityp_owner = 'CTXSYS'
OR ityp_name IN ('CONTEXT', 'CTXCAT', 'CTXRULE', 'PATH_SECTION_INDEX');
-- 2. Check for Custom User Preferences
SELECT pre_owner, pre_name, pre_class, pre_object
FROM ctxsys.ctx_preferences
WHERE pre_owner NOT IN ('SYS', 'SYSTEM', 'CTXSYS');
-- 3. Summary Check One-Liner
SELECT
(SELECT COUNT(*) FROM dba_indexes WHERE ityp_owner = 'CTXSYS') AS active_text_indexes,
(SELECT COUNT(*) FROM ctxsys.ctx_preferences WHERE pre_owner NOT IN ('SYS', 'SYSTEM', 'CTXSYS')) AS custom_preferences
FROM dual;
If both counts return 0, no active text indexes or custom preferences exist on the instance.
Solution or Remediation Steps
Because Oracle Development has no ETA on when an official patch or fix will be released, modifying internal CTXSYS metadata tables directly via UPDATE or ALTER statements is strictly unsupported.
If no existing Oracle Text indexes exist on the database instance, the safest and only vendor-supported path forward is to completely de-install and reinstall the Oracle Text
(CTXSYS) component.
Step 1: De-install Oracle Text Component
Connect as SYSDBA and execute the de-installation script (catnoctx.sql):
Pre-Requisite: Verifying Oracle Text Usage
Before attempting any component-level changes, DBAs must verify whether Oracle Text domain indexes or custom preferences are actively used by application schemas.
Run the following queries as SYSDBA:
-- 1. Check for Active Oracle Text Indexes
SELECT owner, index_name, table_owner, table_name, status
FROM dba_indexes
WHERE ityp_owner = 'CTXSYS'
OR ityp_name IN ('CONTEXT', 'CTXCAT', 'CTXRULE', 'PATH_SECTION_INDEX');
-- 2. Check for Custom User Preferences
SELECT pre_owner, pre_name, pre_class, pre_object
FROM ctxsys.ctx_preferences
WHERE pre_owner NOT IN ('SYS', 'SYSTEM', 'CTXSYS');
-- 3. Summary Check One-Liner
SELECT
(SELECT COUNT(*) FROM dba_indexes WHERE ityp_owner = 'CTXSYS') AS active_text_indexes,
(SELECT COUNT(*) FROM ctxsys.ctx_preferences WHERE pre_owner NOT IN ('SYS', 'SYSTEM', 'CTXSYS')) AS custom_preferences
FROM dual;
If both counts return 0, no active text indexes or custom preferences exist on the instance.
Solution or Remediation Steps
Because Oracle Development has no ETA on when an official patch or fix will be released, modifying internal CTXSYS metadata tables directly via UPDATE or ALTER statements is strictly unsupported.
If no existing Oracle Text indexes exist on the database instance, the safest and only vendor-supported path forward is to completely de-install and reinstall the Oracle Text
(CTXSYS) component.
Step 1: De-install Oracle Text Component
Connect as SYSDBA and execute the de-installation script (catnoctx.sql):
sqlplus / as sysdba
SPOOL text_deinstall.log
-- Remove Oracle Text schema and dictionary objects
@?/ctx/admin/catnoctx.sql
-- Drop remaining validation procedure
DROP PROCEDURE sys.validate_context;
SPOOL OFF
Step 2: Reinstall Oracle Text Component
Re-create the CTXSYS schema and re-initialize system preferences:
sqlplus / as sysdba
SPOOL text_install.log
-- Install Oracle Text (Default tablespace: SYSAUX, Temp: TEMP)
@?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP LOCK
-- Change CTXSYS password and unlock account
ALTER USER CTXSYS IDENTIFIED BY "<password>" ACCOUNT UNLOCK;
-- Connect as CTXSYS to load default preferences and section groups
CONNECT CTXSYS/<password>
@?/ctx/admin/defaults/drdefus.sql
-- Connect back as SYSDBA to compile invalid objects and validate
CONNECT / as sysdba
@?/rdbms/admin/utlrp.sql
SPOOL OFF
Step 3: Verification
Verify that the CTXSYS component is installed and in VALID status across the database registry:
SELECT comp_id, comp_name, version, status
FROM dba_registry
WHERE comp_id = 'CONTEXT';
Once reinstallation is complete, re-run the CREATE INDEX DDL statement. The metadata entries will correctly include the proper namespace prefixes and datatype values, allowing the text domain index build to complete cleanly.
-- Remove Oracle Text schema and dictionary objects
@?/ctx/admin/catnoctx.sql
-- Drop remaining validation procedure
DROP PROCEDURE sys.validate_context;
SPOOL OFF
Step 2: Reinstall Oracle Text Component
Re-create the CTXSYS schema and re-initialize system preferences:
sqlplus / as sysdba
SPOOL text_install.log
-- Install Oracle Text (Default tablespace: SYSAUX, Temp: TEMP)
@?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP LOCK
-- Change CTXSYS password and unlock account
ALTER USER CTXSYS IDENTIFIED BY "<password>" ACCOUNT UNLOCK;
-- Connect as CTXSYS to load default preferences and section groups
CONNECT CTXSYS/<password>
@?/ctx/admin/defaults/drdefus.sql
-- Connect back as SYSDBA to compile invalid objects and validate
CONNECT / as sysdba
@?/rdbms/admin/utlrp.sql
SPOOL OFF
Step 3: Verification
Verify that the CTXSYS component is installed and in VALID status across the database registry:
SELECT comp_id, comp_name, version, status
FROM dba_registry
WHERE comp_id = 'CONTEXT';
Once reinstallation is complete, re-run the CREATE INDEX DDL statement. The metadata entries will correctly include the proper namespace prefixes and datatype values, allowing the text domain index build to complete cleanly.
Thanks & Regards,
https://oracleracexpert.com
