WCC OracleTextSearch: DB-path SortSpec Errors and OTS Rebuild — A Case Study

Situation Overview

In a test environment using Oracle Text Search (OTS), we started seeing timeouts and sort errors right after a DB copy. When OTS was disabled and DB-path search was used instead, certain queries failed to return results because the sort directive (SortSpec) was rejected. Interestingly, with the same conditions the administrator account (e.g., weblogic) worked fine while general users failed. This called for deeper analysis.

Symptoms & Logs (with inline notes)

1) SortSpec rejected under DB-path

Caused By: intradoc.data.DataException: !csSearchInvalidOrderByClause,dDocTitle ASC
# Recorded sort validation error; this alone does not establish an ORDER BY requirement.

Caused By: intradoc.data.DataException: !csSearchInvalidOrderByClause,xPublishDate DESC\, sddDocTitle ASC
# Recorded value includes a backslash and field names; inspect the actual parser, fields and custom filters.

2) With OTS enabled: timeouts and wildcard expansion threshold

ORA-01013: user requested cancel of current operation
# Cancellation was reported; establish the actual caller or timeout path separately.

DRG-51030: wildcard query expansion resulted in too many terms
# Expanded wildcard terms exceeded the limit; this is not a result-row limit.

Diagnosis

The case describes separate DB-path ordering and OTS failures. A csSearchInvalidOrderByClause error does not establish that every SortSpec requires a SQL ORDER BY clause. The WCC 12.2.1.4 GET_SEARCH_RESULTS reference documents a sequence of sort fields and directions; check the actual engine, version, service and custom filters. For OTS, distinguish DRG-51030 wildcard expansion overflow from ORA-01013 cancellation. Attributing either error to the DB copy or user-specific security conditions requires logs and reproduction evidence.

Action ①: Fix SortSpec under DB-path

The left tab preserves an excerpt with an omitted QueryText; it is not an executable file. The right tab is a separate WCC 12.2.1.4 format example using SearchEngineName=database, a SQL predicate and a field/direction sequence in SortSpec. It is neither OTS query syntax nor proof of the historical fix. Verify installed version, supported fields, custom filters and user permissions in an approved test environment.

@Properties LocalData
IdcService=GET_SEARCH_RESULTS
UserTimeZone=UTC
QueryText=... (omitted)
SortSpec=dDocTitle ASC
ResultCount=10
StartRow=1
@end

@Properties LocalData
IdcService=GET_SEARCH_RESULTS
SearchEngineName=database
UserTimeZone=UTC
QueryText=dDocTitle like '%test%'
SortSpec=dDocTitle ASC dInDate DESC
ResultCount=10
StartRow=1
@end

Action ②: Reconfigure OTS after DB copy

A DB copy alone does not require a full collection rebuild or changes to both indexes. Compare the active WCC collection, DB index state and errors before diagnosing a mismatch. The steps and object names below are from the original article, not a universal execution sequence. OracleTextSearch uses Oracle Text in Oracle Database. Check the supported WCC/DB combination, licensing and the distinction between metadata and full-text search first.

1) Assess Collection Rebuild

The WCC 12.2.1.4 guide describes Administration → Admin Applets → Repository Manager, then Start in the Collection Rebuild Cycle on the Indexer tab. The new collection replaces the old one after a successful rebuild. Follow the version-specific restart/rebuild procedure when changing engines; do not repeat it merely because a DB copy occurred. Plan load, storage, interruption and recovery conditions. Full-text coverage depends on format mapping and text extraction; verify language tokenization against lexer settings and representative searches.

2) Apply WORDLIST attributes (renamed)

DOC_WORDLIST is a sample name. Confirm preference ownership, existence and association with the active index first. WILDCARD_MAXTERMS limits expanded search terms, not result rows. The 25,000–50,000 range is not a recommended starting point. Check the installed DB version and compare narrower queries with the memory/latency cost of any limit change. Changing a preference alone does not prove that an existing index has adopted it. The example mutates an existing preference and requires verification of its owner and necessary privileges; it is not a recommendation to connect as SYS or execute it.

BEGIN
  CTX_DDL.SET_ATTRIBUTE('DOC_WORDLIST', 'WILDCARD_MAXTERMS', '25000'); 
  -- Existing-preference mutation example; 25000 is not a recommendation. Check version and workload.
END;
/

3) Review index-change prerequisites (sample names)

IDX_DOCTEXT_A, IDX_DOCTEXT_B and APP_SCHEMA are anonymized examples. The two ALTER INDEX statements illustrate separate targets, not a batch procedure. Oracle Text 19c supports the REBUILD PARAMETERS('REPLACE WORDLIST ...') form, subject to index type, partitioning, ownership, preference and an established need for change. ONLINE supports continuing DML in Enterprise Edition; it does not guarantee continued queries. Some attribute changes, such as a wildcard limit, can use metadata replacement instead of a rebuild, but consistency and locking require review; no alternative mutation command is added here.

-- A and B are separate examples; select only an approved, verified active index.
-- In 19c ONLINE supports continuing DML, not continued querying (Enterprise Edition).
ALTER INDEX APP_SCHEMA.IDX_DOCTEXT_A 
  REBUILD ONLINE PARAMETERS('REPLACE WORDLIST DOC_WORDLIST');

ALTER INDEX APP_SCHEMA.IDX_DOCTEXT_B 
  REBUILD ONLINE PARAMETERS('REPLACE WORDLIST DOC_WORDLIST');

4) Verify application

Oracle Text 19c provides both a CLOB-returning CTX_REPORT.DESCRIBE_INDEX function and a report-parameter procedure. The SQL function form below is supported, but use an unqualified name such as IDX_DOCTEXT_A while connected as the index owner. Existence and active status of A/B remain separate checks. Report output does not substitute for search-result, permission or performance verification.

SELECT CTX_REPORT.DESCRIBE_INDEX('IDX_DOCTEXT_A') FROM dual;
SELECT CTX_REPORT.DESCRIBE_INDEX('IDX_DOCTEXT_B') FROM dual;
-- Connect as the index owner; do not prefix the index name with a schema. A report is not search/performance verification.

Action ③: Admin is fine, general users fail or are slow

When administrator and general-user results differ, compare final queries, permission scope, engine and error timing first. Security predicates can affect execution plans, but that alone does not prove increased wildcard expansion or a specific root cause. The following items are diagnostic candidates, not a mandatory tuning sequence.

1. Check SDATA applicability
   WCC Optimized Fields use Oracle Text SDATA. dSecurityGroup is documented as a default optimized field; do not assume the same for dDocAccount. Check field type, limits and rebuild requirements before testing. Security-predicate performance is not guaranteed to improve.
2. Inspect wildcards in the actual query
   Distinguish UI input from the Oracle Text expression. Oracle Text uses % and _ wildcards; do not treat the UI pattern *term* as literal DB syntax. Compare query narrowing or a version-appropriate limit change only after confirming expansion overflow.
3. Identify cancellation and latency
   ORA-01013 reports cancellation. Correlate the actual cancellation path, including user cancellation or client timeout, with DB waits instead of simply increasing limits.

Validation & Production Rollout

We first corrected SortSpec on the DB-path route and confirmed the service completed successfully. We then applied the same ordering format to the WCC UI configuration and validated end-to-end search there as well, followed by rolling the configuration out to production. This is the original account of the historical rollout, not a result newly verified by this review.

The original article reports test-environment validation of the OTS rebuild. This documentation review has not verified the historical DB version, execution records or current index state. SDATA and wildcard tuning remain candidates for further testing. Compare correctness, latency and errors using actual user permissions as well as administrator access before claiming an improvement.

Checklist

• Compare sorting errors with the actual request and the path-, service- and version-specific contract.
• Assess collection/index changes only after confirming a mismatch following the DB copy.
• Check the expanded-term limit alongside query shape, DB version and memory impact.
• Compare final queries and permission predicates before testing diagnostic candidates such as SDATA.
• Before rollout, verify results, latency, errors and recovery using actual user permissions in the UI.

Appendix: Useful Snippets

-- Existing-preference example: 25000 is not a recommended value.
BEGIN
  CTX_DDL.SET_ATTRIBUTE('DOC_WORDLIST', 'WILDCARD_MAXTERMS', '25000');
END;
/


-- A and B are separate examples; select only an approved, verified active index.
-- In 19c ONLINE supports continuing DML, not continued querying (Enterprise Edition).
ALTER INDEX APP_SCHEMA.IDX_DOCTEXT_A 
  REBUILD ONLINE PARAMETERS('REPLACE WORDLIST DOC_WORDLIST');

ALTER INDEX APP_SCHEMA.IDX_DOCTEXT_B 
  REBUILD ONLINE PARAMETERS('REPLACE WORDLIST DOC_WORDLIST');

-- As the owner, request a report using an unqualified index name.
SELECT CTX_REPORT.DESCRIBE_INDEX('IDX_DOCTEXT_A') FROM dual;
SELECT CTX_REPORT.DESCRIBE_INDEX('IDX_DOCTEXT_B') FROM dual;

@Properties LocalData
IdcService=GET_SEARCH_RESULTS
SearchEngineName=database
UserTimeZone=UTC
QueryText=dDocTitle like '%test%'
SortSpec=dDocTitle ASC dInDate DESC
ResultCount=10
StartRow=1
@end