Oracle Application Express (APEX) Post Upgrade - Remove Old Installations

This article demonstrates how to remove old installations after an Oracle Application Express (APEX) upgrade.

Oracle Application Express (APEX) Installation

Connections

There are three ways you can install APEX, and the database connection you use will vary depending on which you are dealing with. 

The rest of the article assumes you are making the appropriate connection for your installation type.

-- APEX installed in Non-CDB Architecture.

CONN / AS SYSDBA

-- APEX installed in a CDB root container.

CONN / AS SYSDBA

-- APEX installed in a PDB.

CONN / AS SYSDBA

ALTER SESSION SET CONTAINER = {pdb-name};

-- Or

CONN sys@{pdb-name} AS SYSDBA

Check For Old Installations

The following query lists all APEX installations that are not the currently running installation.

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');

USERNAME

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

APEX_200200

SQL>

Drop Old Installations (Non-CDB or PDB)

For every old release we need to drop the APEX_XXXXXX user and the SYS.WWV_DBMS_SQL_APEX_XXXXXX package. For non-CDB instances and installations in a PDB we can run these commands directly.

-- 12.2 onward.

ALTER SESSION SET "_oracle_script"=true;

DROP USER APEX_200200 CASCADE;

DROP PACKAGE SYS.WWV_DBMS_SQL_APEX_200200;

From 12.2 onward, if you don't issue the ALTER SESSION command, you may receive the following error.

ERROR at line 1:

ORA-28014: cannot drop administrative users

Drop Old Installations (CDB)

For every old release we need to drop the APEX_XXXXXX user and the SYS.WWV_DBMS_SQL_APEX_XXXXXX package in all containers. We do this with the "catcon.pl" script. 

Here is an example of dropping an old 19.2 (190200) installation from a CDB root container.

# Drop the user.

$ORACLE_HOME/perl/bin/perl -I \

                           $ORACLE_HOME/rdbms/admin \

                           $ORACLE_HOME/rdbms/admin/catcon.pl \

                           -b drop_apex200200 -- --x'drop user APEX_200200 cascade'

# Drop the package.

$ORACLE_HOME/perl/bin/perl -I \

             $ORACLE_HOME/rdbms/admin \

             $ORACLE_HOME/rdbms/admin/catcon.pl \

            -b drop_wwv_dbms_sql -- --x'drop package SYS.WWV_DBMS_SQL_APEX_200200 cascade'

Network ACLs

The documentation suggests cleaning up any old network ACLs.

BEGIN

  DBMS_NETWORK_ACL_ADMIN.delete_privilege('my_acl.xml', 'APEX_200200');

  COMMIT;

END;

/

Dropping the database user means the network ACL principal is no longer available, so there is no risk associated with them, and they don't show up in the ACL views anymore. 

You may want to amend any ACL scripts you have in version control.

------------------For below 12.2 DB------------

$SQLPLUS / AS SYSDBA

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');

USERNAME

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

APEX_040200

SQL>DROP PACKAGE SYS.WWV_DBMS_SQL_APEX_040200;