Oracle Application Express

Upgrading Oracle Application Express within Oracle Database Express Edition (XE)

Oracle Database 11 g Express Edition (Oracle XE) includes Oracle Application Express release 4.0. It is strongly recommended that you upgrade to the latest release of Oracle Application Express to take advantage of all the latest features.

How to Upgrade

To install the latest version of Oracle Application Express in your Oracle Database XE, first download the latest version of Application Express from the Oracle Technology Network.

1. Unzip the downloadloaded zip file:

• Linux: Unzip <filename>.zip
• Windows: Double click <filename>.zip in Windows Explorer
• [Note: You should keep the directory tree where you unzip the files short and not under directories that contain spaces.
  For example, within Windows unzip to C:\.]

2. Change your working directory to apex.

3. Start SQL*Plus and connect to the Oracle XE database:

• Linux:

  $ sqlplus /nolog
  	SQL> CONNECT SYS as SYSDBA
  	Enter Password:	
  	SYS_Password

• Windows:

  {Command prompt} C:\apex> sqlplus /nolog
  	SQL> CONNECT SYS as SYSDBA
  	Enter Password:	
  	SYS_Password
  

• [Note: You should keep the directory tree where you unzip the files short and not under directories that contain spaces.
  For example, within Windows unzip to C:\.]

4. Install Application Express:

• SQL> @apexins SYSAUX SYSAUX TEMP /i/

5. Log back into SQL*Plus (as above) and load images:

• SQL> @apxldimg.sql APEX_HOME

• [Note: APEX_HOME is the directory you specified when unzipping the file. For example, with Windows ‘C:\’.]

6. Upgrade Application Express password:

• SQL> @apxchpwd
  Enter password for Application Express ADMIN account.

7. In a Web browser, navigate to the Oracle Application Express Administration Services application:

• http://localhost:8080/apex/apex_admin
• In Username, enter ADMIN
• In Password, enter the password entered in Step 6

Oracle Database 10 g Express Edition Specific Considerations

Interface Differences between Oracle APEX 2.1 and later versions of Application Express

Oracle Database 10 g Express Edition (Oracle XE) includes Oracle Application Express release 2.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. As noted below, most database administration functions are not part of Application Express. Two exceptions are the Database Monitor and About Database which are found under the Administration menu. There are no icons for the Object Browser and SQL, those functions are part of the SQL Workshop. Other Administration functions pertain mostly to the administration of the workspace, not the database.

Database Management Differences between Oracle APEX 2.1 and later versions of Application Express

Once you upgrade 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 in later versions of Application Express 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.

• Change my Password
  Location: under Tasks (also under Database Users)
  SQL Statement:

  alter user [username] identified by [new_password];
  

  Notes: 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
  Location: under Tasks
  SQL Statement:
  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
  Location: LOC
  SQL Statement:

  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
  

  Notes: You can access a Workspace Utilization report in Oracle APEX 3.0.1 under Administration > Manage Services > Workspace Overview > Detailed Tablespace Utilization Report.

• View Logging Status
  Location: Storage
  SQL Statement:

  select log_mode 
    from v$database;
  

• Compact Storage
  Location: Storage
  SQL Statement:

  exec dbms_space.database_level_shrink;

• Configure SGA
  Location: Memory
  SQL Statement:

  alter system 
  	set sga_target = sga_target 
  	scope=spfile;
  

• Configure PGA
  Location: Memory
  SQL Statement:

  alter system 
  	set pga_aggregate_target = pga_agg_target 
  	scope=both;

• Create Database User
  Location: Database Users
  SQL Statement:

  create user [username] identified by [password];

  Notes: Within SQL Developer, right-click on the Other Users node under your XE-SYS connection and select Create User.

• Drop Database User
  Location: Database Users
  SQL Statement:

  drop user [username];

  Notes: Within SQL Developer, you can also right-click on the Other Users node under your XE connection and select Drop User.

• Lock Database Account
  Location: Database Users
  SQL Statement:

  alter user [username] account lock;

  Notes: 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
  Location: Database Users
  SQL Statement:

  alter user [username] account unlock;

  Notes: Within SQL Developer, right-click a user under Other Users under your XE-SYS connection. Select edit user and uncheck ‘Account is Locked’.

• Note The location is within the Home > Administration menu of the Oracle Database XE interface.