Cleaning Up

The architecture of Application Express is such that the database objects associated with a specific release all exist in a single schema. For the original Oracle HTML DB 1.5, all of the database objects existed in schema FLOWS_010500. As of the most recent version, Oracle Application Express 3.2, all of the database objects reside in schema APEX_030200.

When a new version of Application Express is installed, the following three actions take place:

1. The new version is installed into a new schema (this happens the same whether it's a new installation or an upgrade installation).
2. The meta data from the previous version is copied and sometimes transformed into the new schema.
3. All of the public synonyms which were pointing at the "old" version are now redirected to the new version of Application Express.

The beauty of this architecture is if an error occurs during installation or during upgrade, it's easy to revert back to the previous installation, as documented here. Both an APEX 3.1 installation (in FLOWS_030100) and an APEX 3.2 installation (in APEX_030200) can exist in the Oracle database at the same time, and with only one version active. Understand, though, if you choose to rollback and remove the newer version of Application Express, all of the changes made in the newer version (instance settings, application changes, new workspaces, etc.) will not be propagated back to the old version of Application Express if you switch schemas.

Once you have successfully upgraded your instance of Application Express, tested all of the upgraded applications, and deem the upgrade successful, there is no reason to maintain the old version of Application Express. Not only does it take up space in your database, it leaves a fairly privileged database account in the database, and this could pose a security risk if compromised.

For apex.oracle.com, which currently has over 20,000 workspaces, I always install the new version of Application Express into it's own tablespace. I do not install Application Express into the default SYSAUX tablespace. Application Express 3.1 was installed into tablespace APEX_REL31. When I recently upgraded to Application Express 3.2, and after we ran successfully for a couple days, it was easy to clean up the old Application Express 3.1 by issuing:

DROP USER FLOWS_030100 CASCADE;
DROP TABLESPACE APEX_REL31 INCLUDING CONTENTS AND DATAFILES;

That's it! The privileged database user from the previous version is gone. All of the space on disk is reclaimed. And the newer version of Application Express 3.2 in APEX_030200 hums along just fine.


Note: There is one schema installed with Application Express, FLOWS_FILES, which persists across version upgrades. The sole purpose of this schema is to maintain uploaded files. You should never manually remove this schema unless you want to remove every facet of Oracle Application Express from your database. And if you really wanted to remove every trace of Application Express, I'd recommend using the apxremov.sql script anyway.



Considerations when dropping on Database 11g

If you're using Application Express with Oracle Database 11g (11.1.0.6 or greater), undoubtedly you had to enable Network Services so Application Express could send e-mail, use Web Services, etc. If you didn't do this, then you'll encounter errors like "ORA-24247: network access denied by access control list (ACL)" when APEX attempts to send e-mail or access any other network resource.

One important caveat to the instructions above - if you created an ACL for FLOWS_030100 (Application Express 3.1) and granted connect privileges to FLOWS_030100, after you upgrade to Application Express 3.2 , grant these same connect privileges on the existing ACL to APEX_030200, and you drop database user FLOWS_030100, the Network ACL will contain a dangling reference to FLOWS_030100 and will be invalid. What this means is that if you're running along just fine with an upgraded Application Express 3.2 and you drop FLOWS_030100, your delivery of e-mail may no longer work. Users will see error messages like "ORA-24247: network access denied by access control list (ACL)" in APEX_MAIL_LOG.MAIL_SEND_ERROR.

This situation happened to me over the weekend. After Application Express 3.2 was running for a month on our internal instance, the old FLOWS_030100 database user was finally dropped. Numerous issues were raised on Monday morning by workspace owners who said they could no longer send e-mail and they were seeing the error message "ORA-24247: network access denied by access control list (ACL)" in APEX_MAIL_LOG.MAIL_SEND_ERROR.

The only way I've discovered to rectify this situation is to manually drop the ACL reference to FLOWS_030100. I connected as SYS in SQL*Plus and ran:

exec dbms_network_acl_admin.delete_privilege('power_users.xml','FLOWS_030100',TRUE,'connect');

This underlying database issue, with the ACL privileges not being dropped when a database user is dropped, was filed as Bug 7493477 and is targeted to be fixed in Database 11gR2.