Skip Headers

Oracle9i Database New Features
Release 2 (9.2)

Part Number A96531-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

Oracle9i Database Release 2 (9.2) New Features

The new features in Oracle9i Database release 2 (9.2) deliver the performance, scalability, and availability critical to hosted service software that must be available round the clock to anyone anywhere.

This chapter discusses these features and is organized to introduce new functionality in accordance with the following themes:

Scalability and Performance

Oracle9i Real Application Clusters enables all applications to exploit cluster database availability, scalability, and performance with no application modifications, making it possible to scale the most demanding e-business applications and thus disperse transaction loads.

Key enhancements in Oracle9i include the following:

Partitioning of Applications No Longer Necessary

Applications can treat Oracle9i Real Application Clusters as a single system making it unnecessary to modify or partition applications to achieve near-linear cluster database scalability. The advantage of this is that you can horizontally scale the database tier as usage and demand grow without needing to modify the application itself.

See Also:

For more information on Oracle Real Application Clusters, please see Scalability and Performance.

See Also:

Oracle9i Real Application Clusters Administration.

Fast Split Partitioning

Fast split partitioning takes advantage of those situations in which a split partition results in all rows being moved to a single partition. If all the rows map into a single partition and if the segment attributes of the old partition and the segment attributes of the partition inheriting all the rows match, then the database simply reuses the old segment and adds an empty segment for the other partition. Another benefit of this is that global indexes do not have to be invalidated and, in certain cases, local index partitions corresponding to the new partitions are also usable.

See Also:

Oracle9i Database Administrator's Guide.

Shared Server

The Shared Server Architecture in Oracle9i significantly improves the scalability of applications and any concurrent clients connected to the server. It also allows existing applications to scale without changes to them.

Dynamic Support for Remote and Local Listener Parameters

This feature allows LOCAL_LISTENER and REMOTE_LISTENER initialization parameters to be updated through the ALTER SYSTEM SET command. Any change made to the LOCAL_LISTENER and REMOTE_LISTENER parameters is recognized by PMON and takes effect immediately.

Same SHUTDOWN IMMEDIATE Behavior for Both Shared and Dedicated Servers

Both shared and dedicated servers are not terminated until all the requests that are in the queue for each kind of server either have been responded to by its server or have been committed.

See Also:

Oracle9i Net Services Administrator's Guide.

Itanium 2 on HP-UX and Linux

Itanium 2 is supported on HP-UX and Linux for use with the Oracle9i database.


Oracle9i extends Oracle's lead on the competition in the internet database availability critical for any e-business application.

Key enhancements in Oracle9i include the following:

Enhanced Disaster Recovery Environment

Oracle9i sets a new standard for high availability with the introduction of four powerful new features to protect against downtime, the most disruptive event an e-business can encounter.

Key enhancements to Oracle9i include the following:

Disaster Recovery and Data Protection

Oracle9i offers many new features for disaster recovery. Key areas include the following:

Oracle9i Data Guard SQL Apply

The logical standby database is a logical copy of the production database. Updates to the secondary site are made using SQL statements regenerated from logged changes to the primary database. You can use the logical standby database to run reports, even when changes are being applied (using SQL) from the production site. If there is a disaster that causes the production database to fail, the logical standby database can be activated as the new primary database with minimal disruption and with no data loss. Data Guard makes the tasks of creating, monitoring, and managing standby databases significantly easier. The Data Guard broker supports up to nine standby database in a single configuration, which can be a mixture of both logical and physical standby databases, with simple role transitions.

See Also:

Correcting Errors Using Flashback Query

Flashback Query allows users to generate flashback information within a SQL statement, instead of only within a session. Once the errors are identified, undoing the updates is a straightforward process that can be done without intervention from the database administrator and without database downtime. You can restore deleted rows, old values, or the previous version of the table, and you can select the difference in sets of data between two different times.

See Also:

Oracle9i Application Developer's Guide - Fundamentals

Recovery Manager Enhancements and Changes

Key enhancements to Recovery Manager include the following:

Backup of Server Parameter Files

The BACKUP command can be used to back up the current server parameter file. Recovery Manager automatically backs up the current server parameter file whenever it includes the current control file in a backup set. If the server parameter file is lost, then you can start an instance without a parameter file, and then run RESTORE SPFILE to restore it.

Control File Autobackups After Structural Changes to the Database

If CONFIGURE CONTROLFILE AUTOBACKUP is set to ON (it is OFF by default), then Recovery Manager automatically backs up the control file and server parameter file after structural changes to the database. The target database records the autobackup in the alert log.

Backing Up Archived Logs That Need Backups

You can use NOT BACKED UP integer TIMES clause of the BACKUP ARCHIVELOG command to back up only those logs that have not been backed up at least integer times. When calculating the number of backups for a file, Recovery Manager only considers backups created on the same device type as the current backup. This option is a convenient way to back up archived logs on specified media--for example, you want to keep at least three copies of each log on tape.

Managing Space When Restoring Archived Logs

The MAXSIZE option of the RECOVER... DELETE ARCHIVELOG command limits how much disk space Recovery Manager uses when restoring logs during media recovery.

New V$ Views in Recovery Manager

The V$DATABASE_BLOCK_CORRUPTION view records the corrupt blocks in a file after the most recent backup, backup validation, or copy of the file. The CORRUPTION_TYPE column shows the type of corruption. Run the BLOCKRECOVER command with the CORRUPTION LIST clause to recover all corrupt blocks recorded in the view. After a corrupt block is repaired, the row describing the corruption remains in the view until the next Recovery Manager backup of the affected file. The V$DATABASE_BLOCK_CORRUPTION view has a corresponding recovery catalog view called RC_DATABASE_BLOCK_CORRUPTION.

The V$DATABASE_INCARNATION view lists all incarnations of the database. A new incarnation is created whenever the database is opened with the RESETLOGS option. The V$DATABASE_INCARNATION view has a corresponding recovery catalog view called RC_DATABASE_INCARNATION.

Default Autolocation for Real Application Clusters

Recovery Manager automatically discovers which nodes of an Oracle Real Application Clusters configuration can access the files that you want to back up or restore. Recovery Manager autolocates the following files:

Prior to this release of Oracle9i, you had to manually enable this option with SET AUTOLOCATE, and the option only applied to backup pieces.

Diagnostics for Media Manager Function Calls

You can now query dynamic performance event views to obtain diagnostic data about Recovery Manager calls to the media manager. An event name corresponds to every media management function. These event names can be used to diagnose problems during Recovery Manager backup, restore, and maintenance jobs.

FORCE Option for DELETE Command

Sometimes the status of an object in the Recovery Manager repository does not reflect the status of the object on the media. For example, someone deletes a backup piece with an operating system utility before CROSSCHECK is run. If the object is listed as AVAILABLE or EXPIRED but the reality on the media is otherwise, and if you run the DELETE command on the object, then Recovery Manager does not delete it. You can override this behavior with the FORCE option of the DELETE command.

Deletion of Files Already Backed Up to a Device

You can delete files that have already been backed up a specified number of times to a device. For example, you can delete all archived redo logs that have been backed up at least twice to tape.

DUPLICATE Command Enhancements

The SKIP TABLESPACE option of the DUPLICATE command enables you to exclude a list of tablespaces from the duplicate database. Also, you can specify the UNTIL clause on the DUPLICATE command to recover the duplicate database to a noncurrent time.

Recovery Manager Error Output Improved

The Recovery Manager error output is more compact and more informative. If an Recovery Manager command fails, then the error stack is always followed by RMAN-03002 or RMAN-03009 stating which command failed. If the errors are generated from the target database, then Recovery Manager does not explicitly indicate that they are from the target database; however, if the errors are from the catalog or auxiliary database, then Recovery Manager indicates this fact in a separate message.

Automated Archived Log Files Space Management

Recovery Manager can automate the space management of archived log files, thus simplifying database administration.

See Also:

Oracle9i Recovery Manager User's Guide

New Utility: DBNEWID

Prior to the introduction of the DBNEWID utility, you could manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID). Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem by allowing you to change any of the following:

Legato NetWorker bundled with Oracle9i

In this release, Oracle has bundled a new version of the Legato backup and recovery software for Oracle databases. This is a single version of Legato NetWorker and the NetWorker Module for Oracle client, which has been designed to operate on the same server as your Oracle database. After installing this version of Legato NetWorker, you will receive a license notice each time you backup your database to tape using the Oracle Recovery Manager (RMAN) interface. To eliminate this license notice, you can enter an Authorization Code, which can be obtained directly from Legato without charge. Instructions for obtaining this code are in the displayed license notice.

Legato NetWorker documentation can be obtained directly from Legato. Documentation for this version of NetWorker can be found at:

This site will also contain any product updates for this NetWorker version.


Oracle9i Systems management is simplified and improved by increased self-management and self-tuning capabilities. Also, Oracle9i integrated system management tools create a complete view of all database and host critical processes, making it possible to quickly and completely assesses the overall health of an e-business infrastructure.

Key enhancements in Oracle9i include the following:

Streamlined Operational Database Management

Several new features simplify administration of the Oracle9i database:

Automatic Server Parameter File Backup and Configuration

Recovery Manager can now automatically back up and restore the server parameter File (SPFILE) configuration file, simplifying the backup and restoration processes of the system.

Performance Tuning Intelligent Advisories

The current release of Oracle9i provides a number of built-in intelligent advisories for performance tuning. These advisories are set to go "out-of-the-box", and allow the administrator to simulate a variety of hypothetical scenarios. These advisories use minimal resources and are available though the standard SQL interface.

Actual Operation-Level Query Statistics

Performance tuning in the current release of Oracle9i is simplified with intelligent tuning features that provide actual operation-level query execution statistics rather than estimates. This new information helps database administrators identify what tables, partitions, and indexes are most heavily accessed.

I/O Topology

A complete I/O topology shows a complete mapping of a file to logical volumes and physical devices.

Dynamic Sampling of Optimizer Statistics

At compile time, this feature, if enabled, will dynamically gather statistics if the existing statistics are incomplete or known to be inaccurate. With default settings, this feature addresses missing optimizer statistics, providing for a possible source of poor optimization. With optional settings, this feature addresses additional sources of inaccurate statistics. Dynamic sampling of optimizer statistics may improve performance by improving the quality of the statistics used by the query optimizer.

Locally Managed SYSTEM Tablespace

The SYSTEM tablespace can be locally managed to simplify the database administrator's tasks.

Clone Database in Oracle Enterprise Manager

Included in the Oracle Enterprise Manager Change Management Pack, this feature enables you to clone a subset of a production environment (data and statistics) for development testing

Specifying FORCE LOGGING Mode

The FORCE LOGGING clause of the CREATE DATABASE, CREATE CONTROLFILE, and CREATE TABLESPACE statement enables you to force redo log records to be written even when NOLOGGING has been specified in a DDL statement.

See Also:

Oracle9i Database Administrator's Guide.

Simplified Oracle9i Administration With Oracle Enterprise Manager

This release of Oracle Enterprise Manager includes a number of improvements over earlier versions:

New Feature Support in Oracle Enterprise Manager

Oracle Enterprise Manager provides support for features new to release 2. These include XML DB, Oracle Streams, the Data Guard SQL Apply Database, and database advisories.

New Features in the Database Configuration Assistant

The Database Configuration Assistant now creates a locally managed SYSTEM tablespace, and resumable space allocations and resource management control operations are now supported in Oracle Enterprise Manager.

Production Database Subset Cloning

Oracle Enterprise Manager can be used to clone a subset of the production environment for development testing.


Workflow Manager is available to users through the Oracle Enterprise Manager console.

See Also:

Oracle Enterprise Manager Administrator's Guide.

LogMiner Enhancements

LogMiner release 9.2 has added support for several new features and changed some default behavior as follows:

LONG and LOB Datatype Support for Redo Logs

LONG and LOB datatypes are supported for redo logs generated on a release 9.2 or later Oracle database.

Supplemental Logging Turned Off by Default

Supplemental logging is turned off by default. This is a change from release 1 (9.0.1), in which minimal supplemental logging was turned on by default. In release 9.2, the level of supplemental logging desired must be specified.

Database and Table Supplemental Logging

Database supplemental logging and table supplemental logging are each available, depending on what level of supplemental logging you need. Within table supplemental logging, you have a choice of using conditional or unconditional log groups.

New Formatting Options

Two new options have been added that affect the formatting of returned data. The DBMS_LOGMNR.NO_SQL_DELIMITER option suppresses the semi-colon at the end of SQL_REDO and SQL_UNDO statements. The DBMS_LOGMNR.PRINT_PRETTY_SQL option formats the reconstructed SQL statements so that they are easier to read.


A new option, DBMS_LOGMNR.CONTINUOUS_MINE, directs LogMiner to automatically add and mine redo log files that are archived after the LogMiner session has started.


Use of the DBMS_LOGMNR.NO_DICT_RESET_ONSELECT option is no longer necessary. When data definition language (DDL) tracking is enabled, LogMiner stores old metadata definitions so that a second select operation has all the needed metadata versions.

New Procedure, DBMS_LOGMNR_D.SET_TABLESPACE, in LogMiner

A new procedure, DBMS_LOGMNR_D.SET_TABLESPACE,recreates all LogMiner tables in a tablespace other than the default tablespace, SYSTEM.

See Also:

Oracle9i Database Administrator's Guide

Minimizing the Production Impact of Upgrading

The Oracle9i Database release 2 introduces new features to lessen the time spent upgrading applications and database systems. These new features include the ability to rename CONSTRAINTS and COLUMNS as well as a significant reduction in the recompilation of PL/SQL packages and procedures. The database system also provides for fast loading of wrapped source code, automatically determines to do nothing upon loading an unchanged package, view or synonym, and removes double invalidation and parallel compilation in dependency code.

See Also:

Oracle9i Database Administrator's Guide.

Cluster File Systems

If your platform supports a cluster file system, then you can use it in Real Application Clusters to store datafiles, control files, the SRVM Configuration Repository, and so on. Using a cluster file system simplifies your administrative overhead and it improves disk manageability.

See Also:

Domain Indexes Enhancement

It is now possible to create and rebuild domain indexes and local domain index partitions in parallel.

See Also:

Oracle9i Database Administrator's Guide.

Table Function Enhancement

A table function can now return the generic collection type SYS.AnyDataSet.

See Also:

PL/SQL User's Guide and Reference

Oracle Net Services Enhancements

Oracle9i includes the following new features for Oracle Net Services:

Net Service Alias

A net service alias is an alternative name for a directory naming object in a directory server. A directory server stores net service aliases for any defined net service name or database service. A net service alias entry does not have connect descriptor information. Instead, it only references the location of the object for which it is an alias. When a client requests a directory lookup of a net service alias, the directory determines that the entry is a net service alias and completes the lookup as if it was actually the entry it is referencing.

Global Database Link Support in Directory Server

A global database link is a database link that is centrally stored in an Oracle Names server. A global database link that is the same as the global database name is automatically registered with an Oracle Names server. You can use a global database link to access an object in the database.

Trace Assistant

Oracle Net Services provides a tool called the Trace Assistant to help you understand the information provided in trace files by converting existing lines of trace file text into a more readable paragraph.

See Also:

Oracle9i Net Services Administrator's Guide.

Segment-level Statistics Collection

With the capacity to collect of segment-level statistics in Oracle9i release 2, users can specify that relevant statistics be collected on the segment level to pinpoint hot spots and performance bottlenecks in the system. You can query these statistics by using the V$SEGMENT_STATISTICS to do a SELECT * FROM V$SEGMENT_STATISTICS. You can also query V$SEGSTAT_NAME for statistics properties and V$SETSTAT for additional efficient access to statistics.

See Also:

Oracle9i Database Performance Tuning Guide and Reference.

Real Application Clusters Guard II

Real Application Clusters Guard II supports comprehensive workload management to maintain high availability for Real Application Clusters databases and their applications. Real Application Clusters Guard II transfers application loads based on the concept of service names. Therefore, Real Application Clusters Guard II supports workload management based on service levels as well as applications using database services.

Service names have been adopted for high availability because you do not have to make application changes to implement them. In addition, service names provide location transparency to the database instances that offer the service. Service names enable a single-system image that simplifies the configuration, operation, and recovery of workloads.

See Also:

Oracle9i Real Application Clusters Guard II Concepts, Installation, and Administration on the Real Application Clusters Guard II software CD

Information Integration

An important feature of any database management system is the ability to share information among multiple databases and applications. Traditionally, this has meant that users and applications must pull information from the database. Today, however, new efficiencies and business models require a more comprehensive and automated approach. This approach must automatically determine what information is relevant and share that information with those who need it. It must also be a universal solution that adapts to changing business requirements and minimizes the trade-offs of single purpose solutions. This active sharing of information includes capturing and managing events in the database, including but not limited to data manipulation language (DML) and propagating those events to other databases and applications.

Information sharing is crucial for data and application integration, replication, data warehouse loading, high availability, data protection, and other applications. However, decision makers are often overwhelmed by the variety of options they face when selecting an information sharing solution. Some solutions are targeted for different purposes and appear incompatible with other solutions. As a result, users find that no single solution meets all their needs and fear becoming limited by the solution they have implemented should their needs change. They therefore want a single solution that meets all their information sharing needs.

Oracle Streams

Oracle9i has provided a variety of information sharing features, including Oracle Advanced Queuing, Advanced Replication, Change Data Capture, and Data Guard. These features provide targeted yet overlapping functionality and are based on different underlying technologies. The Oracle9i Database release 2, however, introduces a new information sharing feature, Oracle Streams. Oracle Streams enables entire new classes of applications and, in a single solution, satisfies the data movement, transaction propagation, and event management needs of most users. Furthermore, should needs change, Oracle Streams can be adapted to meet your requirements.

Oracle Streams enables the propagation of data, transactions and events in a data stream, either within a database or from one database to another. The stream routes published information to subscribed destinations. This provides the functionality and flexibility to capture and to manage events and then to share those events with other databases and applications in a manner that exceeds the capabilities of traditional information sharing solutions. Oracle Streams enables you to break the cycle of trading off one solution against another and provides you with the capabilities needed to build and operate distributed enterprises and applications, data warehouses, and high availability solutions. You can utilize all the capabilities of Oracle Streams simultaneously and, if needs change, you can implement a new capability of Oracle Streams without sacrificing existing capabilities.

Oracle Streams provides a set of elements to enable you to control the following:

You can also address specific requirements by specifying the configuration of the elements acting on the stream.

The architecture of Oracle Streams is very flexible. Streams contains the following three basic elements:

Capturing Events

Streams supports capture of events (database changes and application generated events) into a queue in two ways. On the one hand, implicit capture enables the server to capture DML and DDL events at a source database. On the other hand, user enqueued events allow for more flexibility by allowing applications to explicitly generate events and place them in a queue.

A fundamental distinguishing feature of Oracle Streams is support for log-based change capture. Capturing changes directly from the redo log files minimizes system overhead, while log-based capture leverages the fact that changes made to tables are logged to guarantee recoverability in the event of a malfunction or media failure. Oracle9i can read, analyze, and interpret redo information about the history of activity on a database. Oracle9i release 2 can mine the information and deliver change data to the capture process. Users can specify supplemental logging to log additional information into the redo stream--such as primary key columns--to facilitate the delivery of this information. The capture process retrieves change data extracted from the redo log, formats it into a logical change record (LCR), and places it in a queue for further processing. The capture process can intelligently filter LCRs based upon defined rules so that only the specified types of changes to desired objects are captured.

Staging and Propagating Events

Once captured, events are placed in a queue. The queue provides a service to store and manage captured events. Changes to database tables are formatted as logical change records (LCRs), and then stored in a queue until they are applied or propagated to another queue. LCR staging provides a holding area with security, as well as auditing and tracking of LCR data.

Applying Events

Subscribers examine the contents of the queue and determine whether or not they have an interest in an event. A subscriber can be a user application, another queue--usually on another system--or an apply process. The subscriber can optionally evaluate a set of rules to determine whether or not the event meets the criteria listed in the subscription. If so, then the event will be consumed by the subscriber.

If the subscriber is a user application, then the application will dequeue the event from the queue in order to consume the event. If the subscriber is another queue, then the event will be propagated to that queue. If the subscriber is the default apply process, then the event will be dequeued and applied by the apply process.

Oracle Streams includes a flexible apply process that enables a default or custom apply function. A custom apply sends an event to a user-created PL/SQL procedure for processing. This enables data to be transformed when necessary. Support for explicit dequeue allows application developers to use Oracle Streams to notify applications of changes to data while still leveraging the change capture and propagation features of Oracle Streams.

Open Standards

Oracle Streams is an open information sharing solution. Each element supports industry standard languages and standards. Streams supports capture and apply from Oracle to non-Oracle systems. Changes can be applied to a non-Oracle system by means of an Oracle transparent gateway or by generic connectivity, both of which allow an Oracle database to communicate with and apply changes to a non-Oracle data system. Streams also includes an application programming interface to allow non-Oracle data sources to easily submit or receive change records, allowing for heterogeneous data movement in both directions. In addition, events can be sent to and received from other event queuing systems such as MQSeries and TIBCO through the Message Gateway.


The current release of Oracle9i provides many powerful features to share information between databases, users, and applications. In addition to established features like Advanced Queuing, Advanced Replication, and Data Guard, Streams can now be used to satisfy the most demanding information sharing requirements using a common infrastructure. Complex distributed environments will benefit from a single solution to simplify their information sharing solutions. Simple distributed environments will benefit in the knowledge they can expand their environment as their needs change without having to learn and integrate new products. The result of this is that developers and administrators can spend less time wrestling with their tools and more time providing solutions.

See Also:

Oracle9i Streams.

Business Intelligence

Oracle9i release 2 continues to challenge the competition by providing the best platform support for business intelligence in medium to large scale enterprises. Oracle9i technology focuses especially on the challenges raised by the large volume of data and the need for near real time complex analysis in an Internet-enabled environment.

Key enhancements in Oracle9i include the following:

Oracle OLAP Option Enhancements

With the Oracle OLAP (Online Analytical Processing) Option for the database, Oracle9i challenges the traditional view of analytic servers by making an integrated, relational-multidimensional database available that is fully integrated into the Oracle9i relational database system. This technology eliminates the trade-off between manageability on the one hand and performance and analytic power on the other. Furthermore, it reduces the cost of maintaining data, while it retains excellent performance and support for complex analytical queries. Oracle9i is the only database to provide access to both relational and multidimensional data through SQL or an OLAP API. Any OLAP value calculation can be queried by SQL and the Oracle OLAP functionality provides a complete set of analytic functions through a powerful OLAP API, multidimensional engine, and OLAP data manipulation language (DML). Because OLAP technology is part of a single database process, it is characterized by the same scalability and reliability benefit as the Oracle database process, and, because of this integration, it includes support for Real Application Clusters and Data Guard

See Also:

Oracle9i OLAP User's Guide

Scalability for Business Intelligence

As in previous database releases, the Oracle9i Database release 2 provides important new technological advances scalability for business intelligence.

Key enhancements in Oracle9i include the following:

Partitioning Capabilities

Partitioning capabilities in Oracle9i release 2 have been expanded to support composite range-list partitioning. This makes it much easier to perform rolling window operations on a list of partitions by partitioning by a range of values--for example, a month--with a subpartition with a list value. It is also easy to perform data maintenance operations--for example, backups by geographic region by month. Furthermore, list partitioning now supports the concept of a default partition, so that, if a data row does not conform to the designated list of values, then the data row can be placed in a default partition instead of being rejected and generating an error. This means that applications no longer need to contain code to handle exception cases.

In addition, parallel data manipulation language (DML) is now supported on nonpartitioned data tables, greatly enhancing the performance of a large update operation.

Integrated Relational-Multidimensional Database Capabilities

The Oracle9i Database release 2 also provides important new functionality in Online Analytical Processing (OLAP) and Data Mining.

All Data Stored in Oracle Data Files

In Oracle9i, all data--both relational and multidimensional--is stored in Oracle data files. This means that there are no separate multidimensional files to manage and administer. Additionally, a new data compression capability is leveraged by Oracle OLAP for further disk space savings.

Data Mining

Since the Oracle9i Database release 2 is designed to be a full data warehouse platform, you can leverage the database as the scalable data engine for all operations on data warehousing data.

Extended Data Mining Capabilities

Oracle9i provides a data mining engine that enables customers to incorporate accurate, real-time recommendations into their online operations to integrate data mining more tightly with the relational database.

Range-List Partitioning

You can now subpartition range-partitioned tables by list.

Partitioning Enhancements

You can now simplify the syntax for list partitions by using a DEFAULT partition. You can implement SPLIT operations more easily.

Parallel DML on Nonpartitioned Tables

You can now use parallel DML on nonpartitioned tables.

Summary Advisor Enhancements

The Summary Advisor tool and its related DBMS_OLAP package were improved so you can restrict workloads to a specific schema.

Materialized View Enhancements

You can now nest materialized views when the materialized view contains joins and aggregates. Fast refresh is now possible on materialized views containing the UNION ALL operator. Various restrictions were removed, while the number of situations in which materialized views could be effectively used was expanded. In particular, using materialized views in an OLAP environment is improved.

Data Segment Compression

You can compress data segments in heap-organized tables, and a typical example of heap organized tables you should consider for data segment compression is partitioned tables. Data segment compression is also useful for highly redundant data, such as tables with many foreign keys and materialized views created with the ROLLUP clause. You should avoid compression on tables with many updates or DML.

Query Rewrite Enhancements

Text match processing and join equivalence recognition have been improved. Materialized views containing the UNION ALL operator can now use query rewrite

See Also:

Oracle9i Data Warehousing Guide

Security and Directory

Security for Oracle9i release 2 has been enhanced by the addition of the following new features:

SYS Auditing Capability

Heightened user accountability and database security are now possible with the capability to audit all operations done by user SYS (includes all AS SYSDBA and AS SYSOPER connections).

DBA GRANT/REVOKE of Object Privileges

Improved security functionality allows the database administrator to grant or revoke object privileges on the objects of another user.

Password for Users SYS and SYSTEM at Database Creation

Passwords for the users SYS and SYSTEM can now be set using a clause in the CREATE DATABASE statement. This provides for greater database security by eliminating the use of the commonly known default Oracle passwords for these accounts.

See Also:

Oracle9i Security Overview

Simplified User Setup and Administration

Oracle Advanced Security has made enterprise user security easier to use and has simplified enterprise user setup and administration. Password-based enterprise user security reduces the huge overhead of managing not only the users, but also eliminates certificate administration overhead, thereby reducing the time and costs involved in user administration. This release is particularly useful for large user communities accessing multiple applications in a heterogeneous environment using password-base authentication. In addition, applications that use prior versions of Oracle Database client software can take advantage of this feature without code modification or any upgrade activities on the client. Because users and their credentials are stored in the Oracle internet Directory, they enjoy the same level of protection and security as when they were stored in the database.

User Migration Utility

In this release, user administration is further simplified with the user migration utility. It allows administrators to migrate users defined in the database to Oracle Internet Directory. These migrated users are now referred to as "password authenticated enterprise users." The administrators and the end users enjoy the benefits of centralized user management and single password login to the database.

See Also:

Support for Advanced Encryption Standard (AES)

Advanced Encryption Standard (AES) is a new Federal Information Processing Standard (FIPS) that all U.S. government organizations can use to protect sensitive information. With this release, all government agencies and businesses can leverage the strength of AES to protect sensitive information on the network.

Strong Authentication: Support for RADIUS Authorizations

Support for external RADIUS authorizations in addition to the database roles for a RADIUS user connecting to an Oracle database is new in this release.

Public Key Infrastructure: SSL Hardware Acceleration

In this release, the Oracle Advanced Security option allows enterprises to delegate complex public key cryptographic operations to hardware accelerator devices to speed up SSL transactions.

See Also:

Oracle Advanced Security Administrator's Guide

Directory Naming Access Control in Oracle Net Services

Directory naming is a naming method that resolves a database service, net service name, or net service alias to a connect descriptor stored in a central directory server. With Oracle9i, a directory server provides central administration of directory naming objects, reducing the work effort associated with adding or relocating services.

See Also:

Oracle9i Net Services Administrator's Guide

Oracle Label Security: Releasabilities

Oracle9i release 2 Label Security introduces inverse groups to indicate releasability of information. When you add an inverse group to a data label, the data becomes less classified. For example, a user with the inverse groups UK, US cannot access data which only has inverse group UK. Adding US to that data makes it accessible to all users with the inverse groups UK, US.

For data to be accessible to a user, the data releasabilities must dominate the releasabilities assigned to the user. In other words, releasabilities assigned to a data record must contain all the releasabilities assigned to a user.

See Also:

Internet Content Management

Oracle9i allows customers to store, manage and aggregate all types of multimedia content into a single database. Oracle9i also significantly enhances the capabilities of the database to serve as a platform to create, manage, and deliver internet content.

Key enhancements in Oracle9i include the following:

Storage, Management, and Aggregation of Content

Oracle9i supports new formats for image storage and provides new XML searching and processing capabilities.

Database Content Management Platform Improvements

Improvements for content management include the following new features:

Oracle Text Improvements

Improvements to Oracle Text enhance its manageability and scalability with new features to support XML searching

Format Support by interMedia AVI and Improved Image Processing Performance.

New formats supported by interMedia AVI with improved image processing performance

Oracle Spatial Improvements

Improvements to Oracle Spatial performance

New XML Processing Capabilities

New XML processing capabilities within the database with the brand new technology of Oracle XML DB

See Also:

Oracle Spatial

Oracle Spatial allows database users to index and store spatial data and to develop spatial applications.

Key enhancements in Oracle9i include the following:

Validation with Context

You can now validate a geometry or a layer and obtain additional information about the context (the coordinate, edge, or ring) that causes any geometry to be invalid.


The new SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function performs the same checks as the SDO_GEOM.VALIDATE_GEOMETRY function and includes context information if the geometry is not valid.


The new SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT procedure performs the same checks as the SDO_GEOM.VALIDATE_LAYER procedure, but also includes context information about any invalid geometries.


The SDO_GEOM.VALIDATE_GEOMETRY function and the SDO_GEOM.VALIDATE_LAYER procedure are deprecated and will not be supported in future releases of Oracle Spatial. Because of this, you are encouraged to switch to the new interfaces.

SDO_CS.VIEWPORT_TRANSFORM to_srname Parameter Not Supported

The SDO_CS.VIEWPORT_TRANSFORM function format with the to_srname parameter, which was supported in the previous release, is no longer supported. Because of this, you must use the format with the to_srid parameter.

Utility Functions

A new Spatial utility package, SDO_UTIL, is provided, with the following functions.


The SDO_UTIL.EXTRACT function returns the geometry that represents a specified element (and optionally a ring) of the input geometry.


The SDO_UTIL.GETVERTICES function returns the coordinates of the vertices of the input geometry.

Partitioned Spatial Indexes: Splitting and Merging Supported

ALTER TABLE partitioning statements for splitting and merging partitions, which were not supported in the previous release, are now supported.

Partitioned Spatial Indexes: Exchanging Partitions Including Indexes

You can use the ALTER TABLE statement with the EXCHANGE PARTITION...INCLUDING INDEXES clause to exchange a spatial table partition and its index partition with a corresponding table and its index. In the previous release of Oracle Spatial, this operation resulted in an error.

Exchanging Partitions Including Indexes

You can use the ALTER TABLE statement with the EXCHANGE PARTITION...INCLUDING INDEXES clause to exchange a spatial table partition and its index partition with a corresponding table and its index. In the previous release of Oracle Spatial, this operation caused an error to be generated.

Parallel Index Creation and Rebuilding

Spatial indexes can now be created and rebuilt using parallel execution. The { NOPARALLEL | PARALLEL [ integer ] } option is supported for the CREATE INDEX and ALTER INDEX REBUILD statements.

Deferred Modifications to an Index

Modifications to a spatial index can be deferred until after spatial table INSERT, UPDATE, and DELETE operations are finished, and then the index can be synchronized with the table.

See Also:

Oracle Spatial User's Guide and Reference

Application Development

Oracle9i continues to offer the best development platform for e-business and traditional application development.

Key enhancements in Oracle9i include the following:

Oracle Text

Oracle Text, formerly interMedia Text, indexes any document or textual content to provide fast, accurate retrieval of information. Oracle Text uses standard SQL to index, search, and analyze text and documents stored in the Oracle database, files and on the Web. Oracle Text enables you to perform the following tasks:

Oracle Text includes the following new features:

Document Classification

The new CTX_CLS.TRAIN procedure enables you to generate rules for routing documents to different categories.

User Defined Lexer

The user-defined lexer enables you to create lexing solutions for indexing and querying languages not supported by Oracle Text such as Arabic.

Query Templating

CONTAINS and CATSEARCH are no longer limited to their respective CONTEXT and CTXCAT grammars. Query templating enables you to use the CONTEXT grammar and associated operators in CATSEARCH queries and vice-versa.


You can create a CONTEXT index while allowing inserts, updates, and deletes to your base table.

Parallel Indexing Enhancements

Parallel indexing is now supported for non-partitioned tables. You can use parallelism with CREATE INDEX and ALTER INDEX with parameters replace, resume, and sync. You can also run CTX_DDL.SYNC_INDEX and CTX_DDL.OPTIMIZE_INDEX with a parallel degree.

Stem Indexing

Stem indexing enables better performance for stem ($) queries by indexing the stem form in addition to the base form.

Chinese Lexer

New CHINESE_LEXER enables you to index traditional and simplified Chinese text more efficiently.

URIType indexing

You can create CONTEXT indexes on URIType columns.


The CTXXPATH indextype enables you to speed up ExistsNode() queries on XMLType columns.

ORA:CONTAINS Support in ExistsNode()

You can call the CONTAINS function within an ExistsNode() statement without a Text index.

See Also:

Oracle Text Reference

Workspace Manager

Workspace management refers to the ability of the database to hold different versions of the same record--that is, row--in one or more workspaces. Users of the database can then change these versions independently.

Key enhancements in Oracle9i include the following:

DDL Support Related to Version-Enabled Tables

Some data definition language (DDL) operations are supported on version-enabled tables and on indexes and triggers that refer to version-enabled tables, subject to guidelines and restrictions. These new DDL operations include the following:

Replication Support for Workspace Manager

Workspace Manager supports replication of all workspace-related entities--such as workspaces and savepoints, operations--such as CreateWorkspace and MergeWorkspace, and data manipulation language (DML) and DDL operations on version-enabled tables.

Workspace Manager includes the following new procedures for replication:

The new WM_REPLICATION_INFO metadata view contains information about the Workspace Manager replication environment.

Migration Support Procedures

The following new procedures are available for recovering version-enabled tables left in an inconsistent state after a failed upgrade procedure:

Multilevel Referential Integrity Support

Multilevel referential integrity constraints are permitted on version-enabled tables.

See Also:


XML has emerged as a key application technology in a number of areas. From its beginnings, XML's core characteristics of self description and ad-hoc extensibility have provided the flexibility needed to transport messages between various applications as well as loosely couple distributed business processes. XML is also language and platform independent, and as XML support has become standard in browsers, application servers and databases, enterprises are seeking to tie legacy applications to the Web with XML to transform various proprietary file and document exchange templates into XML.

More recently, a new generation of XML standards, such as XML Schema, have enabled an unified data model that can address both structured data and documents. XML Schema is a key innovation in managing both document content data with equal rigor by enabling documents marked up as XML to move into the database.

Because of these developments, XML use is proliferating in different applications. To include native XML support in a database for users who want to integrate the database with their applications that use XML would give them an enormous advantage over those who use other databases. Thus, for users who want to integrate their database with all their applications, the current release of the Oracle9i database includes Oracle XML DB, which is a set of built-in high-performance storage and retrieval technologies geared toward XML. Oracle XML DB fully absorbs the World-Wide-Web Consortium (W3C) XML data model into the Oracle server and provides new standard access methods for navigating and querying XML. It is important, however, to make clear that XML DB is not separate from the Oracle9i database but is instead an evolution of the familiar Oracle database encompassing both SQL and XML data models in a highly interoperable manner that makes the XML support completely native. The result is that with the Oracle9i database, you get the advantages of relational database technology and of XML technology both at once.

Key aspects of Oracle XML DB:

The key aspects of Oracle XML DB are listed as follows:

Benefits of Oracle XML DB

Users manage structured data as tables on the one hand and unstructured data as files or BLOBs on the other. As a result, users must subject their applications to different paradigms for managing different kinds of data. Systems channel application development either down the unstructured path (making document access transparent but table access complex) or down the structured path (making document access complex and table access transparent). XML DB provides a unique ability to store and manage both structured and unstructured data, under a standard W3C XML data model (that is, XML Schema.). XML DB provides complete transparency and interchangeability between the XML and SQL metaphors. You can perform XML operations over table data and SQL operations over XML documents. This opens up the database for a new class of XML-shaped content.

XML DB provides valuable Repository functionality--foldering, access control, FTP and WebDAV protocol support with versioning--which enables applications to retain the file abstraction when manipulating XML data brought into Oracle.

Users face a performance barrier in storing and retrieving complex XML. However, XML DB helps them to overcome this barrier by providing very high performance and scalability for XML operations. This is accomplished by a number of specific optimizations that relate to XML-specific data-caching and memory management, query optimization on XML, special hierarchical indexes on the XML Repository, and so on.

XML DB provides better management of unstructured XML data with the following features:

XML DB enables data and documents from disparate systems to be accessed (for example, through Oracle Gateways and External Tables) and combined into a standard data model. This integrative aspect reduces the complexity of developing applications that must deal with data from different stores

Oracle XML DB Features

Here are the key features of Oracle XML DB and some of the things you can do with them:


XML Schema support:

Create tables and types are automatically given a W3C standard XML Schema extending the normal SQL data definition language (DDL). This means you have a standard data model for all your data, both structured and unstructured, and can use the database to enforce this data model.

XML Schema storage with DOM fidelity:

Use Object-Relational columns, VARRAYs, nested tables, and LOBs to store any element or element subtree in your XML Schema while still maintaining DOM fidelity (DOM retrieved == DOM stored). By DOM fidelity, your programs can manipulate exactly the same XML data that you received and the process of storage does not mix up the order of elements, name spaces, and so forth.


If you choose the CLOB storage that is available with XMLType, you can retain whitespaces in your data.

XML Piecewise Update:

Use XPath to specify individual elements and attributes of your document during updates, without rewriting the entire document. This is more efficient than the alternatives, especially for large XML documents.

XPath Search:

Specify elements to query against through XPath, and then use SQL operators on these elements to combine the best of SQL and XML.

Lazy XML Load:

XMLType provides a virtual DOM; it only loads rows of data as they are requested, discarding previously-referenced sections of the document if memory usage grows too large (through an LRU cache.) You can use this for high scalability when many concurrent users are working with large XML documents.

XML Views:

Create XML views to create permanent aggregations of various XML document fragments or relational tables. This means you can create your own efficient representations of XML.

Java Beans Interface:

XML DB provides a Java Beans Interface for fast access to structured XML data, with extensions that save only those parts that have been modified in memory. This way, you can get static access to XML as well as dynamic (that is, DOM) access.

Schema Caching:

XML DB keeps structural information (such as element tags, datatypes, and storage location) in a special schema cache, to minimize access time and storage costs.

XML Generation

XML Operators:

New operators such as XMLTABLE (to cast a list of nodes returned by XPath into a table) and XMLELEMENT (to create XML elements on the fly) make XML queries and on-the-fly XML generation easy and make the SQL and XML metaphors interoperable.

XSL Transformations for XMLType:

Use an XSLT to transform XML documents with a SQL operator for database-resident, high-performance XSL transformations.

XML Repository

Access Control Lists (ACLs):

Create high-performance access control lists for any XMLType object, and define your own privileges in addition to the system-defined ones for fine-grained security on XMLType.


Enable folders to map resources (XML files) into database structures and enable hierarchy traversal; also, use XMLTypes or views to map rows into URLs (with ALTER TABLE ENABLE FOLDERING), providing access control, modification date tracking, and other metadata management for those rows.

WebDAV and FTP Access:

Access any foldered XMLType row withWebDAV and FTP (Note that XMLType can manage arbitrary binary data as well, including any file format).

SQL Repository Search:

Operators like UNDER_PATH and DEPTH, allow applications to search folders, file metadata like owner and creation date, as well as file contents through SQL, and enable the SQL optimizer to choose the best execution plan.

Hierarchical Index:

XML DB provides a special hierarchical index to speed path name resolution and folder search. Additionally, you can automatically map hierarchical data in relational tables into folders (where the hierarchy is defined by existing relational information, like the current CONNECT BY uses.)

HTTP Access:

Users manipulating XML data in the Oracle server can use the servlet API to process XML by means of Java.

Advantages of Oracle XML DB

In the absence of strong database support for XML, many users have leaned toward file-storage or unstructured storage of XML. If you store XML data in files or CLOBs, you are not exploiting several key capabilities of databases.

  1. Indexing and Search: Real applications need to do queries like "find me all of the product definitions created between March & April 2000," a query that is typically supported by a BTREE index on a date column. This type of query is why most content management servers need to use an RDBMS, since even document metadata requires BTREE indexes. Content management servers have proprietary query APIs to handle this problem, whereas XML DB enables efficient structured search over XML data using standard SQL and XPath.
  2. Updates & Transaction Processing: Today's commercial databases enable fast updates of subparts of a record, with minimal contention between users trying to update. As traditionally document-centric data becomes more structured (through XML), this requirement gains in importance. File- or CLOB- storage cannot provide the granular concurrency control that XML DB does.
  3. Managing Relationships: Data with any structure will typically have some type of foreign key constraint. Currently, XML data stores lack this feature, so you must implement these in application code. XML DB enables you to constrain XML data to XML schemas and thus achieve the control over relationships that structured data has always enjoyed.
  4. Multiple Views of Data: Most enterprise applications need to group data together in different ways for different modules. This is why relational views are necessary -- to allow for these multiple ways to combine data. By allowing views on XML, XML DB enables you to create different logical abstractions on XML.
  5. Performance and Scalability: People expect data storage, retrieval, and query to be fast. Loading a file or CLOB and parsing is much slower than relational data access. XML DB dramatically speeds up XML storage and retrieval.
  6. Ease of Development: Databases are primarily an application platform that provides standard, easy ways to manipulate, transform and modify individual data elements. While XML parsers give read access to XML data in a standard way, they don't provide an easy way to modify individual XML elements and store them. XML DB supports a number of standard ways to store and retrieve data, including XML Schema, XPath, DOM, and Java Beans. However, if the drawbacks of XML file storage are forcing you to break down XML into database tables and columns, there are several advantages of XML you are leaving on the table.
  7. Structure Independence: The open content model of XML cannot be captured easily in the pure tables-and-columns world. XML Schema allows global element declarations (not just scoped to a container), so that you can find a particular data item regardless of where in the XML document it moves to as your application evolves.
  8. Storage Independence: When you use relational design, your client programs need to know where your data is stored, and in what format, what table, and what the relationships are between those tables. XML Schema enables you to write applications without that knowledge, and allow the DBA to map structured data to physical table and column storage.
  9. Ease of Presentation: XML is understood by browsers, many popular desktop authoring tools, and most internet applications, as a native capability. Relational data isn't generally accessible directly from applications, but requires programming. XML DB enables you to store data as XML and pump it out as XML, requiring negligible programming to display XML content stored in your database.
  10. Ease of Interchange: XML is the language business is using to talk to business. If you are forced to store XML into an arbitrary table structure, you are living with some sort of proprietary translation. Whenever you translate a language, information is lost, so interchange suffers. By natively understanding XML and providing DOM fidelity in the storage/retrieval process, XML DB enables clean interchange.


Complementing the XML Database is the Oracle XML Developer Kit, or XDK -- a set of commonly used building blocks or utilities for development and runtime support. The Oracle XML Developer's Kits (XDK) contain the basic building blocks for reading, manipulating, transforming and viewing XML documents. To provide a broad variety of deployment options, the Oracle XDKs are available for Java, JavaBeans, C, C++ and PL/SQL. Oracle XDKs consist of XML Parsers, an XSLT Processor, XML Schema Processor, XML Class Generator, XML Transviewer Java Beans, XML SQL Utility, XSQL Servlet. Release 9.2 XDK brings these components up-to-date with the latest standards

See Also:

Oracle9i XML Database Developer's Guide - Oracle XML DB

Open Server-Side PL/SQL and Java Debugging

The Oracle9i Database provides JSWP-compliant debugging of Java and PL/SQL in the database, with debugging from any JDB-compliant tool--for example, JDeveloper (IASV2). It also provide Unicode 3.1 standard support along with NCHAR types and character semantics supported with object types. BFILEs also support Unicode character sets.

Key enhancements in Oracle9i include the following:

JDK 1.3 Support

The Oracle9i Database Java Virtual Machine has been brought to JDK 1.3.1 compliance. It now has JDBC support for the TIMESTAMP datatype and for statement pooling. It also provides thin JDBC support for PL/SQL index tables, NUMBER conversion routines, and optimized statement execution time. It has implemented all Java methods for NUMBER and DATE and has J2EE 1.3 compliance for Oracle JMS. SQLJ improvements include TIMESTAMP, specific data sources, and offline parsing. Java publishing improvements include the LOADJAVA capability.

Character Length Semantics

Lengths for character types CHAR and VARCHAR2 may be specified as a number of characters, instead of bytes, in object attributes and collections, even if some of the characters consist of multiple bytes.

Like CHAR and VARCHAR2, NCHAR and NVARCHAR2 may also be used as attribute types in objects and collections. These types are always implicitly measured in terms of characters, so no char qualifier is used.

For CHAR and VARCHAR2 attributes whose length is specified without a CHAR qualifier, the default unit of measure is determined by whether the NLS_LENGTH_SEMANTICS initialization parameter is set to CHAR or BYTE.

Type Synonyms

Synonyms can be defined for user-defined types so that a type can be used without having to qualify its name with the name of the schema in which the type was defined.

User-defined Constructors

User-defined constructor functions make possible custom initialization of newly created object instances. They also make it possible to evolve a type without having to update calls to constructors in existing code to accommodate a newly added attribute.

NCHAR Datatypes

Object types now support NCHAR datatypes and character semantics.

Integrated PL/SQL and Java Debugging

Integrated PL/SQL and Java debugging has been designed for all deployment scenarios, with the initial support for JDeveloper and JDB built on top of standard JDWP protocol. This multi-language debugging functionality has the following features:

Object Type Translator (OTT) Tags: Carrying Forward User Added Code

To extend the functionality of OTT generated code, at times programmers may want to add code in the OTT generated file. The way OTT can distinguish between OTT generated code and code added by the user is by looking for some predefined markers (tags). Support for these tags has been added in the Oracle9i database release 2 (9.2) database.

See Also:

Oracle Call Interface Programmer's Guide

Windows Integration

Oracle9i continues to lead as the platform of choice for organizations deploying on the Windows operating system.

Integration Capabilities

Oracle9i release 2 (9.2) supports the following:

These underlying improvements provide performance benefits for ADO, ADO .Net, and OLE DB .Net. With support for both OLE DB .Net and ODBC .Net, Oracle9i release 2 can participate fully in a Microsoft .Net environment while providing the highest possible scalability and availability.

Additionally, the Oracle9i Database provides support with Oracle Fail Safe for the Microsoft Cluster Server environment. Oracle9i release 2 Fail Safe provides support for Data Guard Redo Apply or Data Guard SQL Apply and multi-clusters management.

Oracle9i supports several versions of Microsoft Windows, including Windows XP, Windows 2000 and Windows NT.

See Also: