Creating or dropping a Text index fails with errors: ORA-04063: package body "CTXSYS.DRIXMD" has errors

(Doc ID 1375052.1)

Last updated on NOVEMBER 01, 2017

Applies to:

Oracle Text - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 11-Apr-2013***

Symptoms

Creating a Text index fails with errors:

SQL> CREATE INDEX quick_text
  2 on quick ( text )
  3 INDEXTYPE IS CTXSYS.CONTEXT;
CREATE INDEX quick_text
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-04063: package body "CTXSYS.DRIXMD" has errors
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIXMD"
ORA-06512: at "CTXSYS.DRUE", line 145
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
ORA-04063: package body "CTXSYS.DRIXMD" has errors
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIXMD"


Dropping a Text index also fails with errors:

SQL> drop index text_idx;
drop index text_idx
*
ERROR at line 1:
ORA-29856: error occurred in the execution of ODCIINDEXDROP routine
ORA-04063: package body "CTXSYS.DRIXMD" has errors
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIXMD"
ORA-06512: at "CTXSYS.DRUE", line 145
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 728
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDDL", line 1067
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-04063: package body "CTXSYS.DRIXMD" has errors
ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIXMD"


dba_objects shows CTXSYS.DRIXMD is INVALID and fails when compiled:

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
  2 FROM DBA_OBJECTS
  3 WHERE STATUS = 'INVALID'
  4 ORDER BY OWNER, OBJECT_NAME, OBJECT_TYPE;

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
CTXSYS               DRIXMD                         PACKAGE BODY

SQL> alter package ctxsys.drixmd compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY CTXSYS.DRIXMD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3482/3   PL/SQL: SQL Statement ignored
3482/37  PL/SQL: ORA-00942: table or view does not exist


Select on dba_tab_privs shows CTXSYS is missing the SELECT privilege on sys.SNAP$:

SQL> select 'TABLE' typ, grantee grantee, privilege priv,
  2         table_name tabnm, owner owner
  3  from dba_tab_privs
  4  where grantee='CTXSYS'
  5    and table_name like 'SN%'
  6  order by tabnm;

no rows selected

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms