Oracle Database 10g Special Edition: not only suitable for beginners (Author: Lewis cunnheim)

Source: http://www.oracle.com/technology/global/cn/pub/articles/cunningham-database-xe.html

 

Oracle Database Xe is not necessarily important to beginners, students, amateurs, or small businesses-it is equally attractive in many other cases.

Released in March 2006

Unless you do not read the news at all, you may have heard of the production version of Oracle Database 10g Special Edition (xe)-a free entry-level database suitable for DBA and developers. This version is a truly historical version of oracle.

However, Oracle Database Xe is not necessarily important to beginners, students, amateurs, or small businesses; it is equally attractive in many other cases. It will greatly help DBAs, developers, and analysts in their daily work, regardless of their enterprise scale.

Specifically, if you are a DBA and you want to demonstrate the "Hypothetical Analysis" solution in your database, you can use ORACLE database Xe to create a policy for providing data to users. In this case, Oracle Database Xe will play a role and minimize the impact on the server ). Deploying this product also solves common problems that users or developers who download and install Open-source databases encounter (this problem brings you many maintenance, support, and security problems ).

If you are a developer eager to learn "dba" tasks or who only needs R & D databases to try out some new functions, or if you are suffering from open source code incompatibility and porting problems when your application needs to be scaled, Oracle Database Xe is your best alternative.

Finally, if you are a business analyst who needs to perform "hypothesis analysis" but cannot perform the required operations in the selected data warehouse or application, oracle Database Xe can use workbooks and models so that you can access the required data securely and reliably.

This article will give an overview of some obvious and less obvious ways Oracle Database Xe can help the above three roles. But before starting, you must first understand the inherent restrictions of Oracle Database Xe.

Restrictions

Oracle imposes certain restrictions on this product to make it easy to install (for example, install through a standard Windows Installer), configure, and maintain.

The first limit is memory-Oracle Database Xe only supports 1 gb ram. However, because the number of computers providing 1 GB of memory is relatively small (especially in small enterprises), this limit will mainly affect the number of users who can access the database at the same time, to what extent does the database performance affect when these limits are met. In most cases, the Oracle database Xe will be deployed on a user desktop or a small workgroup server, so there is more than 1 GB of memory.

The second limit is that Xe will only use one CPU. AndNoThis means that it does not execute multiple tasks, nor does it mean that it only executes one function at a time. On the contrary, Xe can run on a computer with multiple CPUs, except that these CPUs are not used. To use multiple CPUs, you must purchase the Oracle database Standard Edition or Enterprise Edition. The CPU usage described in this article is more than enough.

The third restriction is that only one Xe database can be run on any given computer. It is important that you do not have to allocate a database to each created application just like some other databases. Instead, Oracle uses the schema concept to separate applications.

The last limit is that the maximum disk space is 4 GB-it seems to be a harsh limit on the surface. But for most applications, 4 GB is a relatively large storage size. Compared with multi-TB data warehouses, 4 GB does seem a little small, but not in reality.

Take the attached HR example mode as an example. The maximum size of a row in the Employees table is 144 bytes. 4 GB = 4,294,967,296 bytes, 4 GB/144 = 29,826,162. This means that you can store tens of millions of employee records. This is a large number of employees. You do not want to store so much data locally, even if you consider jobs, jobs, and other example tables. (That's why you want to buy a license .) Oracle Database Xe is used to store only the useful subset of the data.

As you will learn in the next section, these restrictions do not actually set any obstacles for you to perform routine DBA tasks.

Aggregation Angle

Take the data warehouse environment as an example. By implementing a 4 GB disk limit in such an environment, you do not have to worry about data overflow. You can develop clear and refresh (push or pull) plans for customers. The database on the user's desktop does not mean it has "left the building ".

Therefore, you can use ORACLE database Xe to send aggregated data so that users can have full control over their data, thus reducing the load on Enterprise hardware. If you can reduce the EE license cost by one CPU, this effort is worth it. (Aggregated data also means you do not have to worry about privacy or compliance issues .)

Oracle Database Xe supports materialized view replication. However, if the number of users running Xe is large, this replication may be counterproductive. A better solution is to create a set of stored procedures (IN Xe) on the client ). These processes clean up old data and refill the latest and most important data. The following is 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

Some of the advantages of this method are that you can create a refresh plan; you can decide the best loading time for each user.

By using the view on the EE side, you can control the data that any specific user can access by providing an ID for each user in the data warehouse or SE/EE database. When you provide users with a stored procedure for filling, use their ID to create a dedicated database link. In the future, when they use this link to access the database, the link will have their permissions and roles. This means that no additional security overhead will be incurred for DBAs. To delete a user's access to data (when the user changes to a new job or leaves the company), you can lock or delete the user account, and your database is still secure.

Another option for providing data to users will soon be available in the form of the next-generation version (codenamed "Paris") of Oracle warehouse builder. Paris will enable ing to extract data from the abstract table through a common connection (ODBC), which means it can provide the same performance for Oracle Database Xe or other databases. The application target can be the customer's PC or workgroup server.

Oracle Database Xe also supports external tables. If the system has imported a flat file to a data warehouse or generated a flat file for other purposes, you can now provide users with a script to create an external table. Changing their data is as easy as copying a flat file. End users can save the calculated values and other user data in their own tables. The above 4 GB limit applies to user storage in the database, not to external tables.

Many small applications (such as source code control, configuration management, Error 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 the connectivity between the above two types. In some cases, you only need to run some DDL scripts (create database objects) and change the connection string to port these applications to the Oracle database Xe. As this version becomes more popular, it is likely to build Xe support into many newer projects.

For developers: free

If you are a developer, do you want to immediately invest in the project, but you have to wait to create a development instance? Did you want to test an idea but do not have the necessary permissions? Do you want to play with database parameters to understand their functions, but you are told that "this is the work of DBA "? If you have encountered these problems, Oracle Database Xe can solve these problems.

A new type of support: Community Oracle performs an upgrade through the MS installer or Linux RPM as an installation. Since no patches will be released, the upgrade process will be significantly simplified. You can run a simple command to perform the upgrade without having to upgrade all your local computers. You and your users can also get support from the same place (Oracle Database Xe forum on OTN. This forum is a mutual help platform, and anyone can register an ID. If you have a valid support contract, Metalink can also provide a large amount of information regarding any issues or questions you encounter. However, no matter what type of support contract you have, you cannot create a technical assistance request (TAR) for the Xe issue ).

Experience tells you that using an open source database to perform the above operations will cause problems. Two identical databases do not exist. The parameters of most open-source databases are completely different from those of oracle. Management tasks between databases are also different-If you test an idea on an open-source database, you cannot guarantee that Oracle will respond the same way. Worst of all, if you start programming on an open-source database, you will need to connect to Oracle at a certain point in time and you must solve the porting problem.

Oracle Database Xe does not have these problems. If necessary, you can "assume the role of DBA ". Installation and configuration work is very small, so that you can focus on development, but if you want to try the configuration, you have full control permissions.

With Oracle Application expres (the previous html db), you can use different permission levels to create multiple users. Alternatively, you can use SQL * Plus to create users, roles, and configuration files. The time spent on configuration and other DBA tasks depends on you. Oracle Database Xe can be used for application development immediately.

Of course, the reduction of the size and management overhead mean that some content will be ignored. The database is no longer supported by Java. You can connect to the external JVM through JDBC, but no internal JVM exists during this article.

However, it contains the. net clr external process listener. This listener is similar to the C external listener launched after Oracle 8.0. The external process supports registering. net programs in the database, so that they can be called from the PL/SQL stored procedure like any other PL/SQL procedure.

. Net can only be provided on Windows platforms and can only be used in Visual Studio 2003. (Support for vs2005 will be available in early 2006 .) However, you cannot use the vs express tool because Microsoft has added restrictions to the vs express tool set.

All your other development tools (including the new project Raptor graph database development tools for Toad, jdeveloper, forms, PHP, and Oracle) will work in Xe as expected. You can study, test, and develop any application as needed.

Like other versions, Oracle Database Xe includes Application Express web-based development and deployment tools and xml db. With the latter, you can immediately start using XML, WebDAV, and built-in HTTP and FTP servers.

(Note: As an Oracle Developer, your PC may have multiple Oracle Home. If possible, install Xe before installing any other oracle tools. Otherwise, make sure to comment out any ORACLE_HOME and tns_admin variables before installation. If you set the tns_admin environment variable, you can maintain a tnsnames. ora file for all Oracle Home Directories .)

In some cases, the Oracle Personal Edition (PE) may be a more suitable solution for database development. Although PE requires a license, it provides you with a complete EE feature set and Oracle Metalink Support Service. If you want to partition a table, use VPD to protect applications, or expand the table to 4 GB or more, PE is a better choice than Xe.

All day round, what is the assumption analysis?

Most analysts use Excel and access to replace oracle because the software is widely used in enterprise PCs. These PC applications have some obvious disadvantages, such as reliability, security, and Excel's demanding 64 K row restrictions.

But what should analysts do if they cannot create their own tables, mix and match data, or modify the data to view the "Hypothetical Analysis" scenario? Oracle Database Xe can perform all the preceding operations because it provides a reasonable disk and memory size, a simple installation process, and a complete permission set. If your production database contains a user ID, you only need to create a database link to place the data in Oracle Database Xe.

The most important feature here is Application Express, a web-based GUI screen and report builder that takes into account non-programmers. In addition to building screens and reports, you can also use the Application Express Wizard to view disk usage or create tables, indexes, and other database objects.

Application Express provides a graph Query Builder (applicable to users who do not know SQL or have limited knowledge of it) and a graph database browser. The Database Browser allows you to navigate to and view or modify objects in the database.

Analysts can use the Application Express "create from spreadsheet" Wizard to create tables and applications from an existing workbook. Click a menu option and Select Upload Workbook (or cut and paste it) to create a workbook. This wizard even provides you with abstract reports built on workbooks. It is indeed that simple.

You can save the Application Express output as a CSV file that can be opened by Excel.

Compared with converting an Excel worksheet, replacing an access application requires a higher level of technical knowledge. For tips on porting an access application to Application Express, see this presentation.

Oracle Database Xe also provides many built-in analysis functions: lag, lead, percentile_rank, and stddev. All these analysis functions can be used in the Application Express Application. Xe is not applicable to complex intelligent applications. Therefore, it does not support OLAP and select model clauses.

Not just an entry-level database

Although it has all the advantages described in this article, Oracle Database Xe is not a 10 thousand database. If you have important privacy or compliance requirements, you should store the data in the data center, where data can be protected and audited. If you need to provide support to a large number of users and the available disk space of Xe is insufficient, DBA should manage and maintain the database in a controlled environment.

But for many other cases (not just for training or testing), Xe is a great new tool. From small businesses to the world's top 500 companies, you may find that Xe is a database suitable for desktop applications.