How to install APEX in an existing database

I have based my receipe on Tim Hall's instructions found at www.oracle-base.com, and I do not want to take credit for this. But the installation steps was executed by myself, and my notes may come in handy for others.

In my case, a developer needed access to the APEX_JSON package, and as confirmed with Oracle Development, the package is not available outside the APEX software stack. However, when APEX is installed in an existing database, you can start using the functionality in this package even if you don't need anything else that comes with APEX.

Here is how I performed the minimal installation:


1. Create a dedicated tablespace:

create bigfile tablespace apex datafile '/u02/oradata/testdb01/apex.dbf' size 128M autoextend on next 32M maxsize unlimited;

2. Download the latest APEX installation bundle from http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html#close, and upload it to your server

3. Unzip the file

4. change directory to the apex folder

5. Install the software.
The header of the apexins.sql file states its usage:

Arguments:
Position 1: Name of tablespace for Application Express application user
Position 2: Name of tablespace for Application Express files user
Position 3: Name of temporary tablespace or tablespace group
Position 4: Virtual directory for APEX images

sqlplus / as sysdba @apexins.sql APEX APEX TEMP /i/

6. Set the admin password:

sqlplus / as sysdba @apxchpwd.sql

7. Create the APEX_LISTENER and schema APEX_REST_PUBLIC_USER:

sqlplus / as sysdba @apex_rest_config.sql

There are additional, optional steps to be configured, depending on your needs. See this article at Oracle-base.com

How to remove the APEX option from the database

cd $ORACLE_HOME/apex
sqlplus / as sysdba
@apxremov.sql
drop public synonym htmldb_system;
drop PACKAGE HTMLDB_SYSTEM;

If you have older APEX installations, they may be left in the database, but not registred in the dba_registry.

I had an old APEX schema called APEX_030200, with several invalid objects:

OWNER	OBJECT_TYPE	COUNT(*)
PUBLIC	SYNONYM	3
APEX_030200	PACKAGE	2
APEX_030200	PACKAGE BODY	114
APEX_030200	PROCEDURE	3

This means that the script above won't work. You will get this output when attempting to remove the installation:

sqlplus / as sysdba @apxremov.sql
...
Error:
You can only use this script to remove Application Express

I found some useful information about these situations at this blog

Basically, you can simply drop the old schema directly.

Check first:

SELECT username, 'drop user ' || username || ' cascade;' AS remove_statement
  FROM dba_users
 WHERE     (username LIKE 'FLOWS_%' OR username LIKE 'APEX_%')
       AND username NOT IN ('FLOWS_FILES',
                            'APEX_PUBLIC_USER',
                            'APEX_LISTENER',
                            'APEX_REST_PUBLIC_USER',
                            'APEX_INSTANCE_ADMIN_USER')
       AND username NOT IN (SELECT schema s
                              FROM dba_registry
                             WHERE comp_id = 'APEX');

So cleaning up can be done as easily as this:

drop user APEX_030200 cascade;