Oracle 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:
- Save Upgrade Downtime – Upgrade Oracle APEX upfront
- Upgrade downtime credited to APEX
- Is your APEX version certified with your database release?
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.
This 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_LOG;
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.
- Create a
/home/oracle/clean_apex.sqlscript containing:drop public synonym APEX_SPATIAL;
- Execute
/home/oracle/clean_apex.sqlscript withcatcon.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
- 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/
Hello Mike,
Thank you so much for the post!!!
It worked almost 100% for me, to remove APEX from 12cR1 (12.1.0.2), but still have some leftovers (synonymns), that I have fixed like this:
— On CDB (this I think it is covered by your post).
drop public synonym APEX_PKG_APP_INSTALL_LOG;
drop public synonym APEX_SPATIAL;
drop public synonym WWV_FLOW_SPATIAL_API;
— connected on each pdb
exec dbms_pdb.exec_as_oracle_script(‘drop public synonym WWV_FLOW_SPATIAL_API’);
exec dbms_pdb.exec_as_oracle_script(‘drop public synonym APEX_PKG_APP_INSTALL_LOG’);
exec dbms_pdb.exec_as_oracle_script(‘drop public synonym APEX_SPATIAL’);
All the Best,
Rodrigo Mufalani
Hi Rodrigo,
thanks for sharing – I didn’t do this exercise in 12.1 Multitenant (or at least I didn’t document it here).
I blogged about it in the past:
https://mikedietrichde.com/2015/11/19/why-you-should-remove-apex-from-the-cdbroot/
but haven’t documented all the detail steps.
Thanks!
Mike
Thank you very useful!
Welcome 🙂
Can the article be extended for Apex removal in 19c? Thanks.
Hi Federica,
it works the same way for 19c – “12.2” is synonym for 12.2.0.1, 18c and 19c – I don’t differ in the removal procedures.
And for APEX, it hasn’t changed.
Cheers,
Mike