DOWNLOAD Oracle Database 10g Express Edition

TAGS xe, database, 10g, All

Oracle Database 10g Express Edition: Not Just for Learners

Oracle Database XE's value is not necessarily limited to novices, students, hobbyists, or small businesses—it also holds appeal for many other situations.

Published March 2006

Unless you're doing your best to avoid the news, you've probably heard about the production release of Oracle Database 10g Express Edition (XE), a free starter database for DBAs and developers. This release is truly a historic one from Oracle.

Oracle Database XE's value is not necessarily limited to novices, students, hobbyists, or small businesses, however; it also holds appeal for many other situations. Rather, it will bring value to DBAs, developers, and analysts in the course of their everyday jobs, regardless of the size of their business.

Specifically, if you are a DBA and have users who want to play "what-if" scenarios in your database, Oracle Database XE enables a strategy whereby you can push data out to your user, where it does the most good (and your servers the least harm). Deploying that product can also offer a solution to the common problem of users or developers downloading and installing open-source databases, which leaves you with a minefield of maintenance, support, and security headaches.

If you are a developer who yearns to learn "DBA" tasks or just wants an R&D database to try out new things—or if you're simply tired of open-source incompatibility and migration issues when your application needs to scale—Oracle Database XE is your best alternative.

Finally, if you are a business analyst who needs to play "what-if" but can't do what you need in your warehouse or application of choice, Oracle Database XE can augment your spreadsheets and models and give you reliable, secure access to the data you need.

In this article, I will overview some of the obvious as well as less obvious ways Oracle Database XE can add value for these three roles. But before we begin, it's important to understand Oracle Database XE's inherent limitations.

In order to make this release easy to install (e.g., via the standard Windows Installer), configure, and maintain, Oracle has built certain limitations into the product.

The first limitation is memory—Oracle Database XE can address only 1GB of RAM. But when you consider the relative rarity of machines that offer 1GB of memory (especially in small businesses), this limitation should mainly affect how many users can access the database concurrently and, to a certain degree, how well it will perform when those limits are hit. For most purposes Oracle Database XE will be deployed to a single user desktop or small workgroup server, so 1GB is more than enough.

The second limit is that XE will only use one CPU. That does not mean that it won't multi-task or that it can only perform a single function at a time. Rather, XE will run on a computer with more than one CPU, it just won't scale up to use those CPUs. For that functionality, you need to purchase Oracle Database Standard Edition or Enterprise Edition. Again, for the uses discussed here, one CPU is more than enough.

The third limit is that only a single XE database can run on any given computer. The important point here is that you don't need a database for each application you create, as you might for some competing databases. Instead, Oracle uses the concept of schemas to separate applications.

Finally, a 4GB limit is enforced on disk space—which on its face appears to be serious limitation. However, 4GB is a huge amount of storage for most applications. Sure, compared to a multi-terabyte data warehouse, 4GB seems small—but in reality it's hardly that.

Consider the included HR sample schema. The maximum row size of the EMPLOYEES table is 144 bytes. Four gigabytes is 4,294,967,296 bytes; 4GB/144 =29,826,162. That means you could store almost 30 million employee records. That's a lot of employees. Even accounting for JOBS, DEPARTMENTS, and the other sample tables, you would not want to store that much data locally. (That's why you pay for an enterprise license.) Oracle Database XE is designed to store only a useful subset of that data.

As you'll learn in the next section, these limitations can actually work for you, not against you, for everyday DBA tasks.

Consider the example of a data warehouse environment. With the 4GB disk-space limit in such an environment, you won't have to worry about runaway data. You can schedule purges and refreshes (push or pull) for your customers. The fact that the database resides on a user's desktop does not mean it has "left the building."

For that reason, you could use Oracle Database XE to send out aggregated data so that users can have total control over their data and you, in turn, reduce the load on your enterprise hardware. If you can reduce your EE license costs by even a single CPU, you've made your effort worthwhile. (Aggregated data also means that you don't have to worry as much about privacy or compliance issues.)

Oracle Database XE supports materialized views replication but, if you have more than a couple of users running XE, that can be overkill. A better solution would be to create a set of stored procedures on the client-side (in XE). Those procedures would clean up old data and repopulate with the latest and greatest data. Here's an example script:

/* Turn on spooling and write output to the file
test_links.lst for later review */
   spool test_links

/* Connect to the LOCAL XE database as SYSTEM (DBA)  
This user, SYSTEM, has the permissions to execute the 
following grant */
   connect system/&local_system_pwd.@local_xe

/* Grant CREATE TABLE directly to HR 
We need to grant explicitly so that the 
create table command can be used in a 
stored procedure */
   grant create table to hr;

/* Connect to the REMOTE XE Database as HR 
HR is a sample account that comes pre-loaded 
with Oracle Database XE */
   connect hr/&remote_hr_pwd.@remote_xe

/* This select tells us how many records are actually in
the HR.EMPLOYEES table */
   select count(*) from hr.employees;

/* Create a view to restrict data in the remote HR schema
The where clause will reduce the number of records that
a user sees */
   create or replace view hr_employees_vw as
     select * from hr.employees
      where salary < 5000;
	
/* Re-execute the select from above against the new view
and note that there are fewer records in the view 
than there are in the table */
   select count(*) from hr_employees_vw;

/* Connect to the local HR account */
connect hr/&local_hr_pwd.@local_xe

/* Create a database link from the local XE database
to the remote XE database HR account */
   create database link remote_db
     connect to hr
     identified by hr
     using 'remote_xe';

/* Create a procedure to populate a local table
The procedure drops the local HR_LOCAL_EMPLOYEES
table and then recreates it by selecting from
the newly created HR_EMPLOYEES_VW in the remote
XE database using the database link created above */
create or replace procedure pop_hr_data as
begin

  begin
    -- Drop the local table
    execute immediate 'drop table hr_local_employees cascade constraints';
  exception
    when others then
	  null;
  end;
    
  -- Create the table by selecting across the database link
  execute immediate 'create table hr_local_employees as ' ||
                    'select * from hr_employees_vw@remote_db';
					
end;
/

/* Run the procedure to execute the new procedure */
   exec pop_hr_data

/* Verify that the procedure, link and view are working
by executing a select that counts the number of records
in the HR_LOCAL_EMPLOYEES table. This number should be
the same number as the count from
the remote XE restricted view above */
    select count(*) from hr_local_employees;

/* Turn off the spooled output. This will save the file 
so that the script and results can be reviewed */
   spool off

Part of the beauty of this approach is that the refresh can be scheduled; you decide the optimal load times for each user.

By using views on the EE side, you can control the data any particular user can access by giving each user an ID in the warehouse or SE/EE database. When you give them the stored procedure to use for population, create a private database link with their ID. In the future, when they use that link to access the database, it will be with their permissions and roles. This means there is no additional security overhead for the DBA. To remove the user's ability to access the data (when the user moves to a new job or leaves the company), you can lock or remove the user account and your database is secure.

Soon another option for getting data out to the user will be available in the form of Oracle Warehouse Builder's next-generation release (code-named "Paris"). Paris will allow mappings to extract data from your summary tables via generic connectivity (ODBC)—meaning it can target Oracle Database XE or competitive databases equally well. That target can easily be a PC or workgroup server for your customers.

Oracle Database XE also supports external tables. If your system is already importing flat files into your warehouse, or producing flat files for other uses, you can now give your users a script to create an external table. Changing their data is as easy as copying a flat file. End users can keep computed values and other user data in their own tables. The 4GB limit mentioned above applies to user storage in the database, not external tables.

Many smaller applications such as source control, configuration management, bug tracking, and project management use ODBC or JDBC connectivity and ANSI SQL to store data in open source databases. Oracle Database XE supports ANSI standard SQL and both types of connectivity. In some cases, it is only a matter of running some DDL scripts (to create the database objects) and changing connect strings to move those applications to Oracle Database XE. With this release gaining wider popularity, it is likely that XE support will be built into many newer projects.

If you are a developer, have you ever wanted to jump right into a project but had to wait for your development instance to be created? Have you ever wanted to test out an idea but didn't have the necessary permissions? Have you ever wanted to play around with database parameters to see what the effect would be, but were told "That's a DBA's job"? If so, Oracle Database XE is your database.

As you may know from experience, doing the above using an open source database creates problems. No two databases are alike; parameters for most open source databases are completely different from Oracle. Administrative tasks between databases are different as well—if you test an idea on an open source database, there's no guarantee that Oracle will behave similarly. And, worst of all, if you start programming on an open source database, at some point you will want to convert to Oracle and have to deal with migration issues.

These problems go away with Oracle Database XE. If you want to "play DBA," you can. Installation and configuration is minimal so you can concentrate on development, but if you would like to play with configuration, you are in complete control.

With the admin control in Oracle Application Expres (formerly HTML DB), you can create multiple users with varying levels of permissions. Or, use SQL*Plus to create users, roles, and profiles. The amount of time you spend in configuration and other DBA tasks is entirely up to you. Oracle Database XE is ready for application development out of the box.

The reduced size and low administrative overhead means some things were left out, of course. There is no Java support internal to the database. You can connect to an external JVM via JDBC but, as of this writing, there is no internal JVM.

The .Net CLR external process listener is included, however. This listener is similar to the C external listener that has been available since Oracle 8.0. This external process allows .NET programs to be registered with the database so that they can be called from a PL/SQL stored procedure like any other PL/SQL procedure.

.Net Support is available only on the Windows platform and, at this time, is only available for Visual Studio 2003. (Support for VS2005 should be available in early 2006.) However, You will not be able to use the VS Express tools due to limitations Microsoft has built into the VS Express toolset.

All your other development tools will work as expected with XE—including TOAD, JDeveloper, Forms, PHP, and Oracle's new Project Raptor graphical database development tool. You are free to explore, test, and develop for any of your application needs.

As with other editions, Oracle Database XE includes the Application Express Web-based development and deployment tool as well as XML DB. With the latter, you can immediately start using XML, WebDAV, and the built-in HTTP and FTP servers.

(NOTE: As an Oracle developer, you probably have multiple Oracle Homes on your PC. If possible, install XE before any other Oracle tools. If that's not possible, make sure any ORACLE_HOME and TNS_ADMIN variables are commented out before installing. If you set your TNS_ADMIN environment variable, you can maintain a single TNSNAMES.ORA file for all your Oracle Home directories.)

In some cases, Oracle Personal Edition (PE) may be a more appropriate solution for database development. PE requires a license but gives you the full EE set of functionality as well as access to Oracle Metalink support services. If you need to partition tables, secure your application via VPD, or grow a table beyond 4GB, PE may be a better choice than XE.

Most analysts use Excel and Access in lieu of Oracle because that software is so pervasive on enterprise PCs. Those PC applications have some significant downsides. however—stability, security, and the dreaded 64k row limit of Excel, just to name a few.

But what's an analyst to do if they can't create their own tables, mix and match data, or modify that data to see "what-if" scenarios? Thanks to a reasonable disk and memory footprint, easy installation, and complete set of permissions, Oracle Database XE can enable all of the above. If they have an ID in your production database, getting their data into Oracle Database XE is as easy as creating a database link.

Application Express Create Database Link

The most significant feature here, however, is Application Express, the Web-based GUI screen and report builder that was designed with non-programmers in mind. In addition to building screens and reports, you can use Application Express wizards to see your disk usage or create tables, indexes, and other database objects.

Application Express comes with a graphical query builder (for those with limited or no SQL knowledge) and a graphical database browser. The database browser allows you to navigate the objects in your database and view or modify them.

Application Express Query Builder - Conditions

Application Express Query Builder - View SQL

Application Express Query Builder - Results

Application Express Object Browser

Analysts can use the Application Express "Create From Spreadsheet" wizard to create tables and applications from existing spreadsheets. Click a menu option, choose to upload a spreadsheet (or cut and paste it), and you're on your way. The wizard will even give you summary reports built from your spreadsheet. It really is that easy.

Application Express Create Application From Spreadsheet

Output from Application Express can be saved as a CSV file which can be opened by Excel.

Replacing your Access applications requires slightly more technical knowledge than converting your Excel spreadsheets. See this presentation for pointers about migrating Access applications to Application Express.

Oracle Database XE also provides many built-in analytic functions: LAG, LEAD, PERCENTILE_RANK, STDDEV, among others. All these analytic functions can be used in your Application Express applications. OLAP and the SELECT MODEL clause are not available in XE, which is not intended for complex business intelligence applications.

Not Just a Starter Database

Despite the benefits I've described here, Oracle Database XE is not a panacea. If you have major privacy or compliance requirements, your data should reside in a data center where it can be secured and audited. When you need to support large numbers of users, or if XE is running out of available disk space, your database should be managed and maintained by a DBA in a controlled environment.

But for so many other situations—not simply for training or testing—XE is a wonderful new tool. From mom-and-pop to Fortune 500, you're likely to find that XE is the database for your desktop.