8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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

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

Connections
Check For Old Installations
Drop Old Installations (Non-CDB or PDB)
Drop Old Installations (CDB)
Network ACLs

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_190200

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_190200 CASCADE;
DROP PACKAGE SYS.WWV_DBMS_SQL_APEX_190200;

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_apex190100 -- --x'drop user APEX_190200 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_190200 cascade'

Network ACLs

The documentation suggests cleaning up any old network ACLs.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege('my_acl.xml', 'APEX_190200');
  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 more information see:

Hope this helps. Regards Tim...

Back to the Top.