XDB.DBMS_XDBT Package is INVALID – Could mean install of Oracle Text
February 14, 2011 Leave a comment
While Oracle XDB and Oracle Text are two independent products, package XDB.DBMS_XDBT is used to setup XDB context index. The package XDB.DBMS_XDBT requires that Oracle Text is installed.
SQL> alter package XDB.DBMS_XDBT compile body; Warning: Package Body altered with compilation errors. SQL> show error Errors for PACKAGE BODY XDB.DBMS_XDBT: LINE/COL ERROR -------- ----------------------------------------------------------------- 34/7 PL/SQL: Statement ignored 34/7 PLS-00201: identifier 'CTXSYS.CTX_OUTPUT' must be declared 37/5 PL/SQL: Statement ignored 37/5 PLS-00201: identifier 'CTXSYS.CTX_OUTPUT' must be declared 38/5 PL/SQL: Statement ignored 38/33 PLS-00201: identifier 'CTXSYS.CTX_OUTPUT' must be declared 71/5 PL/SQL: Statement ignored 71/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared 75/5 PL/SQL: Statement ignored 75/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared 79/5 PL/SQL: Statement ignored 79/5 PLS-00201: identifier 'CTX_DDL.DROP_SECTION_GROUP' must be declared 83/5 PL/SQL: Statement ignored 83/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared 86/5 PL/SQL: Statement ignored 86/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared 90/5 PL/SQL: Statement ignored 90/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared 94/5 PL/SQL: Statement ignored 94/5 PLS-00201: identifier 'CTX_DDL.DROP_STOPLIST' must be declared
There are 2 options. Either you can choose to install oracle text or this object can be ignored or can be dropped if you don’t plan on using Oracle Text in the future.
Installing Oracle Text 11.1.0.7.0
Full guide can be found in note ‘Manual installation, deinstallation and verification of Oracle Text 11gR1 [ID 579601.1]’
sqlplus "/ as sysdba" spool text_install.txt @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK
Where:
change_on_install – is the ctxsys user password
SYSAUX – is the default tablespace for ctxsys
TEMP – is the temporary tablespace for ctxsys
LOCK|NOLOCK – ctxsys user account will be locked or not
The next step is to install appropriate language-specific default preferences – all the available default preferences are in the $ORACLE_HOME/ctx/admin/defaults directory:
connect "CTXSYS"/"change_on_install" @?/ctx/admin/defaults/drdefuk.sql;
To then fix the invalid package:
grant execute on ctxsys.ctx_ddl to XDB; grant execute on ctxsys.ctx_output to XDB; grant ctxapp to XDB; alter user ctxsys account lock password expire; @?/rdbms/admin/dbmsxdbt.sql ## Recompile package alter package XDB.DBMS_XDBT compile; alter package XDB.DBMS_XDBT compile body; show errors package body XDB.DBMS_XDBT spool off
Validate the Install of Oracle Text for 11.1.0.7
-- List version SELECT comp_name, status, substr(version,1,10) AS version FROM dba_registry WHERE comp_id = 'CONTEXT'; SELECT * FROM ctxsys.ctx_version; SELECT substr(ctxsys.dri_version,1,10) VER_CODE FROM dual; -- Should be 372 objects SELECT count(*) FROM dba_objects WHERE owner='CTXSYS'; -- invalid objects SELECT object_name, object_type, status FROM dba_objects WHERE owner='CTXSYS' and status != 'VALID' ORDER BY object_name;