B Upgrading Oracle Application Express within Oracle Database Express Edition

B.2.1 User Interface Differences

This section describes the differences between Oracle Application Express release 2.1 and later releases:

• Login - When accessing an upgraded version of Oracle Application Express, the login page that prompts you for a workspace, username, and password. The previous version only prompts for a username and password. When you log in, your workspace is the same as your username.

• Changing Password - Within Oracle Application Express, the change password function only changes your Oracle Application Express password, not your database password. Once you upgrade, your schema is associated with your workspace, but your database account is now separate. Note that if you create new database users, they will not automatically have access to Oracle Application Express.

• Home Page - The Home page displays differently in newer versions of Oracle Application Express. As noted in "Database Management Differences," most database administration functions are not part of Oracle Application Express. The two exceptions are that the Database Monitor and About Database which are found under the Administration menu.

• SQL Workshop - Object Browser and SQL are now part of the SQL Workshop.

• Administration - Other Administration functions pertain mostly to the administration of the workspace, not the database.

B.2.2 Database Management Differences

Once you upgrade Oracle Application Express within your Oracle Database XE, you lose an Oracle Application Express interface that enabled you to manage some database management functions, including the ability to create and alter users, set database parameters, and so. Most of these functions previously existed within the Oracle Database XE interface under Home, Administration.

To perform these functions when using later versions of Oracle Application Express, you must execute an alternate SQL statement when connected to Oracle Database XE in either a SQL Worksheet of SQL Developer or in SQL*Plus. Note that most of these commands must be executed as the user SYS.

The following database management functions that are no longer supported in later versions of Oracle Application Express:

• Change My Password

  • Function: Changes your database user password. Once you upgrade, your database account is associated with an Oracle Application Express account but the passwords are separate. A change to one account does not affect the other.

  • Location: Under Tasks, Database Users.

  • SQL Statement:

    alter user username identified by new_password;
    

  Within SQL Developer, right-click a user under Other Users, select Edit User, and use New Password and Confirm Password.

• 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 support remote connects:

    exec dbms_xdb.setlistenerlocalaccess
    (l_access => FALSE);
    
    

• Usage Monitor

  • Location: LOC.

  • Function: Changes your database user password. Once you upgrade, your database account is associated with your Oracle Application Express account but the passwords are separate. A change to one does not affect the other.

  • SQL Statement:

    To set HTTP Access to Local only:

    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
    
    

    Tip:

    You can access a Workspace Utilization report in Oracle Application Express release 3.0.1 by selecting 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:

    drop user username;
    
    

    Within SQL Developer, right-click a user under Other Users under your XE-SYS connection. Select edit user and select Account is Locked.

• Drop Database User

  • Location: Database Users.

  • SQL Statement:

    drop user username;
    
    

    Within SQL Developer, you can also right-click 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;
    
    

    Within SQL Developer, right-click a user under Other Users under your XE-SYS connection. Select edit user and select Account is Locked.

• Unlock Database Account

  • Location: Database Users.

  • SQL Statement:

    alter user username account unlock;
    

    Within SQL Developer, right-click a user under Other Users under your XE-SYS connection. Select edit user and deselect Account is Locked.