Oracle Text (CONTEXT) Clean Up in Oracle Database 11.2-19c

Oracle Text (CONTEXT) clean up in Oracle Database 11.2-19c is an interesting topic. A removal script exists but some additional tasks may be necessary depending on the database release.

Oracle Text (CONTEXT) Clean Up in Oracle Database 11.2-19c

Oracle Text (formerly known as Context Option) is a excellent feature a lot of people using actively. You can access all the necessary resources on OTN.

Oracle Text uses standard SQL to index, search, and analyze text and documents stored in the Oracle database, in files, and on the web. Oracle Text can perform linguistic analysis on documents, as well as search text using a variety of strategies including keyword searching, context queries, Boolean operations, pattern matching,

Before you start removing anything from your database please make sure you read my introductory blog post about how to Remove and Clean Up Components from Oracle 11.2 – 19c . You’ll find links to monitor proper component removal there as well as in the SQL Scripts Repository on this blog.

Removal of Oracle Text is described until Oracle Database 11.2.0.4 in MOS Note: 970473.1 – Manual installation, deinstallation of Oracle Text 11.2

Oracle Database 11.2.0.4

You can approach the removal of Oracle Text simply with this script and a drop command:

$ sqlplus / as sysdba
SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;

Due to dependencies XDB.DBMS.XDBT will become INVALID. According to MOS Note:296869.1 you can issue the following commands to clean up the situation:

SQL> drop package XDB.dbms_xdbt;
SQL> drop procedure xdb.xdb_datastore_proc;
SQL> start ?/rdbms/admin/utlrp.sql

The final recompilation is required but can be done online.

Oracle Database 12.1.0.2

The cleanup process for Oracle Text in Oracle Database 12.1.0.2 is exactly the same as it was in Oracle Database 11.2.0.4. You start by simply running the removal script and a cleanup command:

$ sqlplus / as sysdba
SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;

Due to dependencies XDB.DBMS.XDBT will become INVALID. According to MOS Note:296869.1 you can issue the following commands to clean up the situation:

SQL> drop package XDB.dbms_xdbt;
SQL> drop procedure xdb.xdb_datastore_proc;
SQL> start ?/rdbms/admin/utlrp.sql

The final recompilation is required but can be done online.

Oracle Database 12.2.0.1

non-CDB

The same process for Oracle Text works in Oracle Database 12.2.0.1 for non-CDBs as it did in previous releases. Start by simply running the removal script and a cleanup command:

$ sqlplus / as sysdba
SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;

Due to dependencies XDB.DBMS.XDBT will become INVALID. According to MOS Note:296869.1 you can issue the following commands to clean up the situation:

SQL> drop package XDB.dbms_xdbt;
SQL> drop procedure xdb.xdb_datastore_proc;
SQL> start ?/rdbms/admin/utlrp.sql

The final recompilation is required but can be done online.

CDB

For Oracle Multitenant databases in Oracle Database 12.2.0.1 – 19c the cleanup process is technically identical to non-CDBs with the exception that you’ll have to use catcon.pl to execute the scripts. I will remove Oracle Text from all PDBs first, then clean it up from the CDB$ROOT.

At first I start the removal process from the PDBs:

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b catnoctx_pdbs -d $ORACLE_HOME/ctx/admin catnoctx.sql

As this step has some leftovers I will clean them up with a short script:

$ vi ctx_leftovers.sql
        drop procedure sys.validate_context;
        drop package XDB.dbms_xdbt;
        drop procedure xdb.xdb_datastore_proc;

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b ctx_leftovers_pdbs -d '''.''' ctx_leftovers.sql

Additionally I will repeat the same steps within the CDB$ROOT container:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnoctx_cdb -d $ORACLE_HOME/ctx/admin catnoctx.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b ctx_leftovers_cdb -d '''.''' ctx_leftovers.sql

And finally I will have to remove a leftover public synonym from all containers:

$ vi ctx_rmsyn.sql
           drop public synonym DBMS_XDBT;

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b ctx_rmsys -d '''.''' ctx_rmsyn.sql

The leftovers seen in the 12.2.0.1 process are still present in 19.12.0 where I tested the process again.

Component Clean Up Series

• Remove and Clean Up Components from Oracle 11.2 – 12.2 – General Guidelines and Precautions
• APEX – Oracle Application Express Clean Up
• OWM – Oracle Workspace Manager Clean Up
• DV – Oracle Database Vault Clean Up
• OLS – Oracle Label Security Clean Up
• SDO – Oracle Spatial Data Option Clean Up
• CONTEXT – Oracle Text Clean Up
• ORDIM – Oracle Multi Media Clean Up
• XOQ – Oracle OLAP API Clean Up
• APS – Oracle OLAP Analytical Workspace Clean Up
• AMD – Oracle OLAP Catalog Clean Up
• OWB – Oracle Warehouse Builder Clean Up
• EXF/RUL – Oracle Expression Filters and Rules Manager Clean Up
• EM – Enterprise Manager Database Control Clean Up
• JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up
• XDB – Oracle XML Database Clean Up

Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/

–Mike