Oracle Text (CONTEXT) Clean Up in Oracle Database 11.2-12.2

Oracle Spatial (SDO) Clean Up in Oracle Database 11.2-12.2Oracle Text (CONTEXT) clean up in Oracle Database 11.2-12.2 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-12.2

Oracle Text (CONTEXT) Clean Up in Oracle Database 11.2-12.2Oracle 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 – 12.2 . 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 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

That’s it!

Component Clean Up Series

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

 

–Mike

Leave a Reply

Your email address will not be published. Required fields are marked *