Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2

Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2Oracle APEX (Application Express) clean up in Oracle Database 11.2-12.2 is fairly simple and straight forward. With the upgrade to Oracle Database 12.2.0.1 Oracle APEX does not get upgraded automatically anymore. If necessary you should upgrade Oracle APEX upfront of afterwards. But please do keep an eye on the APEX certification with Oracle Database 12.2.0.1 to avoid any issues:

Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2

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.

Oracle APEXThis blog post does not recommend to remove APEX from an Oracle Database. I by myself use Oracle APEX quite often, there’s a huge developer community out there – and APEX is one of the coolest things Oracle ever did in addition to the database.

APEX removal is non-invasive

But there may be situations when you have to remove APEX. Maybe it could have been accidentally installed into the CDB$ROOT of a Multitenant database: Why you should remove APEX from CDB$ROOT.

Removing APEX does remove all APEX applications as well. Export existing APEX applications upfront.

Oracle Database 11.2.0.4

In Oracle Database 11.2.0.4 APEX gets installed by default as version 3.2.1.00.12. The removal procedure is documented in MOS Note:558340.1 – How to Uninstall Oracle HTML DB / Application Express from a 10G/11G Database.

SQL> @?/apex/apxremov.sql
SQL> drop package HTMLDB_SYSTEM;

That’s very simply and straight forward. And it works. The leftover package HTMLDB_SYSTEM is an artifact from older versions of APEX and should be removed (see MOS Note:1231863.1).

Oracle Database 12.1.0.2

In Oracle Database 12.1.0.2 the APEX removal instructions are in the Application Express Installation Guide. The script for an APEX removal remains the same, regardless of executed in a non-CDB or CDB. APEX gets installed by default in version 4.2.5.00.08.

$ cd $ORACLE_HOME/apex
$ sqlplus / as sysdba
SQL> @apxremov.sql
SQL> drop PUBLIC SYNONYM APEX_SPATIAL;
SQL> drop PUBLIC SYNONYM APEX_PKG_APP_INSTALL_LOGIN;

Finally there may be two leftover synonyms APEX_SPATIAL and APEX_PKG_APP_INSTALL_LOGIN requiring clean up.

Oracle Database 12.2.0.1

The default APEX version in Oracle Database 12.2.0.1 is APEX 5.0.4.00.12. Again, the removal scripts is the same for non-CDBs and CDBs. The documentation describes the process only for CDBs but fortunately there are no leftovers for non-CDBs.

Non-CDB
$ cd $ORACLE_HOME/apex
$ sqlplus / as sysdba
SQL> @apxremov.sql
CDB

In this case the script will utilized catcon.pl. Therefore I’m adding perl to the PATH variable.

$ cd $ORACLE_HOME/apex
$ PATH=$PATH:$ORACLE_HOME/perl/bin/perl
$ sqlplus / as sysdba
SQL> @apxremov.sql

Unfortunately there are leftovers after this process: the public synonym APEX_SPATIAL in all containers except CDB$ROOT and PDB$SEED will exist if Oracle Spatial is present. The removal must be done with catcon.pl.

  1. Create a /home/oracle/clean_apex.sql script containing:
    drop public synonym APEX_SPATIAL;
  2. Execute /home/oracle/clean_apex.sql script with catcon.pl:
    $ cd $ORACLE_HOME/rdbms/admin
    $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b clean_apex -d /home/oracle clean_apex.sql
    

     

Component Clean Up Series

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