Oracle Application Express
Return to Oracle APEX Home
Oracle Database XE and Application Express 3.1
Oracle Database 10g Express Edition (Oracle Database XE) includes Oracle Application Express (Oracle APEX) release 2.1. With the release of Oracle Application Express 3.1, you can now upgrade the Application Express within your Oracle Database XE. This will enhance your development experience by providing you with all the new features introduced in Oracle APEX 2.2, Oracle APEX 3.0 and Oracle APEX 3.1. Some of the most compelling improvements include PDF Printing, Flash Charts, Interactive Reports region, New Item Types, Improved Web Services support, Page and Region Caching, Supporting Objects (and the ability to import and install our latest Packaged Applications), Item Finder, new themes, and dictionary views of the Oracle APEX metadata.

Database Management Differences between Oracle APEX 2.1 and 3.1
If you decide to upgrade the Application Express within your Oracle Database XE, you will lose the Oracle APEX interface to perform some of your database management functions. This includes the ability to create and alter users, set database parameters, etc. To perform these functions, you will need to use either SQL Developer or SQL*Plus.

The following is a list of database management functions that cannot be performed using Oracle APEX 3.1 but that were features of Oracle APEX 2.1, the default interface of Oracle Database XE. For each, there is a SQL Statement provided that can be executed either in a SQL Worksheet of Oracle SQL Developer or using SQL*Plus when connected to your Oracle Database XE. Most of these commands need to be executed as the user SYS.

Feature Location (*) SQL Statement Notes
Change My Password under Tasks (also under Database Users)
alter user [username] identified by [new_password];
Within SQL Developer, right-click a user under Other Users, select Edit User, use the New Password and Confirm Password.

Please note that this will change your database user password. Once you upgrade, your database account is associated with your Application Express account but the passwords are separate. A change to one does not affect the other.

Manage HTTP Access under Tasks To set HTTP Access to Local only:
  exec dbms_xdb.setListenerLocalAccess
       (l_access => TRUE);

To set HTTP Access to allow remote connects:
  exec dbms_xdb.setListenerLocalAccess
       (l_access => FALSE);
 
Usage Monitor Storage (also on Home page)
select nvl(b.tablespace_name,
         nvl(a.tablespace_name,'UNKNOWN'))
         tablespace_name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0)
         size_alloc_bytes,
       round(((kbytes_alloc-nvl(kbytes_free,0))/
         kbytes_alloc)*200) used_chart,
       to_char(((kbytes_alloc-nvl(kbytes_free,0))/
         kbytes_alloc)*100,
         '999G999G999G999G999G999G990D00') ||'%' used,
       data_files
  from ( select sum(bytes)/1024/1024 Kbytes_free,
              max(bytes)/1024/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024 Kbytes_alloc,
              tablespace_name, count(*) data_files
       from sys.dba_data_files
       group by tablespace_name )b
 where a.tablespace_name (+) = b.tablespace_name
You can access a Workspace Utilization report in Oracle APEX 3.1 under Administration > Manage Services > Workspace Overview > Detailed Tablespace Utilization Report.
View Logging Status Storage
select log_mode
  from v$database;
 
Compact Storage Storage
exec dbms_space.database_level_shrink;
 
Configure SGA Memory
alter system
   set sga_target =  
                                          
sga_target
   scope=spfile;
                                        
 
Configure PGA Memory
alter system
   set pga_aggregate_target =  
                                          
pga_agg_target
   scope=both;
                                        
 
Create Database User Database Users
create user [username] identified by [password];
Within SQL Developer, right-click on the Other Users node under your XE-SYS connection and select Create User.
Drop Database User Database Users
drop user [username];
Within SQL Developer, you can also right-click on the Other Users node under your XE connection and select Drop User.
Lock Database Account Database Users
alter user [username] account lock;
Within SQL Developer, right-click a user under Other Users under your XE-SYS connection. Select edit user and check 'Account is Locked'.
Unlock Database Account Database Users
alter user [username] account unlock;
Within SQL Developer, right-click a user under Other Users under your XE-SYS connection. Select edit user and uncheck 'Account is Locked'.
* The location is within the Home > Administration menu of the Oracle Database XE interface.


Interface Differences between Oracle APEX 2.1 and 3.1
When accessing your upgraded Oracle APEX, you will be presented with a login page that prompts for Workspace, Username and Password. Previously, you were only prompted for Username and Password. To login, your Workspace would just be the same as your Username. It is important to note that within Application Express, the change password function changes only your Application Express password, not your database password. Once you upgrade, your schema is associated with your workspace but your database account is now separate. If you create new database users, they will not automatically have access to Application Express.

The Home page will appear different. There is no icon for Administration. As noted above, most database administration functions are not part of Application Express. Two exceptions are the Database Monitor and About Database. In Oracle Database XE, these are under the Administration menu. Within Oracle APEX 3.1, they are under Utilities. There are no icons for the Object Browser and SQL, those functions are part of the SQL Workshop. Other Administration functions are grouped on the far right in a task region and these pertain mostly to the administration of the workspace, not the database.

How to Upgrade
If you have reviewed the differences described above and want to proceed to install Application Express 3.1 in your Oracle Database XE, first follow step 3.2 of the Application Express Installation Guide. The command and arguments used to install APEX 3.1 via SQL*Plus should be:

  @apexins SYSAUX SYSAUX TEMP /i/

Then to change the password for the admin account run apxchpwd.sql and when prompted enter a password for the ADMIN account.

 @apxchpwd
Once you complete the standard install, you need to perform the following steps to complete the upgrade:
  1. Connect to SQL*Plus as SYS
  2. @APEX_HOME/apex/apxldimg.sql APEX_HOME
  3. @APEX_HOME/apex/apxxepwd.sql password
    (where password is the password of the Application Express internal ADMIN account)
For the steps above, APEX_HOME is the directory where the Application Express software was unzipped.

When logging into Application Express 3.1 in Oracle Database XE, you will now be presented with a login page that prompts for Workspace, Username and Password. The Workspace name is equal to the database username used to login to Oracle Database XE. For example, in Oracle Database XE, if you used credentials Username=SCOTT, Password=TIGER, in Application Express 3.1, you will now use Workspace=SCOTT, Username=SCOTT, Password=TIGER.

For more information about Oracle Database XE, visit the Oracle Database 10g Express Edition OTN site. Please note that this Application Express upgrade does not change the support policy of Oracle Support. Oracle Database XE is supported only via the OTN forums. Oracle Support will not answer questions about any Oracle APEX 3.1 on XE issues, installation or otherwise.