Removing Prior Versions of APEX in a PDB

Dimitri Gielis discovered a possible gap in our Application Express Installation Guide about removing prior versions of APEX, when APEX is installed locally in a PDB. If you follow our recommendation to remove the prior version of APEX after a successful upgrade to APEX 18.1, you might run into something similar to the following:

SQL> alter session set container=APEX18;

Session altered.

SQL> SELECT username

  FROM dba_users 

 WHERE (  username LIKE 'FLOWS\_______' ESCAPE '\'

       OR username LIKE 'APEX\_______' ESCAPE '\' )

   AND username NOT IN ( SELECT schema

                           FROM dba_registry

                          WHERE comp_id = 'APEX' );  2    3    4    5    6    7  

USERNAME

--------------------------------------------------------------------------------------------------------------------------------

APEX_050100

SQL> DROP USER APEX_050100 CASCADE;

DROP USER APEX_050100 CASCADE

*

ERROR at line 1:

ORA-28014: cannot drop administrative users

You would encounter this error if you followed section "4.14.3 Removing Schemas from Prior Installations" in the Installation Guide. The section you actually need to follow is "4.14.4 Removing Schemas from Prior Installations in a CDB." Now, you may argue that since APEX is installed locally, you are not installed in a CDB. But you actually are. You just don't have APEX installed in CDB$ROOT, but your database architecture is still a CDB. You need to use catcon.pl to drop the Oracle Maintained APEX_050100 schema like the following example:

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex050100 -- --x'drop user APEX_050100 cascade'

The command above though will run in every container, or PDB, in the CDB. In some containers, APEX_050100 will not exist, and you will see an error in the catcon logs. Also, you may only want to drop the APEX_050100 schema from a particular PDB. There is the gap in the documentation, and what we need to add to the next version. In my case, my PDB was called APEX18, so to drop APEX_050100 only in the APEX18 PDB, the command would be:

$ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -b drop_apex050100 -c 'APEX18' -- --x'drop user APEX_050100 cascade'

We will add this example as well as try and make the section title and lead in more clear in the next version of the Installation Guide.