DBA Hub

📋Steps in this guide1/5

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 miscconfigurationintermediate
by OracleDba
12 views
1

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
-- 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
2

Check For Old Installations

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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>
3

Drop Old Installations (Non-CDB or PDB)

For every old release we need to drop the user and the package. For non-CDB instances and installations in a PDB we can run these commands directly. From 12.2 onward, if you don't issue the ALTER SESSION command, you may receive the following error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
-- 12.2 onward.
ALTER SESSION SET "_oracle_script"=true;

DROP USER APEX_190200 CASCADE;
DROP PACKAGE SYS.WWV_DBMS_SQL_APEX_190200;

ERROR at line 1:
ORA-28014: cannot drop administrative users
4

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
# 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'
5

Network ACLs

The documentation suggests cleaning up any old network ACLs. 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: - Performing Post Installation Tasks for Upgrade Installations - Oracle Application Express (APEX) Installation - APEX Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege('my_acl.xml', 'APEX_190200');
  COMMIT;
END;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!