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.

For more information about Oracle Database XE, visit the Oracle Database 10 g 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 Application Express on Oracle XE issues, installation or otherwise.

 
false ,,,,,,,,,,,,,,,,