Oracle Application Express (APEX) is a product provided by Oracle that is
used for rapid application development to create web enabled applications to
run against an Oracle database. Being primarily a developer’s tool, the
question arises…should DBAs be overly concerned about APEX? And the short
answer to this question is…YES!
The three main reasons that DBAs do need to be concerned are first, it must
be installed into the database, http server access to the database must be
enabled for APEX based applications to run and there are several privileges
granted to the workspace administrator accounts that the DBA should be aware
of. Additionally there are always space requirements for each of the workspaces
that the DBA needs to be aware of if managing a database that is going to
support applications designed using Oracle Application Express.
Choosing the HTTP Server Access
The first decision that should be made is to determine which kind of HTTP
Server Access. There are slightly different detailed installation steps
depending on which access method is chosen.
Application Express has three options (if installed into an Oracle 11g
database) for configuring the required HTTP Server Access to the database; the
Oracle HTTP Server with mod_plsql, the Oracle Application Express Listener or
the Embedded PL/SQL Gateway. All of these methods are used to establish
communications between the web server and the APEX objects in the database
using a SQL*Net connection.
Application Express Listener
This is a java based web server and is a multi tier option that allows for
separation of the listener tier from the database tier. This is downloaded
separately from Oracle and must be unzipped and then separately installed and
HTTP Server with mod_plsql
Using the Oracle HTTP Server requires the mod_plsql plug-in and the proper
configuration of the dads.conf file for the HTTP Server.
The huge advantage of the HTTP Server with mod_plsql is that it allows for
separation of the mid tier from the database tier. If your application is going
to be placed outside of a firewall (for example, external web based applications)
the DBA should always go with the HTTP Server with mod_plsql option rather than
using the Oracle Application Express Listener.
Embedded PL/SQL Gateway
This is installed in the database as part of the Oracle XML DB HTTP Server.
However, it should be noted, that there are two distinct disadvantages to
using the embedded gateway option. First, there is no way to separate the
listener from the database, which would be a security risk for internet based
applications. Second, it is not as feature rich, when it comes to configuration
and logging, as the Oracle HTTP server with mod_plsql.
Installing Application Express
Before beginning the installation (Application Express 4.0), the following
system requirements must be met:
a) Database Version 10.2.0.3 or higher
b) Shared pool size at least 100M (or use Automatic Shared Memory Management or
Automatic Memory Management)
c) Free space in the APEX tablespace 185M (plus 75M for each additional
d) Free space in the SYSTEM tablespace 100M
e) Oracle XML DB must be installed, and the associated anonymous user must not
f) Oracle Text is required (but this is installed by default)
g) PL/SQL Web Toolkit version 10.1.2.0.6 or later
Running the Installation Scripts
Application Express can be downloaded from Oracle ( Application
Express Download Site ). There are two versions of Application Express, the
full development version and a runtime version. After downloading and staging
the software, running the following command (must be run while connected as
sysdba) installs the full version:
@apexins <apex_tbs> <apex_files_tbs> <tmp_tbs> /i/ <apex_tbs> is the tablespace for the APEX application user <apex_files_tbs> is the tablespace for the APEX files user <tmp_tbs> is the temporary tablespace /i/ the virtual directory for APEX images - using /i/ is recommended for future upgrade support
The following script has the same parameters, but installs the runtime
version of Application Express.
@apexrtins <apex_tbs> <apex_files_tbs> <tmp_tbs> /i/
The following three accounts will be created in the database – APEX_040000
(owner of the application express schema and meta data), FLOWS_FILES (owner of
any uploaded files by application express) and APEX_PUBLIC_USER (a low
privilege account used by the HTTP Server with mod_plsql)
Changing the Application Express Administrator Password
A default ADMIN account is set up to be used when first logging into the
Application Express Administration site. This is the primary set of tools used
by the Application Express Administrator to create and manage workspaces in the
Oracle database. This is not the account that is set up for each workspace to
be the workspace administrator. There is generally only one Application Express
Administrator while there is at least one workspace administrator account for
each workspace created.
This password should always be changed using the provided script.
If using the HTTP Server with mod_plsql (recommended), then the
APEX_PUBLIC_USER must be configured properly. Log in as sysdba and set the
password for the account.
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY password
It is also important to make sure the password for the APEX_PUBLIC_USER does
not expire because your application will become unusable if the password
changes and the dads.conf file is not updated properly.
The best method is to create a password profile that does not expire the
password and assign that profile to the APEX_PUBLIC_USER account.
CREATE PROFILE apex_user_prfl LIMIT PASSWORD_LIFE_TIME unlimited; ALTER USER APEX_PUBLIC_USER PROFILE apex_user_prfl;
Copy the Images
The images for Apex must be copied from the apeximages directory to the home
for the Oracle HTTP Server. The following example could be used on a unix
cp -rf $ORACLE_HOME/apex/images HTTPSERVERHOME/Apache
Configure the dads.conf file
Configure the dads.conf file (if using the HTTP Server with mod_plsql) – the
following is a typical dads.conf entry for Application Express:
Alias /i/ "HTTPSERVER_HOME/Apache/images/" <Location /pls/apex Order deny, allow PlsqlDocumentPath docs AllowOverride None PlsqlDocumentProcedure www_flow_file_mgr.process_download PlssqlDatabaseConnectString host:listenerport:service PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8 PlsqlAuthenticationMode Basic SetHandler pls_handler PlsqlDocumentTablename wwv_flow_file_objects$ PlsqlDatabaseUsername APEX_PUBLIC_USER PlsqlDefaultPage apex PlsqlDatabasePassword <password set earlier> PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize Allow from all </Location>
Enable Network Services (for 11g databases)
By default, in Oracle 11g (both R1 and R2) the ability to interact with
network services is disabled. The DBMS_NETWORK_ACL_ADMIN package must be used
to grant connect privileges to any host for the APEX_040000 database user
For more details on using the package, refer to the online installation
documentation for Application Express (Application
Express Installation Guide)
In order for Application Express to run successfully, it submits jobs, and
requires JOB_QUEUE_PROCESSES to be set. A recommended starting number is 20.
Privileges Granted to the Workspace Administrator Accounts
Each workspace that is created is associated with a workspace administrator
database account in the database. By default, there are a number of privileges assigned
to this account, and the workspace administrator is able to connect via tools
other than just Application Express.
The following is a list of the privileges that are assigned to the workspace
CREATE ANY CONTEXT
CREATE MATERIALIZED VIEW
With the popularity of Oracle Application Express on the rise, database
administrators should be aware of the features, installation options,
listener/communication options and privileges, and space associated with the
workspaces and user accounts associated with Oracle Application Express.