Oracle Essentials, 4th Edition

Chapter 1. Introducing Oracle

Where do we start? One of the problems in comprehending a massive product such as the Oracle database is getting a good sense of how the product works without getting lost in the details. This book aims to provide a thorough grounding in the concepts and technologies that form the foundation of Oracle’s Database Server, currently known as Oracle Database 11g. The book is intended for a wide range of Oracle database administrators, developers, and users, from the novice to the experienced. It is our hope that once you have this basic understanding of the product, you’ll be able to connect the dots when using Oracle’s voluminous feature set, documentation, and the many other books and publications that describe the database.

Oracle also offers an Application Server and Fusion Middleware, business intelligence tools, and business applications (the E-Business Suite, PeopleSoft, JD Edwards, Siebel, Hyperion, and Project Fusion). Since this book is focused on the database, we will touch on these as they relate to specific Oracle database topics covered.

This first chapter lays the groundwork for the rest of the book. Of all the chapters, it covers the broadest range of topics. Most of these topics are discussed later in more depth, but some of the basics—for example, the brief history of Oracle and the contents of the different “flavors” of the Oracle database products—are unique to this chapter.

Over the past 30 years, Oracle grew from being one of many vendors that developed and sold a database product to being widely recognized as the database market leader. Although early products were typical of a startup company, the Oracle database quality and depth grew such that its technical capabilities are now often viewed as the most advanced in the industry. With each database release, Oracle has improved the scalability, functionality, and manageability of the database.

This book is now in its fourth edition. This edition, like the second and third editions, required many changes since the database has changed a great deal over this time. Highlights of Oracle releases include:

• Oracle8 (released in 1997) improved the performance and scalability of the database and added the ability to create and store objects in the database.

• Oracle8i (released in 1999) added a new twist to the Oracle database—a combination of enhancements that made the Oracle8i database a focal point in the world of Internet computing.

• Oracle9i (released in 2001) introduced Real Application Clusters as a replacement for Oracle Parallel Server and added many management and data warehousing features.

• Oracle Database 10g (released in 2003) enabled deployment of “grid” computing. A grid is simply a pool of computers and software resources providing resources for applications on an as-needed basis. To support this style of computing, Oracle added the ability to provision CPUs and data. Oracle Database 10g also further reduced the time, cost, and complexity of database management through the introduction of self-managing features such as the Automated Database Diagnostic Monitor, Automated Shared Memory Tuning, Automated Storage Management, and Automated Disk Based Backup and Recovery.

• Oracle Database 11g (released in 2007) is the current release. Many of the self-tuning and managing capabilities are further improved, especially Automatic Memory Management, partitioning, and security. The lifecycle of database change management is extended within Oracle’s Enterprise Manager as Oracle now provides improved diagnosis capabilities and linkage to Oracle Support via a Support Workbench. This version also features improved online patching capabilities.

Before we dive into further details, let’s step back and look at how databases evolved, how we arrived at the relational model, and Oracle’s history. We’ll then take an initial look at Oracle database packaging and key Oracle features today.

The Evolution of the Relational Database

The relational database concept was described first by Dr. Edgar F. Codd in an IBM research publication entitled “System R4 Relational” that was published in 1970. Initially, it was unclear whether any system based on this concept could achieve commercial success. Nevertheless, a company named Software Development Laboratories Relational Software came into being in 1977 and then released a product named Oracle V.2 as the world’s first commercial relational database within a couple of years (also changing its name to Relational Software, Incorporated). By 1985, Oracle could claim more than 1,000 relational database customer sites. Curiously, IBM would not embrace relational technology in a commercial product until the Query Management Facility in 1983.

Why did relational database technology grow to become the de facto database technology? A look back at previous database technology may help to explain this phenomenon.

Database management systems were first defined in the 1960s to provide a common organizational framework for data formerly stored in independent files. In 1964, Charles Bachman of General Electric proposed a network model with data records linked together, forming intersecting sets of data, as shown on the left in Figure 1-1. This work formed the basis of the CODASYL Data Base Task Group. Meanwhile, the North American Aviation’s Space Division and IBM developed a second approach based on a hierarchical model in 1965. In this model, data is represented as tree structures in a hierarchy of records, as shown on the right in Figure 1-1. IBM’s product based on this model was brought to market in 1969 as the Information Management System (IMS). As recently as 1980, almost all database implementations used either the network or hierarchical approach. Although several competitors sold similar technologies around 1980, only IMS could still be found in many large organizations 20 years later.

Figure 1-1. Network model (left) and hierarchical model (right)

Relational Basics

The relational database uses the concept of linked two-dimensional tables consisting of rows and columns, as shown in Figure 1-2. Unlike the hierarchical approach, no predetermined relationship exists between distinct tables. This means that data needed to link together the different areas of the network or hierarchical model need not be defined. Because relational users don’t need to understand the representation of data in storage to retrieve it (and many such users create ad hoc queries), ease of use helped popularize the relational model.

Figure 1-2. Relational model with two tables

Relational programming is nonprocedural and operates on a set of rows at a time. In a master-detail relationship between tables, there can be one or many detail rows for each individual master row, yet the statements used to access, insert, or modify the data simply describe the set of results. In many early relational databases, data access required the use of procedural languages that worked one record at a time. Because of this set orientation, programs access more than one record in a relational database more easily. Relational databases can be used more productively to extract value from large groups of data.

The contents of the rows in Figure 1-2 are sometimes referred to as records. A column within a row is referred to as a field. Tables are stored in a database schema, which is a logical organizational unit within the database. Other logical structures in the schema often include the following:

Views

Provide a single view of data derived from one or more tables or views. The view is an alternative interface to the data, which is stored in the underlying table(s) that make up the view.

Sequences

Provide unique numbers for column values.

Stored procedures

Contain logical modules that can be called from programs.

Synonyms

Provide alternative names for database objects.

Indexes

Provide faster access to table rows.

Database links

Provide links between distributed databases.

The relationships between columns in different tables are typically described through the use of keys, which are implemented through referential integrity constraints and their supporting indexes. For example, in Figure 1-2, you can establish a link between the DEPTNO column in the second table, which is called a foreign key, to the DEPTNO column in the first table, which is referred to as the primary key of that table.

Finally, even if you define many different indexes for a table, you don’t have to understand them or manage the data they contain. Oracle includes a query optimizer (described in Chapter 4) that chooses whether to use indexes, and the best way to use those indexes, to access the data for any particular query.

The relational approach lent itself to the Structured Query Language (SQL). SQL was initially defined over a period of years by IBM Research, but it was Oracle Corporation that first introduced it to the market in 1979. SQL was noteworthy at the time for being the only language needed for relational databases since you could use SQL:

• For queries (using a SELECT statement)

• As a Data Manipulation Language or DML (using INSERT, UPDATE, and DELETE statements)

• As a Data Definition Language or DDL (using CREATE or DROP statements when adding or deleting tables)

• To set privileges for users or groups (using GRANT or REVOKE statements)

Today, SQL contains many extensions and follows ANSI/ISO standards that define its basic syntax.

How Oracle Grew

In 1983, Relational Software Incorporated was renamed Oracle Corporation to avoid confusion with a competitor named Relational Technologies Incorporated. At this time, the developers made a critical decision to create a portable version of Oracle written in C (version 3) that ran not only on Digital VAX/VMS systems, but also on Unix and other platforms. By 1985, Oracle claimed the ability to run on more than 30 platforms. Some of these platforms are historical curiosities today, but others remain in use. (In addition to VMS, early operating systems supported by Oracle included IBM MVS, HP/UX, IBM AIX, and Sun’s Solaris version of Unix.) Oracle was able to leverage and accelerate the growth of minicomputers and Unix servers in the 1980s. Today, Oracle also leverages this portability to operating systems such as Microsoft Windows and Linux.

In addition to multiple platform support, other core Oracle messages from the mid-1980s still ring true today, including complementary software development and decision support (business intelligence) tools, ANSI standard SQL across platforms, and connectivity over standard networks. Since the mid-1980s, the database deployment model has evolved from dedicated database application servers to client/server to Internet computing implemented using browser-based clients accessing database applications.

Oracle introduced many innovative technical features to the database as computing and deployment models changed (from offering the first distributed database to supporting the first Java Virtual Machine in the core database engine to enabling grid computing). Oracle offered support for emerging standards such as XML, important in deploying a Service-Oriented Architecture (SOA). Table 1-1 presents a short list of Oracle’s major product introductions.

Table 1-1. History of Oracle introductions

Year	Feature
1977	Software Development Laboratories founded by Larry Ellison, Bob Miner, Ed Oates
1979	Oracle version 2: first commercially available relational database to use SQL
1983	Oracle version 3: single code base for Oracle across multiple platforms
1984	Oracle version 4: with portable toolset, read consistency
1986	Oracle version 5 generally available: client/server Oracle relational database
1987	CASE and 4GL toolset
1988	Oracle Financial Applications built on relational database
1989	Oracle6 generally available: row-level locking and hot backups
1991	Oracle Parallel Server on massively parallel platforms
1993	Oracle7: with cost-based optimizer
1994	Oracle version 7.1 generally available: parallel operations including query, load, and create index
1996	Universal database with extended SQL via cartridges, thin client, and application server
1997	Oracle8 generally available: object-relational and Very Large Database (VLDB) features
1999	Oracle8i generally available: Java Virtual Machine (JVM) in the database
2000	Oracle9i Application Server generally available: Oracle tools integrated in middle tier
2001	Oracle9i Database Server generally available: Real Application Clusters, OLAP, and data mining in the database
2003	Oracle Database 10g and Oracle Application Server 10g: “grid” computing enabled; Oracle Database 10g automates key management tasks
2005	Oracle completes PeopleSoft acquisition and announces Siebel acquisition, thus growing ERP and CRM applications and business intelligence offerings
2007	Oracle Database 11g: extension of self-managing capabilities and end-to-end database change management; Hyperion acquisition adds database-independent OLAP and Financial Performance Management applications

The Oracle Database Family

Oracle Database 11g is the most recent version of the Oracle Relational Database Management System (RDBMS) family of products that share common source code. The family of database products includes:

Oracle Enterprise Edition

Flagship database product and main topic of this book, aimed at large-scale implementations that require Oracle’s full suite of database features and options. For advanced security, only the Enterprise Edition features Virtual Private Database (VPD) support, Fine-Grained Auditing, and options including the Database Vault, Advanced Security, and Label Security. Data warehousing features only in Enterprise Edition include compression of repeating stored data values, cross-platform transportable tablespaces, Information Lifecycle Management (ILM), materialized views query rewrite, and the Partitioning, OLAP, and Data Mining Options. High-availability features unique to the Enterprise Edition include Data Guard and Flashback database, Flashback table, and Flashback transaction query. Added to Oracle Database 11g are an Advanced Compression Option for all workloads, including transaction processing, Large Object (LOB) storage, and backups; a database testing option called the Real Application Testing Option that includes Database Replay and SQL Performance Analyzer; and a Total Recall Option used to enable a Flashback Data Archive that retains data for historic queries (where a SQL construct specifies an “AS OF” date in the past).

Oracle Standard Edition

Oracle’s database intended for small and medium-sized implementations. This database can be deployed onto server configurations containing up to 4 CPUs on a single system or on a cluster using Real Application Clusters (RAC).

Oracle Standard Edition One

Designed for small implementations. This database can support up to 2 CPUs and does not support RAC. The feature list is otherwise similar to Oracle Standard Edition.

Oracle Personal Edition

Database used by single developers to develop code for implementation on Oracle multiuser databases. It requires a license, unlike Express Edition, but gives you the full Enterprise Edition set of functionality.

Oracle Express Edition

Entry-level database from Oracle available at no charge for Windows and Linux. This database is limited to 1 GB of memory and 4 GB of disk. It provides a subset of the functionality in Standard Edition One, lacking features such as a Java Virtual Machine, server-managed backup and recovery, and Automatic Storage Management. Although this database is not manageable by Oracle Enterprise Manager, you can deploy it for and manage multiple users through the Oracle Application Express (formerly HTML-DB) administration interface.

Oracle generally releases new versions of the flagship database about every three to four years. New releases typically follow themes and introduce a significant number of new features. In recent releases, these themes are indicated in the product version naming. In 1998, Oracle announced Oracle8i, with the "i" added to denote new functionality supporting Internet deployment. Oracle9i continued using this theme. In 2003, Oracle released Oracle Database 10g, with the "g" denoting Oracle’s focus on emerging grid computing deployment models. Oracle has continued that theme in the current database version highlighted in this book. In between major versions, Oracle issues point releases that also add features but are more typically focused on improvements to earlier capabilities.

The terms “Oracle,” “Oracle8,” “Oracle8i,” “Oracle9i,” “Oracle Database 10g,” and “Oracle Database 11g" might appear to be used somewhat interchangeably in this book because Oracle Database 11g includes all the features of previous versions. When we describe a new feature that was first made available specifically in a certain release, we’ve tried to note that fact to avoid confusion, recognizing that many of you maintain older releases of Oracle. We typically use the simple term “Oracle” when describing features that are common to all these releases.

Oracle Development has developed releases using a single source code model for the core family of database products since 1983. While each database implementation includes some operating-system-specific source code at very low levels in order to better leverage specific platforms, the interfaces that users, developers, and administrators deal with for each version are consistent. Since feature behavior is consistent across platforms for implementations of these Oracle flavors, organizations can migrate Oracle applications and databases easily among various hardware platform vendors and operating systems. This development strategy also enables Oracle to focus on implementing new features only once across its product set.

Summary of Oracle Database Features

The Oracle database is a broad product. To give some initial perspective, we begin describing Oracle with a high-level overview of the basic areas of functionality. By the end of this portion of the chapter, you will have orientation points to guide you in exploring the topics in the rest of this book.

To give some structure to the broad spectrum of the Oracle database, we’ve organized our initial discussion of these features into the following sections:

• Database application development features

• Database connection features

• Distributed database features

• Data movement features

• Database performance features

• Database management features

• Database security features

Tip

In this chapter, we’ve included a lot of terminology and rather abbreviated descriptions of features. Oracle is a huge system. Our goal here is to quickly familiarize you with the full range of features in the system. Subsequent chapters will provide additional details. Obviously, though, whole books have been written about some of the feature areas summarized here.

Database Application Development Features

The Oracle database is typically used to store and retrieve data through applications. The features of the Oracle database and related products described in this section are used to create applications. We’ve divided the discussion in the following subsections into database programming and database extensibility options. Later in this chapter, we will describe Oracle’s development tools and Oracle’s other embedded database products that meet unique applications deployment needs.

Database Programming

All flavors of the Oracle database include languages and interfaces that enable programmers to access and manipulate the data in the database. Database programming features usually interest developers who are creating Oracle-based applications to be sold commercially or IT organizations building applications unique to their businesses. Data in Oracle can be accessed using SQL, ODBC, JDBC, SQLJ, OLE DB, ODP.NET, SQL/XML, XQuery, and WebDAV. Programs deployed within the database can be written in PL/SQL and Java.

SQL

The ANSI standard Structured Query Language (SQL) provides basic functions for data manipulation, transaction control, and record retrieval from the database. Most business users of the database interact with Oracle through applications or business intelligence tools that provide interfaces hiding the underlying SQL and its complexity.

PL/SQL

Oracle’s PL/SQL, a procedural language extension to SQL, is commonly used to implement program logic modules for applications. PL/SQL can be used to build stored procedures and triggers, looping controls, conditional statements, and error handling. You can compile and store PL/SQL procedures in the database. You can also execute PL/SQL blocks via SQL*Plus, an interactive tool provided with all versions of Oracle. PL/SQL program units can be precompiled.

Java

Oracle8i introduced the use of Java as a procedural language and a Java Virtual Machine (JVM) in the database (originally called JServer). The JVM includes support for Java stored procedures, methods, triggers, Enterprise JavaBeans? (EJBs), CORBA, IIOP, and HTTP.

The inclusion of Java within the Oracle database allows Java developers to leverage their skills as Oracle applications developers. Java applications can be deployed in the client, Application Server, or database, depending on what is most appropriate. Oracle Database 11g includes a just-in-time Java compiler that is enabled by default. We briefly discuss some aspects of Java development in Chapter 14.

Oracle and web services

As of Oracle Database 11g, the database can serve as a web services provider implemented through XML DB in the database. Web services enable SQL or XQuery to submit queries and receive results as XML, or invoke PL/SQL functions or package functions and to receive results. XQuery in Oracle Database 11g provides support for the emerging JSR-225 standard and includes a number of performance enhancements.

Large objects

Interest in the use of large objects (LOBs) is growing, particularly for the storage of nontraditional datatypes such as images. The Oracle database has been able to store large objects for some time. Oracle8 added the capability to store multiple LOB columns in each table. Oracle Database 10g essentially removed the space limitation on large objects. Oracle Database 11g greatly improved the performance of query and insert operations used with LOBs through the introduction of SecureFiles. Transparent data encryption is supported for SecureFiles LOB data.

Object-oriented programming

Support of object structures has existed since Oracle8i to provide support for an object-oriented approach to programming. For example, programmers can create user-defined datatypes, complete with their own methods and attributes. Oracle’s object support includes a feature called Object Views through which object-oriented programs can make use of relational data already stored in the database. You can also store objects in the database as varying arrays (VARRAYs), nested tables, or index organized tables (IOTs). We discuss the object-oriented features of Oracle further in Chapter 14.

Third-generation languages (3GLs)

Programmers can interact with the Oracle database from C, C++, Java, or COBOL by embedding SQL in those applications. Prior to compiling the applications using a platform’s native compilers, you must run the embedded SQL code through a precompiler. The precompiler replaces SQL statements with library calls the native compiler can accept. Oracle provides support for this capability through optional “programmer” precompilers for C and C++ using Pro*C and for COBOL using Pro*COBOL. In recent Oracle versions, Oracle features SQLJ, a precompiler for Java that replaces SQL statements embedded in Java with calls to a SQLJ runtime library, also written in Java.

Database drivers

All versions of Oracle include database drivers that allow applications to access Oracle via ODBC (the Open DataBase Connectivity standard) or JDBC (the Java DataBase Connectivity open standard). Also available are data providers for OLE-DB and for .NET.

The Oracle Call Interface

If you’re an experienced programmer seeking optimum performance, you may choose to define SQL statements within host-language character strings and then explicitly parse the statements, bind variables for them, and execute them using the Oracle Call Interface (OCI). OCI is a much more detailed interface that requires more programmer time and effort to create and debug. Developing an application that uses OCI can be time-consuming, but the added functionality and incremental performance gains could make spending the extra time worthwhile. In certain programming scenarios, OCI improves application performance or adds functionality. For instance, in high-availability implementations in which multiple systems share disks using Real Application Clusters, you could write programs using OCI that allow users to reattach to a second server transparently if the first fails.

National Language Support

National Language Support (NLS) provides character sets and associated functionality, such as date and numeric formats, for a variety of languages. Oracle Database 11g features Unicode 5.0 support. All data may be stored as Unicode, or select columns may be incrementally stored as Unicode. UTF-8 encoding and UTF-16 encoding provide support for more than 57 languages and 200 character sets. Extensive localization is provided (for example, for data formats), and customized localization can be added through the Oracle Locale Builder. Oracle includes a Globalization Toolkit for creating applications that will be used in multiple languages.

Database Extensibility

The Internet and corporate intranets have created a growing demand for storage and manipulation of nontraditional datatypes within the database. There is a need for extensions to the standard functionality of a database for storing and manipulating image, audio, video, spatial, and time series information. These capabilities are enabled through extensions to standard SQL. For more details regarding these features of Oracle, see Chapter 14.

Oracle Multimedia

Oracle Multimedia (formerly interMedia) provides text manipulation and additional image, audio, video, and locator functions in the database. Oracle Multimedia offers the following major capabilities:

• The text portion of Multimedia (Oracle Text) can identify the gist of a document by searching for themes and key phrases within the document.

• The image portion of Multimedia can store and retrieve images of various formats; starting with Oracle Database 11g, these include DICOM medical images.

• The audio and video portions of Multimedia can store and retrieve audio and video clips, respectively.

• The locator portion of Multimedia can retrieve data that includes spatial coordinate information.

Oracle content management

Oracle’s content management solutions include a Content Database Option used to store and manage documents in the database and Stellent’s content management applications that were acquired by Oracle in 2007. The applications include Universal Content Management, Universal Records Management, and Information Rights Management.

Oracle search capabilities

The Oracle Database and Application Server include a search tool named Ultra Search. Ultra Search is typically used to search and gather location information for text data stored within an organization’s network. Document retrieval is based on user access rights. In addition, Oracle offers an alternative Secure Enterprise Search offering that is more flexible in non-Oracle environments.

Oracle Spatial Option

The Spatial Option is available for Oracle Enterprise Edition. It can optimize the display and retrieval of data linked to coordinates and is used in the development of spatial information systems. Several vendors of Geographic Information Systems (GIS) products now bundle this option and leverage it as their search and retrieval engine.

XML DB

Oracle added native XML datatype support to the Oracle9i database and XML and SQL interchangeability for searching. The structured XML object is held natively in object relational storage meeting the W3C DOM specification. The XPath syntax for searching in SQL is based on the SQLX group specifications, and XQuery is also supported.

Database Connection Features

The connection between the client and the database server is a key component of the overall architecture. The database connection is responsible for supporting all communications between an application and the data it uses. Oracle includes a number of features that establish and tune your database connections, described in the following subsections. We’ve divided the discussion into two categories: database networking and Oracle Application Server.

Database Networking

Database users connect to the database by establishing a network connection. You can also link database servers via network connections. Oracle provides a number of features to establish connections between users and the database and/or between database servers, as described in the following subsections.

Oracle Net

Oracle’s network interface, Oracle Net, was formerly known as Net8 when used in Oracle8, and SQL*Net when used with Oracle7 and previous versions of Oracle. You can use Oracle Net over a wide variety of network protocols, although TCP/IP is by far the most common protocol today. Features associated with Oracle Net, such as shared servers, are referred to as Oracle Net Services.

Oracle Internet Directory

The Oracle Internet Directory (OID) was introduced with Oracle8i. OID replaced Oracle Names used in prior database releases since it gives users a way to connect to an Oracle Server without having a client-side configuration file. OID is an LDAP (Lightweight Directory Access Protocol) directory and so it supports Oracle Net and other LDAP-enabled protocols.

Oracle Connection Manager

Each connection to the database takes up valuable network resources, which can impact the overall performance of a database application. Oracle’s Connection Manager (CMAN), illustrated in Figure 1-3, reduces the number of Oracle Net client network connections to the database through the use of concentrators, which provide connection multiplexing to implement multiple connections over a single network connection. Connection multiplexing provides the greatest benefit when there are a large number of active users.

Figure 1-3. Concentrators with Connection Managers for a large number of users

You can also use the Connection Manager to provide multiprotocol connectivity if you still have some clients and servers not using TCP/IP. Oracle Database 10g introduced dynamic Connection Manager configuration, enabling the changing of CMAN parameters without shutting down the CMAN process.

Oracle Application Server

The popularity of Internet and intranet applications led to a change in deployment from client/server (with fat clients running a significant piece of the application) to a three-tier architecture (with a browser supplying everything needed for a thin client). Oracle Application Server enables deployment of the middle tier in a three-tier solution for web-based applications, component-based applications, and enterprise application integration. Oracle Application Server is a key part of Oracle’s Fusion Middleware and can be scaled across multiple middle-tier servers.

This product includes a web listener based on the popular Apache listener, servlets and JavaServer Pages (JSPs), business logic, and/or data access components. Business logic often is deployed as Enterprise JavaBeans (EJBs). Data access components can include JDBC, SQLJ, and EJBs. TopLink provides a mapping tool that links Java objects to the database via JDBC such that the Java developer need not build SQL calls and or face broken Java applications resulting from database schema changes.

Oracle Application Server offers additional solutions in the cache, portal, business intelligence, and wireless areas:

Cache

Oracle Application Server Web Cache introduced a middle tier for the caching of web pages or portions of pages. An earlier cache, Oracle Application Server Database Cache, was used for caching PL/SQL procedures and anonymous PL/SQL blocks but is no longer supported as of Oracle Application Server 10g.

Portal

Oracle Application Server Portal is also a part of the Oracle Developer Suite (discussed later in this chapter) and is used for building easy-to-use enterprise dashboards. The developed portal is deployed to the Application Server.

Business Intelligence

Application Server Business Intelligence components include the Portal as well as Oracle’s original business intelligence tools:

• Oracle Reports, which provides a scalable middle tier for the reporting of prebuilt query results

• Oracle Discoverer, for ad hoc query and analysis

• A deployment platform for JDeveloper custom-built OLAP and data mining applications

These capabilities are discussed in Chapter 10.

Oracle Wireless

Oracle Wireless (formerly known as Oracle Portal-to-Go) includes:

• Content adapters for transforming content to XML

• Device transformers for transforming XML to device-specific markup languages

• Personalization portals for service personalization of alerts, alert addresses, location marks, and profiles; wireless personalization portal also used for the creation, servicing, testing, and publishing of URL service and for user management

Oracle Application Server is packaged in several editions: Enterprise Edition, Standard Edition, Standard Edition One, and Java Edition, which includes key components for Java developers. Portal, TopLink with the Application Development Framework, and the Web Cache are included in the Standard Edition and in Standard Edition One. The Enterprise Edition adds the following capabilities: Forms Services, Reports Services, Discoverer Viewer, Oracle Internet Directory, Oracle Application Interconnect, Wireless Option, and integration with Enterprise Service Bus (ESB). The Java Edition bundle includes an HTTP Server, OC4J, and TopLink with the Application Development Framework. We provide more details about Oracle Application Server in Chapter 15.

Oracle Application Server Enterprise Edition has several available options including:

BPEL Process Manager Option

Oracle’s Business Process Execution Language (BPEL) tool is designed for Service-Oriented Architecture (SOA) environments and used for creating, managing, and deploying cross-application business processes. It supports standards such as BPEL, Web Services, XML, XSLT, XPATH, JMS, and JCA.

Business Activity Monitoring (BAM)

BAM is used for building real-time dashboards displaying key performance indicators (KPIs) populated with data from alerts monitored via the Web.

BI Publisher

A publishing and report layout tool used in generating high-fidelity reports from XML data.

Service Registry

The Oracle Service Registry enables publishing and advertising of services and provides a System of Record for SOA services.

SOA Suite for Oracle Middleware

The Suite bundles Oracle Fusion Middleware SOA offerings, including BPEL, BAM, business rules engine, Enterprise Service Bus (for messaging, routing, and transformations), Web Services Management (including a policy manager and monitoring dashboard), Web Services Registry, and applications and technology adapters.

Communication and Mobility Server

This bundle includes TimesTen, and also provides a SIP Servlet Container, enabler framework and enablers, voice access, and mobile access.

WebCenter

WebCenter is Oracle’s latest portal framework used for deploying portlets and Ajax-based components, especially in Web 2.0 environments. It includes discussion forums, presence server, instant message client, Wiki, VOIP call setup and teardown, SIP Servlet Container, Java and Web Service APIs, Click-2-dial integration, and voice-enabled soft client.

Fusion Middleware Adapters

Adapters include Applications, Transaction Processing Monitors, EDI, and others.

The Fusion Middleware SOA Suite serves as the basis for Oracle’s Application Integration Architecture (AIA). AIA also includes prepackaged business objects and business processes known as Process Integration Packs and provides key underpinnings used in integrating Oracle’s current and future applications.

Distributed Database Features

The Oracle database is well known for its ability to handle extremely large volumes of data and users. Oracle not only scales through deployment on increasingly powerful single platforms, but also can be deployed in distributed configurations. Oracle deployed on multiple platforms can be combined to act as a single logical distributed database.

This section describes some of the basic ways that Oracle handles database interactions in a distributed database system.

Distributed Queries and Transactions

Data within an organization is often spread among multiple databases for reasons of both capacity and organizational responsibility. Users may want to query this distributed data or update it as if it existed within a single database.

Oracle first introduced distributed databases in response to the requirements for accessing data on multiple platforms in the early 1980s. Distributed queries can retrieve data from multiple databases. Distributed transactions can insert, update, or delete data on distributed databases. Oracle’s two-phase commit mechanism, described in Chapter 13, guarantees that all the database servers that are part of a transaction will either commit or roll back the transaction. Background recovery processes can ensure database consistency in the event of system interruption during distributed transactions. Once the failed system comes back online, the same process will complete the distributed transactions.

Distributed transactions can also be implemented using popular transaction monitors (TPs) that interact with Oracle via XA, an industry-standard (X/Open) interface. Oracle8i added native transaction coordination with the Microsoft Transaction Server (MTS), so you can implement a distributed transaction initiated under the control of MTS through an Oracle database.

Heterogeneous Services

Heterogeneous Services allow non-Oracle data and services to be accessed from an Oracle database through generic connectivity via ODBC and OLE-DB included with the database.

Optional Transparent Gateways use agents specifically tailored for a variety of target systems. Transparent Gateways allow users to submit Oracle SQL statements to a non-Oracle distributed database source and have them automatically translated into the SQL dialect of the non-Oracle source system, which remains transparent to the user. In addition to providing underlying SQL services, Heterogeneous Services provide transaction services utilizing Oracle’s two-phase commit with non-Oracle databases and procedural services that call third-generation language routines on non-Oracle systems. Users interact with the Oracle database as if all objects are stored in the Oracle database, and Heterogeneous Services handle the transparent interaction with the foreign database on the user’s behalf.

Data Movement Features

Moving data from one Oracle database to another is often a requirement when using distributed databases, or when a user wants to implement multiple copies of the same database in multiple locations to reduce network traffic or increase data availability. You can export data and data dictionaries (metadata) from one database and import them into another. Oracle Database 10g introduced a high-speed data pump for the import and export.

Oracle also offers many other advanced features in this category, including transportable tablespaces, Advanced Queuing/Oracle Streams, and extraction, transformation and loading (ETL) solutions. We introduce these next.

Transportable Tablespaces

Transportable tablespaces first appeared in Oracle8i. Instead of using the export/import process, which dumps data and the structures that contain it into an intermediate file for loading, you can place a tablespace in read-only mode, move or copy it from one database to another, and then mount it. The same data dictionary (metadata) describing the tablespace must exist on the source and the target. This feature can save a lot of time since it simplifies the movement of large amounts of data. Starting with Oracle Database 10g, you can move data with transportable tablespaces between heterogeneous platforms or operating systems.

Advanced Queuing and Oracle Streams

Advanced Queuing (AQ), first introduced in Oracle8, provides the means to asynchronously send messages from one Oracle database to another. Because messages are stored in a queue in the database and sent asynchronously when a connection is made, the amount of overhead and network traffic is much lower than it would be using traditional guaranteed delivery through the two-phase commit protocol between source and target. By storing the messages in the database, AQ provides a solution with greater recoverability than other queuing solutions that store messages in filesystems.

Oracle messaging adds the capability to develop and deploy a content-based publish and subscribe solution using a rules engine to determine relevant subscribing applications. As new content is published to a subscriber list, the rules on the list determine which subscribers should receive the content. This approach means that a single list can efficiently serve the needs of different subscriber communities. In the first release of Oracle9i, AQ added XML support and Oracle Internet Directory (OID) integration.

As of the second release of Oracle9i, AQ became part of Oracle Streams. Streams has three major components: log-based replication for data capture, queuing for data staging, and user-defined rules for data consumption. Since Oracle Database 10g, Streams also includes support for change data capture and file transfer solutions. Streams is managed through Oracle Enterprise Manager and described in more detail in Chapter 13.

Extraction, Transformation, and Loading

Oracle Warehouse Builder (OWB) is a tool used in the design of target databases, especially data warehouses, and provides a metadata repository. However, it is best known as a GUI-based tool used in building source-to-target maps and for generating extraction, transformation, and loading (ETL) scripts. OWB leverages key embedded ETL features in the Oracle database first made available in Oracle9i. OWB is included with the Oracle database as of Oracle Database 10g Release 2. We describe it further in Chapter 10.

Optionally, Oracle also offers a data integration tool, Oracle Data Integrator (ODI), that is not as Oracle database-centric as OWB (although the Oracle database can be a source and/or target). Oracle Data Integrator is based on a product and company that Oracle acquired named Sunopsis. In addition to providing ETL capabilities, ODI can generate code as web services for SOA deployment and is a key part of Oracle’s SOA integration strategy.

Database Performance Features

Oracle includes several features specifically designed to boost performance in certain situations. We’ve divided the discussion in the following subsections into two categories: database parallelization and data warehousing.

Database Parallelization

Database tasks implemented in parallel speed up querying, tuning, and maintenance of the database. By breaking up a single task into smaller tasks and assigning each subtask to an independent process, you can dramatically improve the performance of certain types of database operations. Examples of query features implemented in parallel include:

• Table scans

• Nested loops

• Sort merge joins

• GROUP BYs

• NOT IN subqueries (anti-joins)

• User-defined functions

• Index scans

• Select distinct UNION and UNION ALL

• Hash joins

• ORDER BY and aggregation

• Bitmap star joins

• Partition-wise joins

• Stored procedures (PL/SQL, Java, external routines)

In addition to parallel query, many other Oracle features and capabilities are parallelized. Parallel operations are further identified and described in Chapter 7.

Data Warehousing and Business Intelligence

While parallel features improve the overall performance of the Oracle database, Oracle also has particular performance enhancements for business intelligence and data warehousing applications. We introduce many of them here, but see Chapter 10 for more detailed explanations of products and features specific to data warehousing and business intelligence.

Bitmap indexes

Oracle added support for stored bitmap indexes to Oracle 7.3 to provide a fast way of selecting and retrieving certain types of data. Bitmap indexes typically work best for columns that have few different values relative to the overall number of rows in a table.

Rather than storing the actual value, a bitmap index uses an individual bit for each potential value with the bit either “on” (set to 1) to indicate that the row contains the value or “off” (set to 0) to indicate that the row does not contain the value. Bitmap indexes are described in more detail in Chapter 4.

Star query optimization

Typical data warehousing queries occur against a large fact table with foreign keys to much smaller dimension tables. Oracle added an optimization for this type of star query in Oracle 7.3. Performance gains are realized through the use of Cartesian product joins of dimension tables with a single join back to the large fact table. Oracle8 introduced a further mechanism called a parallel bitmap star join, which uses bitmap indexes on the foreign keys to the dimension tables to speed star joins involving a large number of dimension tables.

Materialized views

Since Oracle8i, materialized views have provided another means of achieving a significant speedup of query performance. Summary-level information derived from a fact table and grouped along dimension values is stored as a materialized view. Queries that can use this view are directed to the view, transparently to the user and the SQL they submit. Oracle has continued to improve optimizer usage of materialized views with each new release of the database.

Analytic functions

A growing trend in Oracle and other databases is inclusion of SQL-accessible analytic and statistical functions in the database. Oracle first introduced such capabilities in Oracle8i with the CUBE and ROLLUP functions. Today, the functionality provided also includes ranking functions, windowing aggregate functions, lag and lead functions, reporting aggregate functions, statistical aggregates, linear regression, descriptive statistics, correlations, crosstabs, hypothesis testing, distribution fitting, and Pareto analysis.

OLAP Option

The OLAP Option physically stores dimensionally aware cubes in the Oracle relational database. These cubes are most frequently accessed using SQL, although a Java API is also supported. As of Oracle Database 11g, Oracle’s optimizer recognizes the levels within these cubes. As a result, any business intelligence tool that submits SQL to an Oracle database can transparently take advantage of the improved performance offered by deployment of this option. Refreshes of the values in these cubes are now maintained similar to refreshing materialized views.

Data Mining Option

Since Oracle9i, popular data-mining algorithms have been embedded in the database through the Data Mining Option and are exposed through a PL/SQL or Java data-mining API. Data-mining applications that use these algorithms are typically built using Oracle’s DataMiner or using data-mining tools from Oracle partners such as InforSense and SPSS. Algorithms available in the Data Mining Option for Oracle Database 11g include Naïve Bayes, Associations, Adaptive Bayes Networks, Clustering, Support Vector Machines (SVM), Nonnegative Matrix Factorization (NMF), Decision Trees, and Generalized Linear Models.

Business intelligence tools

Oracle data warehouses are often accessed using business intelligence tools from other popular vendors. However, Oracle’s own tools became more common for such deployment as Oracle grew its offerings through acquisitions. Oracle’s initial offering included Oracle Discoverer and Reports, and these tools remain available in the Application Server or as a standalone Oracle Business Intelligence Standard Edition Suite.

Oracle’s flagship product in this area is Oracle Business Intelligence Enterprise Edition Suite (OBI EE) originally consisting of former Siebel Analytics, including Oracle Answers, Dashboards, Delivers, BI Publisher, and Office Plug-ins. Oracle expanded this offering in OBI EE Plus adding Hyperion components that include Foundation Services, Interactive Reporting, SQR production reporting, Financial Reporting, SmartView for Office, and Web Analysis.

Essbase is available as an option for providing an OLAP cube and functionality independently of the data warehouse database. A subset OBI EE is included in Business Intelligence Standard Edition One, along with the Oracle Standard Edition One database and Oracle Warehouse Builder.

Oracle also offers business intelligence applications that include data models and reporting and analysis with prepopulated business metadata. Flagship applications include Oracle’s Business Intelligence Applications (the former Siebel Business Analytics applications) and Hyperion Financial Performance Management applications.

Database Management Features

Oracle includes many features that make the database easier to manage. Ease in Oracle management fundamentally improved with the introduction of Oracle Database 10g, and has continued to evolve toward being more self-tuning and self-managing with the release of Oracle Database 11g. If you are still managing Oracle databases using techniques (such as scripts) from previous releases and are moving to one of the newer releases, now is the time to reevaluate your thinking on management.

Starting with Oracle Database 10g, statistics are automatically gathered to an Automatic Workload Repository (AWR) within the database. Oracle’s Automatic Database Diagnostic Monitor (ADDM) evaluates the statistics on a regular basis and sends alerts of potential problem conditions to Oracle Enterprise Manager, where you can evaluate the condition in more detail and potentially take corrective actions. Some of the newer fully automated features, such as Automatic Memory Management, also leverage data gathered in the AWR.

Oracle has a near real-time view of current database conditions as it makes automated recommendations. Such recommendations will often be more accurate than would be possible with the manual processes you might have used in the past. In the following subsections we’ll introduce the impact this has on Oracle Enterprise Manager and add-on packs, Information Lifecycle Management, backup and recovery, and database availability.

Oracle Enterprise Manager

Oracle includes Oracle Enterprise Manager (EM) with its most widely deployed database products. EM provides a database management tool framework and an HTML-based interface used to manage database users, instances, and features. EM can also manage Oracle Application Server, Oracle Applications, Oracle’s Linux release, and software products from other vendors.

The database console in Oracle’s current version provides information on database status, availability, schema, data movement configuration, and software maintenance. New with Oracle Database 11g is the Support Workbench and diagnosability infrastructure leveraged in reporting problems to Oracle Support. Multiple database administrators can access the EM repository at the same time.

EM can be deployed in several ways: as a central console for monitoring multiple databases leveraging agents, as a “product console” (installed by default with each individual database), or through remote access, also known as “studio mode.” When deployed as a central console, Enterprise Manager is referred to as “Grid Control” and can be used for rapid installation of Oracle software, provisioning, and automated rolling patch updates.

A subset of Enterprise Manager functionality is accessible through Microsoft Pocket PC Internet Explorer on wireless PDAs using EM2Go. EM2Go can monitor the status of the Oracle database and Oracle Application Server.

Information Lifecycle Management and ILM Assistant

Introduced in 2006, Information Lifecycle Management (ILM) provides a means to define classes of data and storage tiers and move the data to the storage tiers that provide the right combination of performance and cost. The ILM Assistant interface for setting up and managing ILM can be downloaded from the Oracle Technology Network at http://otn.oracle.com. For more details, see Chapter 5.

Backup and Recovery

As every database administrator knows, backing up a database is a rather mundane but necessary task. An improper backup makes recovery difficult, if not impossible. Unfortunately, people often realize the extreme importance of this everyday task only after losing business-critical data resulting from a failure of a related system.

The following sections introduce some features used in performing database backup operations. We discuss backup and recovery strategies and options in much greater detail in Chapter 11.

Recovery Manager

Typical kinds of backups include complete database backups (the most common type), tablespace backups, datafile backups, control file backups, and archivelog backups. Oracle8 introduced the Recovery Manager (RMAN) for the server-managed backup and recovery of the database, leveraging a Recovery Catalog stored in the database. RMAN can automatically locate, back up, restore, and recover datafiles, control files, and archived redo logs. Since Oracle9i, RMAN can restart backups and restore and implement recovery window policies when backups expire. Oracle Enterprise Manager provides a GUI-based interface to RMAN. Oracle Enterprise Manager 10g introduced an improved job scheduler that can be used with RMAN for managing automatic backups to disk.

Incremental backup and recovery

RMAN can perform incremental backups of Enterprise Edition databases. Incremental backups will back up only the blocks modified since the last backup of a datafile, tablespace, or database; thus, they’re smaller and faster than complete backups. RMAN can also perform point-in-time recovery, which allows the recovery of data until just prior to an undesirable event (such as the mistaken dropping of a table).

Oracle Secure Backup

Various media-management software vendors leverage Oracle’s RMAN, but starting with Oracle Database 10g, the database also includes an entry-level tape storage management solution of its own known as Oracle Secure Backup XE. Optionally, Oracle offers an enterprise-class backup solution simply named Oracle Secure Backup.

Database Availability

Database availability depends upon the reliability and management of the database, the underlying operating system, and the specific hardware components of the system. Oracle has improved availability by reducing backup and recovery times by:

• Providing online and parallel backup and recovery

• Improving the management of online data through range partitioning

• Leveraging hardware capabilities for improved monitoring and failover

The relevant features are described in the following subsections.

Partitioning option

Oracle introduced partitioning as an option with Oracle8 to provide a higher degree of manageability and availability. You can take individual partitions offline for maintenance while other partitions remain available for user access. In data warehousing implementations, partitioning is sometimes used to implement rolling windows based on date ranges. Other partitioning types include hash partitioning (used to divide data into partitions using a hashing function and providing an even distribution of data) and list partitioning (enabling partitioning of data based on discrete values such as geography). Starting with Oracle Database 11g, interval partitioning can also be used to automatically create new fixed ranges as needed during data insertions.

Many of these partitioning types can be used in combination as “composite” partitions. Examples of composite partitions in Oracle Database 11g include range-range, range-hash, range-list, list-range, list-hash, and list-list.

Data Guard

Oracle first introduced a standby database feature in Oracle 7.3. The standby database provides a copy of the production database to be used if the primary database is lost—for example, in the event of primary site failure or during routine maintenance. Primary and standby databases may be geographically separated. The standby database is created from a copy of the production database and updated through the application of archived redo logs generated by the production database. Data Guard, first introduced in Oracle9i, fully automates this process; previously, you had to manually copy and apply the logs. Agents are deployed on both the production and standby database, and a Data Guard Broker coordinates commands. A single Data Guard command invokes the eight steps required for failover.

In addition to providing physical standby database support, Data Guard can create a logical standby database. In this scenario, Oracle archive logs are transformed into SQL transactions and applied to an open standby database.

Oracle Database 10g introduced several new features, including support for real-time application of redo data, integration with the Flashback database feature, and archivelog compression. Starting with Oracle Database 10g, rolling upgrades are supported. As of Oracle Database 11g, the Active Data Guard Opton enables the standby database to be used for queries, sorting, and reporting even as changes from the production system are being applied.

Fail Safe

The Fail Safe feature provides a higher level of reliability for an Oracle database. Failover is implemented through a second system or node that provides access to data residing on a shared disk when the first system or node fails. Oracle Fail Safe for Windows, in combination with Microsoft Cluster Services, provides a failover solution in the event of a system failure.

Fail Safe is primarily a disaster recovery tool, so some downtime does occur as part of a failover operation. The recommended solution for server availability, since Oracle9i, is Real Application Clusters.

Oracle Real Application Clusters

Real Application Clusters (RAC) replaced the Oracle Parallel Server (OPS) option beginning with Oracle9i. RAC can provide failover support as well as increased scalability on Unix, Linux, and Windows clusters. Key to improved scalability was the introduction of Cache Fusion that greatly minimizes the amount of writing to disk that was formerly used to control data locks. Oracle Database 10g introduced a new level of RAC portability and Oracle support by providing integrated “clusterware” for the supported RAC platforms.

With Real Application Clusters, you can deploy multiple Oracle instances on multiple nodes of a clustered solution or in a grid configuration. RAC coordinates traffic among the systems or nodes, allowing the instances to function as a single database. As a result, the database has proven to scale across dozens of nodes. Since the cluster provides a means by which multiple instances can access the same data, the failure of a single instance will not cause extensive delays while the system recovers. You can simply redirect users to another instance that’s still operating. Applications can leverage the Oracle Call Interface (OCI) to provide failover to a second instance transparently to the user.

Data Guard and RAC

Data Guard and RAC in combination replaced Parallel Fail Safe beginning with Oracle9i. Data Guard provides automated failover with bounded recovery time in conjunction with Oracle Real Application Clusters. In addition, it provides client rerouting from the failed instance to the instance that is available with fast reconnect and automatically captures diagnostic data.

Automated Storage Management

Oracle Database 10g introduced Automated Storage Management (ASM), which provides optimum striping and mirroring of data for performance and availability. Because ASM is managed through Enterprise Manager, the database administrator now can perform this critical management task. The need to coordinate this activity with a system administrator is thus greatly reduced.

Real Application Testing Option

Oracle Database 11g introduced the capability to rerun production workloads and test system changes through the Real Application Testing Option. This database option includes a Database Replay facility and the SQL Performance Analyzer. Database Replay captures production workload information, including concurrency, dependencies, and timing. It transforms the workload capture files into replay files, provides a Replay Client for processing the replay files, and provides the means to report on performance statistics and any errors that might occur. The SQL Performance Analyzer captures a SQL workload to be analyzed, measures the performance before database changes and afterward, and identifies performance changes among SQL statements.

Database Security Features

Oracle includes basic security for managing user access through roles and privileges. These can be managed through Enterprise Manager on a local basis or on a global basis by leveraging Oracle’s enterprise user security, a feature in the Advanced Security Option. We describe Oracle’s database security features in Chapter 6.

Database security features allow you to implement a Virtual Private Database (VPD) using Oracle by creating and attaching policies to database tables, views, or synonyms. These policies are then enforced by placing a predicate WHERE clause on SELECT, INSERT, UPDATE, DELETE, and/or INDEX statements.

Many organizations face the need to meet more stringent compliance requirements for improved data protection, although database usage now can extend beyond organizational boundaries. Oracle has added several options to the database to enable secure deployment in such challenging environments. These options include the Advanced Security Option, Label Security Option, Database Vault, and Audit Vault.

Advanced Security Option

The Advanced Security Option was once known as the Advanced Networking Option (ANO). Key features for enabling a more secure Oracle Net include use of encryption services such as RSA Data Security’s RC4, the U.S. Data Encryption Stanadard (DES), Triple DES, and the Advanced Encryption Standard (AES). Authentication can be through Kerberos, RADIUS, or the Distributed Computing Environment (DCE). Network data integrity checking uses MD5 or SHA-1. Oracle Database 11g added enhanced transparent data encryption and expanded Kerberos authentication leveraging of Oracle’s encryption types.

Label Security Option

Oracle Label Security controls access to data by comparing labels assigned to rows of data with label authorizations granted to users through their privileges. Multiple authorization levels are possible within a single database. Label security authorizations are managed through a Policy Manager. Policies are enforced in the database instead of through views, thus greatly simplifying management of data accessibility and providing a more secure implementation.

Database Vault Option

Oracle Database Vault Option provides fine-grained access control to data for everyone with access to the database, including database administrators. The security administrator can set factors to define access to the database and audit specific dimensions of security. At a more granular level, realms can be defined for limiting access to specific schemas and roles.

Audit Vault Server

Oracle Audit Vault Server monitors database audit tables, redo logs, and operating system audit files for suspicious activities. It can then generate reports or send alerts showing where such unusual activity is occurring.

Oracle Development Tools

Many Oracle tools are available to developers to help them present data and build more sophisticated Oracle database applications. Although this book focuses on the Oracle database, this section briefly describes the main Oracle tools for application development: Oracle JDeveloper, Oracle SQL Developer, and Oracle Developer Suite. The Developer Suite, sometimes referred to as the Oracle Internet Developer Suite, consists of Oracle Forms Developer, Oracle Reports Developer, Oracle Designer, Oracle Discoverer Administrative Edition, and Oracle Portal.

Oracle JDeveloper

Oracle JDeveloper was introduced by Oracle in 1998 to enable the development of basic Java applications without the need to write code. JDeveloper is now available for free and can be downloaded from the Oracle Technology Network. It includes a Data Form wizard, a Beans Express wizard for creating JavaBeans and BeanInfo classes, and a Deployment wizard. JDeveloper includes database development features such as various Oracle drivers, a Connection Editor to hide the JDBC API complexity, database components to bind visual controls, and a SQLJ precompiler for embedding SQL in Java code that you can then use with Oracle. You can also deploy applications developed using JDeveloper to Oracle’s Application Server. Although JDeveloper uses wizards to allow programmers to create Java objects without writing code, the end result is generated Java code.

Oracle SQL Developer

Oracle SQL Developer was introduced in 2006 and can be used to connect to any Oracle database dating back to Oracle9i Release 2. SQL Developer can create connections to Oracle databases, browse database objects, create and modify database objects, query and update data, export data and DDL, import data, process commands, and run and create reports. The product’s tools support the editing, debugging, and running of PL/SQL scripts. In addition, SQL Developer can be pointed at non-Oracle databases to view their database objects and data, and it provides capabilities to begin a migration to an Oracle database.

SQL Developer is available at no charge and can be downloaded from the Oracle Technology Network. Versions are available for Windows, Linux, and Apple Mac OS X. Oracle also hosts a SQL Developer forum at the Oracle Technology Network site.

Oracle Forms Developer

Oracle Forms Developer is a tool for building forms-based applications and charts for deployment as traditional client/server applications or as three-tier browser-based applications via Oracle Application Server. Developer is a fourth-generation language (4GL). With a 4GL, you define applications by defining values for properties, rather than by writing procedural code. Developer supports a wide variety of clients, including traditional client/server and Java-based clients. The Forms Builder includes a built-in JVM for previewing web applications.

Oracle Reports Developer

Oracle Reports Developer provides a development and deployment environment for rapidly building and publishing web-based reports via Reports for Oracle Application Server. Data can be formatted in tables, matrices, group reports, graphs, and combinations. High-quality presentation is possible using the HTML extension Cascading Style Sheets (CSS).

Oracle Designer

Oracle Designer provides a graphical interface for Rapid Application Development (RAD) for the entire database development process—from building the business model to schema design, generation, and deployment. Designs and changes are stored in a multiuser repository. The tool can reverse-engineer existing tables and database schemas for reuse and redesign from Oracle and non-Oracle relational databases.

Designer also includes generators for creating applications for Oracle Developer, HTML clients using Oracle Application Server, and C++. Designer can generate applications and reverse-engineer existing applications or applications that have been modified by developers. This capability enables a process called round-trip engineering, in which a developer uses Designer to generate an application, modifies the generated application, and reverse-engineers the changes back into the Designer repository.

Oracle Discoverer Administration Edition

Oracle Discoverer Administration Edition enables administrators to set up and maintain the Discoverer End User Layer (EUL) for Oracle’s previous generation of business intelligence tools. The purpose of this layer is to shield business analysts using Discoverer as an ad hoc query and analysis tool from SQL complexity. Wizards guide the administrator through the process of building the EUL. In addition, administrators can place limits on resources available to analysts monitored by the Discoverer query governor.

Oracle Portal

Oracle Portal, introduced as WebDB in 1999, provides an HTML-based tool for developing web-enabled applications and content-driven web sites. Portal application systems are developed and deployed in a simple browser environment. Portal includes wizards for developing application components incorporating “servlets” and access to other HTTP web sites. Portals can be designed to be user-customizable and are deployed to the middle-tier Oracle Application Server.

Oracle Portal brought a key enhancement to WebDB, the ability to create and use portlets, which allow a single web page to be divided up into different areas that can independently display information and interact with the user. For example, Oracle Answers, Discoverer, and Reports can be accessed as portlets.

Oracle’s next generation portal framework product, introduced in 2006, and initially made available as an Application Server option is WebCenter.

Embedded Databases

Although Oracle’s database family can be deployed for embedded applications, the footprint and functionality might be more than what you need. Today, Oracle offers other embedded databases including TimesTen, Berkeley DB, and Oracle Database Lite. These database engines have unique code lines in order to provide small footprints and have different intended roles. For this reason, we will describe these briefly in the following subsections but will not explore their capabilities in great detail elsewhere in this book.

Oracle TimesTen

Oracle TimesTen is a relational database that is stored in physical memory and is typically used where very high-performance transaction-processing workloads are present. Access to the TimesTen database is available through SQL, JDBC, JMS, and ODBC. TimesTen databases can be deployed as exclusive or shared and can be created as permanent or temporary.

The database is refreshed by gathering data using TimesTen libraries deployed to applications or by using a Cache Connect option to an Oracle database. Because data is read and updated in memory, average update or read response times are typically measured in the millionths of seconds. The Cache Connect option supports both read and write caching of Oracle database data. Updates can be bidirectional between TimesTen and Oracle.

As is typical for embedded databases, TimesTen requires almost no ongoing administration. Replication is possible from one TimesTen database to another through an option and is, by default, asynchronous.

Oracle Berkeley DB

Oracle Berkeley DB is an extremely small-footprint embedded database engine providing record-level locking. It comes in Java and XML versions. It is designed to be deployed with and run in the same process as your applications. When Berkeley DB is deployed in this manner, no separate database administration is required. Footprints for the database can be as small as 400 KB.

The Java Edition of Berkeley DB supports the Java Transaction API (JTA), J2EE Connector Architecture (JCA), and Java Management Extensions (JMX). The database is a single JAR file that is 820 KB in size and runs in the same Java Virtual Machine (JVM) as the application. A Direct Persistence Layer (DPL) is supported for accessing Java objects.

The XML Edition of Berkeley DB is most commonly used in network-based applications where content is managed. XQuery and XPath are supported.

Both editions can be configured for high availability using replication. Automatic recovery is also supported. Deployment decisions such as these are made by the application designer at application design time.

Oracle Lite

Oracle Lite is a suite of products enabling mobile use of database-centric applications. Key components of Oracle Lite include the Oracle Lite Database, Mobile Development Kit, and Mobile Server (an extension of the Oracle Application Server).

The Oracle Lite Database engine requires a 50KB to 1 MB footprint depending on the platform. Applications written using Mobile SQL, C++, and Java can use the database. ODBC is also supported. Java support includes Java stored procedures and JDBC. The Oracle Lite Database is also designed to be self-tuning and self-administering and is supported on handheld devices running Windows CE, Symbian, Windows, and Linux.

In typical usage of Oracle Lite, the user will link her handheld or mobile device running the Oracle Lite Database to a large-footprint Oracle Database Server. Data is then automatically synchronized between the two systems. The user will then remove the link and work in disconnected mode. After she has performed her tasks, she will relink and resynchronize the data with the Oracle Database Server.

Oracle Lite supports a variety of synchronization capabilities, including the following:

• Bidirectional synchronization between the mobile device and Oracle’s larger footprint databases

• Publish-and-subscribe based models

• Support for protocols such as TCP/IP, HTTP, CDPD, 802.1, and HotSync

You can define priority-based replication of subsets of data. Because data distributed to multiple locations can lead to conflicts—such as which location now has the “true” version of the data—automated conflict and resolution is provided. You can also customize the conflict resolution.

The Mobile Server provides a single platform for publishing, deploying, synchronizing, and managing mobile applications. The web-based control center can be used for controlling access to mobile applications. Oracle’s former “Web-to-Go” product is also part of the Mobile Server and provides centralized wizard-based application development and deployment.