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;
