1 Oracle Database 10g New Features

This chapter contains descriptions of all of the features that are new to Oracle Database 10g Release 2 (10.2). This chapter contains the following sections:

1.1 Application Development

The following sections describe Application Development features for Oracle Database 10g Release 2.

1.1.1 Application Program Interface (API) and Precompilers

These features provide API and functionality improvements to enable ISVs and developers to build more powerful applications with higher levels of availability. Oracle Open Database Connectivity (ODBC) Driver Support for Linux

Support has been added for an Oracle ODBC driver for the Linux platform. Now, ODBC applications can be developed and used on Linux with an Oracle supported ODBC driver.

This support enables ODBC applications to run on the Linux platform, encouraging wider Oracle Database adoption by allowing Linux clients to access Oracle databases. Oracle Call Interface (OCI) Client Version API

This feature adds client versioning macros to support version sensing.

This allows applications to be compiled for multiple versions of the Oracle client from a single source code environment. This leads to easier development of products with customizations based on versions. Start Up and Shut Down of Database OCI API

This feature adds API calls for starting and stopping a database from inside an application.

This enables ISVs and testers to start up and shut down databases without using SQL*Plus or other tools. This streamlines the testing process for application developers. Oracle C++ Call Interface (OCCI) Transparent Application Failover Support

This feature adds extensions to OCCI API to support Transparent Application Failover (TAF).

This enables more robustness with TAF. If a server node goes down, applications automatically reconnect to another surviving node, replaying transactions if possible. Instant Client Light

Instant Client Light reduces the size of the Instant Client package by removing most of the character sets and leaving only English error messages.

This feature allows Instant Client to be used in a wider variety of environments, especially where storage space constraints are prominent. Instant Client Software Development Kit (SDK)

The Instant Client SDK packages the OCI/OCCI header files necessary for developing Oracle client applications outside of an Oracle installation. Now, application developers can develop, test, and deploy Oracle client applications from an Instant Client deployment without the need for an ORACLE_HOME or full client installation.

This feature enables a larger development community to develop client applications for Oracle, encouraging more Oracle Database adoption.

1.1.2 Globalization and Unicode Improvements

Globalization and Unicode support have been enhanced in Oracle Database 10g Release 2. Consistent linguistic ordering support has been added for all SQL functions, enhancing the ability to write internationalized applications without regard to locale. The Globalization Development Kit (GDK) for PL/SQL provides new locale mapping tables and Japanese transliteration support. SQL Regular Expression support has been updated to support key Perl conventions used in the Life Science community.

This release also supports the latest version of Unicode 4.0. Identification of potential problems when migrating to another database character set has also been enhanced. The Character Set Scanner (CSSCAN) now offers selective scanning and provides support for new data types. The Language and Character Set Scanner (LCSSCAN) has improved detection quality of shorter text strings and now supports HTML files. Unicode 4.0

Unicode 4.0 is the latest release of the Unicode standard. Oracle's Unicode character sets, AL32UTF8 and AL16UTF16, have been updated to support Unicode 4.0 in Oracle Database 10g Release 2. Unicode 4.0 has 1,226 new characters.

This feature provides greater support of the Unicode character set for Oracle Database users. Unicode Data Type Literal Enhancement

This feature supports SQL string literal INSERT and UPDATE operations on NCHAR without data loss regardless of what the database character set happens to be. Multilingual Unicode data can now be added without restrictions such as having to provide hex Unicode values. The support for this feature is available in SQL, PL/SQL, OCI, and JDBC.

This feature prevents data loss when performing INSERT and UPDATE operations on NCHAR. Globalization Development Kit (GDK) 2.0

Globalization Development Kit (GDK) has added new locales and common locale mapping information into the GDK for the PL/SQL package.

Among other things, the mappings allow for picking the most appropriate Oracle Linguistic sort when ordering different users' language data.

Additional mapping functions have been added to allow developers to build global PL/SQL applications including Common Sort, Common Time Zone, Common Territory, and Translated Locale Names. Character Set Scanner (CSSCAN) Enhancements

Two new parameters, QUERY and COLUMN, have been added in CSSCAN in Oracle Database 10g Release 2. These parameters offer finer control in performing selective analysis on the user data. Support for some remaining data types that CSSCAN does not handle has been added. They are multilevel VARRAY and multilevel nested tables.

The enhancements in CSSCAN provide more flexibility in defining the criteria for data scanning, which in turn helps to more accurately detect the effect of migrating the database to a new character set. Language and Character Set File Scanner (LCSSCAN) Enhancements

In this release, enhancements include:

  • Improved guessing for shorter length strings

  • Support for HTML files

Oracle's Language and Character Set Detection (LCSD) is a high-performance, statistically-based technology for determining the language and the character set for unknown text in a file. It can automatically identify a wide variety of language and character set pairs. Introduced in Oracle Database 10g Release 1, LCSD is offered as part of the Migration Utilities and called the Language and Character Set File Scanner (LCSSCAN). LCSD is also offered as part of the Globalization Development Kit through Java API's. Consistent Linguistic Searching and Sorting

A new NLS_COMP parameter, LINGUISTIC, has been added to support the existing and remaining SQL & PL/SQL operators and functions.

This feature ensures all SQL string comparisons are consistent, and that they follow the linguistic convention as specified in the NLS_SORT parameter. Transliteration for Japanese Kana Conversion

This feature provides a transliteration mechanism to support various Japanese Kana conversions in different forms. The TRANSLITERATE function for the UTL_I18N PL/SQL package is provided for transliteration in this release.

This feature performs Japanese Kana conversions.

1.1.3 Java, Java Database Connectivity (JDBC), and Web Services

Oracle Database 10g Release 1 brought new major application development and deployment capabilities to Java and Web Services developers. Oracle Database 10g Release 2 goes further in terms of JDBC standards support, dynamic load balancing of connections in Real Application Clusters (RAC) and GRID environments, and many performance improvements such as caching XA connections, and DML in the RETURNING clause. JDBC XA Connections Caching

This feature improves JDBC support for caching XAConnections on OracleXADataSources. Applications can now enable Implicit Connection Caching and Fast Connection Caching (in Real Application Clusters environments) on OracleXADataSources. When XAConnection caching is not enabled, applications obtain XAConnections directly from OracleXADataSource, and then obtain the associated XAResource objects.

This feature improves the performance of distributed transactions through the caching of XA connections in a single instance or multiple instance database environments. JDBC Runtime Connection Load Balancing

This feature provides dynamic routing of connection requests to the least loaded instance. The JDBC connection cache manager uses Real Application Clusters (RAC) workload metrics and the specified load balancing policy to choose the best instance to process the connection request.

The benefit is efficient RAC and Grid database resource utilization by even distribution of the workload across RAC instances based on workload metrics and distribution policies. JDBC Support for Data Manipulation Language (DML) in the RETURNING Clause

This feature enables you to issue a DML command (for example, INSERT, UPDATE, or DELETE) specified in the RETURNING clause upon a SQL query execution.

This feature increases performance by saving a client-to-database round-trip. Support for JDBC 3.0 ResultSet Holdability

By default, the database automatically closes cursors and result sets upon transaction commit. A holdable cursor, or holdable resultset, is not closed when the transaction that contains the cursor is committed thereby reducing hard parses and library latches. JDBC 3.0 ResultSet holdability is specified using the createStatement(), prepareStatement(), or prepareCall() methods and one of the following two constants: HOLD_CURSORS_OVER_COMMIT or CLOSE_CURSORS_AT_COMMIT. Oracle Database supports HOLD_CURSORS_OVER_COMMIT. Any other value passed as an argument is ignored.

Keeping cursors open when a transaction is committed increases the performance of JDBC applications or J2EE components. Support for JDBC 3.0 Retrieval of Auto-Generated Key

During a SQL INSERT command, this feature retrieves the value of the pseudo-column, ROWID, of the newly inserted row.

This feature allows JDBC applications and J2EE components to retrieve the key (ROWID) of the newly inserted row during the same database operation (INSERT). As a result, a network round-trip to the database is saved and the performance is increased.

1.1.4 Oracle HTML DB Improvements

In this release, the Oracle HTML DB user interface has been enhanced and made more consistent to simplify development and deployment. Additional enhancements include:

  • The Create Application wizard has been simplified and the generated application made more feature rich.

  • The Chart wizard now provides additional chart types, including stacked bar, cluster bar, and dial charts.

  • Incorporating web services into your HTML DB application has been made easier through support for UDDI and additional wizards.

  • More than ten complete new themes have been added to the built-in themes repository.

  • A new Form wizard enables creation of a master detail form without coding.

  • Loading of text and spreadsheet data has been enhanced to support larger data sets and loading performance has been improved.

  • Tabular forms can now be easily extended to support selecting of rows with check boxes and adding of blank rows. Cluster Bar Charts

SVG charts now include Cluster Bar charts where bars are grouped into columns of data allowing for easy comparison.

The new Cluster Bar charts provide a method of visualizing data in a side-by-side comparison series. Dial Charts

SVG charts now include a dial or gauge chart, allowing you to plot a single value relative to a range defined by a minimum and maximum value represented by a needle on a semicircular surface.

The new Dial chart provides a method of visualizing data commonly used in executive dashboards. Percentage Bar Charts

SVG charts now include a Percent Bar chart used to show cumulative percentages.

Percent Bar charts provide a method of visualizing data that was previously unavailable in Oracle HTML DB. Stacked Bar Charts

SVG charts now include Stacked Bar charts for showing multiple series of absolute values combined into single bars. They are useful when visualizing accumulations or cumulative data.

The addition of Stacked Bar charts allow visualizing of cumulative data in bar charts that was previously not possible. Asynchronous Refresh for All Charts Based on Scalable Vector Graphics (SVG)

SVG charts can now be configured to refresh at an interval defined by the developer. The refresh causes the chart to rerun the query upon which its data set is based.

Every SVG chart used in an HTML DB application can now automatically refresh itself at regular intervals based on the most up-to-date data. Create Master Detail With Choice of Layout

The Master Detail wizard creates a master detail mini-application divided into either 2 or 3 pages with the following features:

  • Lost update detection

  • Developer-defined navigation through master rows with Next and Previous buttons

  • Editing of detail rows on a separate page or on a combined master detail form page

  • Single click adding of detail rows in tabular form

The Master Detail wizard automates the manual steps previously required to build a master detail form. Application From a Spreadsheet Wizard

The Create Application wizard has been enhanced in two ways. First, it now enables you to create an application on spreadsheet data. Previously, importing of spreadsheet data had to be done in a separate step using the Data Workshop. This task has now been integrated into the wizard. Secondly, the generated application both on imported spreadsheet data and on an existing table has more features. Those features include:

  • Optional read-only version of the generated application

  • Record-by-record navigation

  • More analysis screens with summary reports and charts

The enhanced Create Application wizard simplifies the process of creating an application based on spreadsheet data and generates a more feature rich application. Create Forms and Reports Based on a Web Service Reference

With the enhanced Web Services wizard, you can create forms that invoke web services and reports based on the results returned by the web service.

The enhancements to the Web Services wizard allow you to incorporate web services in an application without any programming. Discover Web Services Through UDDI Repository

A Universal Description, Discovery, and Integration (UDDI) registry is a directory where businesses register their web services. Through UDDI, the new Web Services wizard can search for registered web services by name.

The UDDI feature of the new Web Services wizard simplifies the process of registering a web service for consumption by an Oracle HTML DB application. Simplified and More Consistent User Interface

The user interface for Oracle HTML DB has been streamlined to improve usability. Specifically, a large number of options have been removed or reorganized to eliminate confusion. Additionally, commonly used tasks such as Run Application or Edit Application have moved to improve consistency.

Oracle HTML DB's simplified and more consistent user interface makes HTML DB easier to navigate and reduces the number of clicks to create and edit applications and components.

1.1.5 PL/SQL Improvements

PL/SQL in Oracle Database 10g Release 2 adds these new features:

  • Conditional compilation to allow automatic selection of source code according to the version of Oracle Database.

  • A new Web Gateway to allow the Oracle instance itself to respond directly to an HTTP request with a response generated by a stored procedure.

  • A package to support matrix math. PL/SQL Conditional Compilation

Conditional compilation enables PL/SQL programmers to use new language features in a PL/SQL compilation unit without sacrificing the ability to compile the unit in an older database version that does not support these features. Other typical uses include embedding conditionally compiled debugging and tracing support code in PL/SQL programs.

The benefit is that one body of source code can be deployed in a range of Oracle Database versions to take advantage of new features where they are available and to use fallback approaches where they are not. Debugging and tracing code can be left in place without penalizing the size of the executable or runtime efficiency. Such code can be easily activated by a simple recompilation. This provides a feature in PL/SQL that programmers who use other languages have come to expect. Obfuscation of Dynamically Generated PL/SQL Source Code

The existing DBMS_DDL supplied package is enhanced with two new subprograms. The command-line WRAP utility takes a plain text CREATE or REPLACE command that specifies creation of a PL/SQL unit and returns a CREATE or REPLACE command where the text of the PL/SQL unit has been obfuscated. The obfuscated representation is identical to what is produced if the same CREATE or REPLACE command is processed with the command-line WRAP utility. The CREATE_WRAPPED command is a shortcut for and has the same semantics as the EXECUTE IMMEDIATE DBMS_DDL.WRAP (input) command.

This feature allows the text of a PL/SQL unit that is created dynamically to be obfuscated. This is critical when an installed product generates new PL/SQL units, for example as part of a customization process, and when the generated code embodies methods that the product vendor considers to be valuable intellectual property. The command-line WRAP utility, available in Oracle Database 10g Release 1 and earlier, allowed the code that a vendor shipped to be obfuscated. This new feature allows custom code generated by shipped vendor code to be obfuscated. Relaxation of Line Length and Overall Limits for the DBMS_OUTPUT PL/SQL Package

The declaration of the package type DBMS_OUTPUT.CHARARR is enhanced to support the increased maximum of 32,767 bytes. Previously, the VARCHAR2 constraint was 255 bytes. This determines the maximum line size that can be written by the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE procedures. The procedure DBMS_OUTPUT.ENABLE (buffer_size integer defaults to 20000) has a new allowed value of NULL for the buffer_size. In this case, NULL means "unlimited." SQL*Plus, through the SET SERVEROUTPUT ON command, enables you to take advantage of the new "unlimited" meaning.

PL/SQL programmers frequently use DBMS_OUTPUT and, in Oracle Database 10g Release 1 and earlier, were constrained by the 255 byte limit. When using SQL*Plus, most programmers are regularly caught by the small default overall limit and sometimes by the current 1,000,000 maximum overall limit. In Release 2, the line length limit is increased to 32,767 bytes and the overall limit is removed altogether. UTL_NLA PL/SQL Package for Matrix Math

C and Fortran APIs for matrix math are freely available. Moreover, efficient platform-specific implementations have been written for many of the most popular platforms. The new UTL_NLA package exposes a matrix PL/SQL data type and wrapper PL/SQL subprograms for two of the most popular of these C APIs; namely BLAS and LAPACK.

To do matrix manipulation in PL/SQL in Oracle Database 10g Release 1 and earlier, it was necessary to invent a matrix representation based on PL/SQL native data types and then write matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. Oracle Database 10g Release 2, through the new UTL_NLA package, removes the programming effort and delivers a fast implementation.

1.1.6 SQL Language Improvements

SQL Language improvements in Oracle Database 10g Release 2 include:

  • Performance improvements for collection operators.

  • Added support for accessing remote LOBs.

  • Usability and performance enhancements to Expression Filter.

  • Perl compatibility in Regular Expression.

  • Rules Manager for Expression Filter can manage and evaluate an unlimited number of rules, provide efficient synchronization of multiple events and threads, and support a single management environment for rules and application data. Rules Manager

Rules Manager is a new feature of Oracle Database 10g Release 2. It enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the database. It can evaluate events using data from the application and from database tables. It stores intermediate results to quickly evaluate the next event in a long running composite event (an event made up of two or more simple events). Rules are defined using XML and SQL and can have complex conditions using conjunctions and disjunctions, and specify a set of events, time, and non-occurrence of events with or without a deadline. Event policies control how rules are processed and the duration of an event. Rules can trigger actions that are user-defined procedures running inside an Oracle Database or actions in another application.

The benefit of this feature is that rules that are managed in Oracle Database keep pace with changing business conditions and are always up-to-date; rules are easily changed with SQL and are not included in your application or loaded into a memory-based rules repository. Rules can be evaluated efficiently with the complete business context stored in your Oracle Database and data provided by your application. Event response is flexible; rules can trigger actions in Oracle Database or your application.

Additional advantages of Oracle Database over other approaches include:

  • Manageability, by storing rules and event policies with your application data.

  • Performance, by evaluating rules and coordinating multiple events and application threads with the full capabilities of Oracle Database.

  • Scalability, by evaluating sets of rules of any size. Regular Expression Enhancements

In Oracle Database 10g Release 2, Regular Expression is enhanced for Perl compatibility. Support for the common Perl REGEXP extensions that are not included and do not conflict with the POSIX standard are added.

Oracle's SQL Regular Expression provides a simple yet powerful mechanism for rapidly describing patterns and greatly simplifies the way in which you search, extract, format, and otherwise manipulate text in the database. Collection Operator Performance Improvements

Collection operators operate on collections of data objects. For Oracle Database 10g Release 2, the EQUALITY and MEMBER operators for collections have been improved.

These enhancements have improved the performance for collection operators. Distributed Large Objects (LOB) Support

Distributed LOBs support provides easy-to-use and efficient support for accessing unstructured data in a distributed environment. The data interface for LOBs can now INSERT, UPDATE, and SELECT LOBs across dblinks.

The benefit of this new support is the ability to access remote LOBs. Expression Filter Enhancements

Expression Filter usability and performance enhancements include:

  • Providing extensible optimizer support for the EVALUATE operator.

  • Allowing users to specify default values for a subset of attributes in an attribute set.

  • Allowing users to specify name spaces in stored XPath expressions.

  • Indexing XPath range predicates.

  • Allowing users to collect statistics on a set of XPath expressions stored in a table to create an optimal Expression Filter index structure.

Expression Filter features for Oracle Database 10g Release 2 include better usability and improved performance. You can specify default values for expression attributes and specify name spaces in XPath expressions.

1.1.7 XML Application Development

New for this release is the Enterprise XML Developer's Kit, which brings full XML support for XMLType in Java, C, and C++. This enables developers to use existing XDK-based applications directly with the database thus improving performance.

Also included for Java is XSLT 2.0 support with XPath functions and operators, bringing new power and efficiency to style sheets.

Finally, application developers can incorporate XML support into applications using the JAXB compiler. Its customization feature enables modification of Java classes to speed development and reduce support costs. XQuery

This feature provides a native implementation of XQuery, the World Wide Web Consortium's (W3C) emerging standard for querying XML, integrated into Oracle Database. It introduces two new functions, XMLQUERY and XMLTABLE.

You can use the XQuery language from an SQL client to query Oracle XML DB repository documents, XML views over relational data, and XMLType data, whether persistent or generated.

XMLTABLE maps the result of an XQuery to relational rows and columns so that results can be queried using SQL.

The benefit of this feature is that Oracle now supports querying of XML, relational, object-relational and repository data using XQuery, the W3C emerging standard for querying XML.

See also:

Oracle XML DB Developer's Guide for details Advanced XPath Rewrite

Oracle Database 10g Release 2 extends the XPath Rewrite feature of XML DB to handle additional operations on more complex Schema Constructs. This feature reduces the number of conditions under which XPath Rewrite does not occur. The net effect is that applications using these constructs run faster without any recoding.

This feature allows faster querying and updating of schema-based XMLType data by extending the number of Schema Constructs and Xpath expressions that can be processed by XPath Rewrite.

See also:

Oracle XML DB Developer's Guide for details New InsertXML(), AppendChildXML(), InsertXMLBefore(), and DeleteXML() Functions

The current UpdateXML() function can only manipulate the content of existing nodes within an XML document. It cannot be used to add or remove nodes from a document. The only way to add or remove nodes from a document is to use the methods provided by the DOM API.

This feature resolves this issue by introducing a family of SQL functions similar to UpdateXML() that make it possible to add and remove nodes from a document.

It also enables XPath Rewrite in the cases where the target of the function is a schema-based XMLType with structured storage. This means that operations can be executed in a more efficient manner than would be possible with the DOM API.

This feature improves programmer productivity by providing functions that make it possible to perform complex operations on the content of XML documents directly from SQL. Typically, these functions reduce the number of statements required to perform a particular operation when compared to typical DOM-based operations.

This new set of functions also improves application performance by allowing more complex manipulation of XML content to be performed by XPath Rewrite. XPath Rewrite makes it possible to perform operations on XML documents faster than can be achieved with conventional DOM or SAX-based programming techniques. It is now possible to do in one statement what previously took 10 to manipulate XML eliminating the need to use the DOM API to manipulate XML documents. These new functions improve application performance by allowing complex operations on XML documents to be performed using XPath Rewrite.

See also:

Oracle XML DB Developer's Guide for details Simple Object Access Protocol (SOAP) 1.2 Implementation in C/C++

Implementation of the SOAP 1.2 W3C specification in C and C++.

Developers can build C or C++ SOAP Services to consume and generate SOAP messages using the Oracle XML stack. SQL/XML Standard (SQL:2003 Standard Part 14) Compliance

This feature extends Oracle's support for the SQL:2003 Standard, adding support for comments, processing instructions, and CDATA and XML prologues. This helps ensure that the SQL/XML operators become the defacto approach to generated XML from a relational query, eventually replacing both XSU and the DBMS_XMLGEN package.

SQL/XML provides an intuitive, standards-based method for creating XML directly from a SQL query. The SQL/XML specification is Part 14 of the SQL:2003 Standard. This feature extends Oracle's support for the SQL/XML operators to bring it in compliance with the SQL:2003 Standard. SQL/XML Standard Compliance

SQL/XML lets you query XML using SQL in a standard way and defines ways of importing and storing XML data in a SQL database, manipulating it within the database, and publishing both XML and conventional SQL data in XML form.

Oracle Database 10g Release 2 includes support for all the functions in SQL/XML 2003 (for example, XMLSERIALIZE and XMLPARSE), and some Oracle-proposed extension functions.

See also:

Oracle XML DB Developer's Guide for details Easy XML Application Development Using JAXB Customization

The Java Architecture for XML Binding (JAXB) customization feature permits the XML-generated class names to be replaced with application or Java-specific names to ease manipulation of Java objects making development and maintenance more efficient and straightforward.

Application developers can more easily incorporate XML support into applications using JAXB. The customization feature enables modification of Java classes to speed development and reduce support costs. Oracle XML Developer's Kit (XDK) PL/SQL Document Object Model (DOM) API Deprecation

This feature completes the work required to make it possible to deprecate the XDK PL/SQL packages and replace them with synonyms equivalent to Oracle XML DB PL/SQL packages without having a negative effect on applications that were originally developed using the XDK implementation.

This feature allows applications developed with the legacy XDK PL/SQL DOM API package (XMLDOM) to leverage the improved performance and functionality offered by the Oracle XML DB PL/SQL DOM API (DBMS_XMLDOM) without requiring significant application changes.

This release completes the deprecation of the XMLDOM, XMLPARSER and XSL_PROCESSOR packages in favor of DBMS_XMLDOM, DBMS_XMLPARSER and DBMS_XSLPROCESSOR.

See also:

Oracle XML DB Developer's Guide for details XPath 2.0 and XQuery 1.0 Functions and Operators Support in Java

The World Wide Web Consortium (W3C) XPath 2.0 and XQuery 1.0 Functions and Operators adds support for the XML Schema data types along with a rich set of functions and operators to XPath that can be used in advance XSLT 2.0 style sheets and XQuerys.

This feature improves the performance and range of applications for style sheets and XQuerys. This reduces the need for compiled code, easing development and maintenance. Custom Metadata for Oracle XML DB Repository Resources

This feature allows an application developer to define an XML Schema that defines the custom metadata and associate it with a particular XML DB resource. Once the XML Schema has been defined, application developers can create richer content management applications by adding customer metadata to the standard metadata recorded by the Oracle XML DB repository.

This feature makes better use of information contained in documents by making it possible to associate custom metadata with documents stored in the Oracle XML DB repository. This metadata improves your ability to classify, organize, manage, process, and search the vital corporate information stored in documents.

See also:

Oracle XML DB Developer's Guide for details Secure Sockets Layer (SSL) Support

This feature enables the use of the secure HTTP protocol (HTTPS), in addition to the HTTP protocol, with Oracle XML DB.

SSL adds support for security standards to the Oracle XML protocols servers and allows the HTTPS protocol to be used to secure the transport of information between client and server.

See also:

Oracle XML DB Developer's Guide for details Transportable Tablespace Support

This feature adds new support for XMLType to the Transportable Tablespace capability.

The benefit of this new feature is that it allows the Transportable Tablespace feature of Oracle Database to be used to move tablespaces containing schema-based XMLType tables and columns from one database to another. This method moving XML between different Oracle databases is faster than alternatives such as FTP and Import/Export. Oracle XML DB Administration in the Enterprise Manager Web Console

Enterprise Manager can be used to manage the following Oracle XML DB features:

  • XML DB parameters

  • XML DB resources

  • XML DB Access Control Lists (ACL)

  • XML schemas

  • XMLType tables and columns

It is now convenient to manage Oracle XML DB in the same place where Oracle Database is managed, which is in the Enterprise Manager Web console.

1.2 Availability

The following sections describe Availability features for Oracle Database 10g Release 2.

1.2.1 Fast-Start Failover to Standby Database

In Oracle Database 10g Release 2, Oracle Data Guard offers a compelling set of capabilities that provide unparalleled levels of data availability, data protection, and data recovery. For example, on an outage of the primary server, site, or network, the new Fast-Start Failover feature allows Data Guard to rapidly and automatically fail over to the standby database, without requiring manual intervention. This significantly improves the degree of high availability as well as the disaster resilience for the system infrastructure. This capability makes Data Guard particularly attractive as a high availability/disaster recovery solution for 24x7 applications demanding the highest levels of uptime. Fast-Start Failover

On a failure or outage of the primary server or site, or of the network, Data Guard can now rapidly and automatically fail over to a previously chosen standby database, without requiring manual intervention.

The benefit of fast-start failover is that it increases the degree of high availability, as well the robustness of disaster recovery, for the system infrastructure. It also reduces manual operations, thereby reducing management costs. This makes Data Guard particularly attractive as a high availability/disaster recovery solution for 24x7 applications where it is extremely critical to maintain uptime transparently, despite failures and outages.

See also:

Oracle Data Guard Broker for details Automatic Conversion of the Primary Database to a Standby Database Upon Failover

After a failover, this feature allows the old primary database to be automatically reinstated as a standby database, without requiring re-creation of the primary, or manually performing the Flashback Database operation. This feature is supported only for synchronous redo transports that enabled zero data loss (that is, Maximum Protection and Maximum Availability modes). It also requires Flashback Database to be enabled on both the primary and target standby databases. It is also possible to do this conversion using a single command or a simple push-button interface through Enterprise Manager.

The benefit of this feature is that it enables the Data Guard configuration to easily restore disaster protection in the configuration, without complex manual steps, improving the robustness of the disaster recovery features of Data Guard, as well as improving Data Guard manageability.

See also:

Oracle Data Guard Broker for details Optimized Asynchronous Redo Transmission

Asynchronous redo transmission using the log writer process is no longer limited by the size of the network buffer. The log writer process is no longer blocked on network time outs during asynchronous redo transmission. This allows a greater number of archiver processes to archive redo logs remotely.

The benefit of this feature is that it reduces any effect on the primary database during asynchronous redo transmission. It allows faster redo transmission to standby databases for bulk batch updates on the primary database. Faster Redo Apply Failover

This feature allows the user to activate a physical standby database (that is, fail over to a primary database) that has never been opened read-only, without doing a database restart.

The benefit of faster Redo Apply failover is that it enables customers to recover from a failure or outage much faster, increasing the degree of high availability of the system. Faster SQL Apply Failover

Failover to a logical standby database can now be completed much faster. It is no longer necessary to restart SQL Apply as part of the failover operation.

The benefit of faster SQL Apply failover is that it enables recovery from a failure or outage much faster, increasing the degree of high availability of the system.

1.2.2 Support for Database Transport Across Same Endian Platforms

Oracle Database 10g Release 2 introduces database transport across the same Endian platforms which provides a fast and easy way to transport the whole database across platforms with the same Endian format. This feature uses RMAN to quickly convert the datafiles and create the new database. The newly created database is a copy of the source database but on a different operating system/hardware platform; for example, fast Windows to Linux or Solaris to HP-UX migration. Database Transport Across Same Endian Platforms

This transport feature provides a fast method to move a database from one platform to another.

The ability to migrate databases from one platform to another has become more important for Oracle customers. Oracle Data Pump can export the entire database, but it may be time consuming because it uses the SQL layer to process data. Database transport across the same Endian platforms provides a faster and easier way to move Oracle databases from one platform to another.

1.2.3 High Availability Improvements

Oracle Database 10g Release 2 continues to enhance the built-in database features for recovery and testing operations to be expeditious, simple, and automatic. Employing Recovery Manager (RMAN) and Flashback Database further reduces the DBA's time spent on the important task of database backup and recovery and creation of test databases. In this release, Data Guard has also optimized the network transmission of redo data, enabling standby databases to be synchronized with the primary database at a faster rate than in previous releases, with minimal effect on the primary database. Similarly, Data Guard has considerably improved its manageability and ease-of-use compared to previous releases, allowing administrators to monitor the state and performance of the disaster recovery configuration at a higher granular level. Add and Create a Transportable Tablespace to a Database

This feature allows a DBA to:

  • Generate a tablespace set from the source database and plug it into the target database, essentially automating movement of tablespaces between the databases.

  • Generate a tablespace set which can be used to transfer a tablespace to any compatible database.

  • Plug-in the tablespace set (provided by the user) into the target database and perform an import operation.

Using this tool, database administrators can automate the process of transporting tablespaces across databases. Tablespaces can be transported between databases running on different platforms using different supported versions. This user-friendly tool performs checks and validations, which are needed to ensure valid and compatible tablespace sets for plugging into the target databases. Additional Data Type Support in LogMiner and SQL Apply

SQL Apply, LogMiner, and Streams now support IOTs with overflow segments and LOBs.

This feature allows Data Guard SQL Apply and LogMiner to be used for protection of a broader range of data types. Similarly, Streams can be used for replication of a broader range of data types.

See also:

Oracle Database Utilities for details Automatic Deletion of Applied Archive Logs

Archived logs, once they are applied on the logical standby database, will be automatically deleted by SQL Apply.

This feature reduces storage consumption on the logical standby database and improves Data Guard manageability. Data Guard: Improved Manageability for Redo Transport, Log Apply, and Broker

This feature deprecates unnecessary initialization parameters and attributes for Oracle Database 10g Release 2.

This feature simplifies the usage of initialization parameters and attributes, as well as certain SQL statements. Dynamic RMAN Channel Allocation for Backing Up and Recovering RAC Database

By configuring parallelism when backing up or recovering a RAC database, RMAN channels are dynamically allocated across all RAC instances. This is similar to dynamic RMAN channel allocation for a single instance.

Channel failover allows a failed operation on one node to be continued on another node.

This feature eliminates manual allocation of RMAN channels for each RAC node. It also provides better tolerance of media failures (that is, when one node fails, another node can continue backup work). RMAN Backup Encryption

RMAN now creates encrypted backups that cannot be restored by unauthorized people. There are 3 modes of backup encryption:

  • Transparent encryption

  • Password encryption

  • Dual-mode encryption using either transparent or password encryption

Backup encryption is a regulatory requirement for many customers. The presence of this capability in Oracle obviates the need to purchase it elsewhere. Easy Conversion of a Physical Standby Database to a Reporting Database

A physical standby database may be activated to be a primary database, opened read/write for reporting purposes, then flashed back in the past to be easily converted back to a physical standby, upon which Data Guard will automatically synchronize the standby database with the primary database. This mechanism also allows a physical standby database to be used as part of a clone, test, or development solution. Note that while this database is open for reporting, it will not be able to receive any redo data from the primary database. Therefore, such a configuration may be more suitable for customers with less demanding disaster recovery requirements.

This feature allows the physical standby database to be utilized for reporting, read/write, and cloning activities, enabling customers to extract more value out of their Data Guard investment. Eliminate Control File Re-Creation

With the control file enhancements, there is no longer a requirement to re-create the control file when changes in the configuration parameters are made. These include the MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES parameters.

This feature eliminates database downtime when a configuration parameter change is made in the control file. Enhanced Memory Corruption Checking

A new value of MAX has been added to the database parameter, DB_BLOCK_CHECKSUM, to compute checksums on the reading, updating, and deleting of a block.

This feature enhances the ability to validate the block when it is in memory and not just on disk. Enhanced RMAN Backup Job Views

The new RMAN views correlate and simplify the metadata of an RMAN backup job. For example, a backup job can be associated with its backup sets, and a backup set with its composite control file, archived logs, or datafiles. Additional details such as I/O file sizes and compression ratio can be found in the new views.

This features enhances the manageability of RMAN backups by providing a SQL interface to find additional statistics on the backup job and files. Enhanced Server Side Shutdown

When using the SHUTDOWN ABORT command with Oracle Database 10g Release 2 and higher clients, all processes (including, for Unix, the foreground process) will be terminated.

Also, when issuing the STARTUP FORCE command, in previous versions, "Starting ORACLE instance (FORCE)" was written to the alert log. In Oracle Database 10g Release 2 and higher, "Shutting down instance (ABORT)" followed by "Starting ORACLE instance (NORMAL)" are now written to the alert log.

Transparently handled both in the server and the client, all processes will be cleaned up after a SHUTDOWN is issued. This allows a DBA to startup in the same session. Enhanced Space Management Verification

Additional checking has been added to the DBMS_SPACE_ADMIN package to provide better tools to debug space management problems. Extended syntax is added to the ALTER SYSTEM command to dump the space headers of locally managed tablespace files for added analysis.

This feature enhances the tools used to debug space management-related problems to detect corrupt metadata. Failover Improvements for Real Application Clusters (RAC) Distributed Transactions

Distributed Transactions in a RAC environment detect failures and start the failover and failback processes automatically.

The benefit of this feature is reduced administration and development time because Oracle manages the distributed transaction, therefore, there is no need to write manual procedures. Faster Instance Startup for Large Memory Configurations

This feature decreases the time it takes to open Oracle Database instance.

Accelerated instance startup makes Oracle Database available sooner when databases have multigigabyte shared memory configurations. Fine-Grained, Automated Tracking of SQL Apply Runtime Performance

Several additional views have been provided, and existing views have been enhanced to enable administrators to dynamically track the progress and performance of SQL Apply at a granular level. Enterprise Manager also provides historical trend analysis of the progress and performance data.

This feature makes it considerably easier to maintain and tune SQL Apply configurations and improves Data Guard manageability. Flashback Across Data Guard Switchovers

It is now possible to flashback the primary and standby databases to an SCN or a point in time prior to the switchover operation. Using flashback in this way on a physical standby database preserves the standby role. Using flashback in this way on a logical standby database changes the role of the standby database to what it was at the target SCN/time.

This extends the flashback window; that is, the possible SCN/time in the past to which any database can be flashed back to. This provides administrators more flexibility to detect and correct human error situations. Flashback Database Through Resetlogs

This feature supports flexible data recovery that enables movement of the database back and forth in time even after you perform a resetlogs operation.

If you ever need to do a resetlogs operation for recovery purposes, you can take the database back to a point in time before the resetlogs if you find that you made a mistake. This provides administrators more flexibility to detect and correct human error situations. Hardware Assisted Resilient Data (HARD) Compliant RMAN Backups

RMAN backup pieces can be stored on and validated by HARD-compliant disk hardware.

This prevents corruptions in RMAN backup pieces from being written to permanent storage. Increase Supported Real Application Clusters Instances for a Database

For Oracle Database 10g Release 2, the control file supports 1,055 instances for a RAC database.

This feature provides enhanced support and flexibility for Grid Management. Incremental Roll Forward of Database Copy

This feature can be used to create an RMAN incremental backup from a specified SCN. This incremental backup can be used to quickly move a standby database forward in time or load new data into a clone database for testing.

This feature minimizes the time data is unprotected by creating an incremental backup that can be applied to a standby database to quickly make the standby database transactionally consistent with the primary database. This also eliminates the need to continuously re-create a clone database with newer data values. Online Redefinition Supports Clustered Tables, ADT's, MV Logs, AQ Tables, and Preserves Database Statistics

Online redefinition can now be performed on clustered tables, tables containing Abstract Data Types (ADT), Advanced Queuing (AQ) tables, Materialized View (MV) logs, and it retains all statistics on a table. It also clones statistics, checks and non-NULL constraints, and dependent objects on nested tables. PL/SQL packages do not have to be recompiled if the number and ordering of columns and data types in the table remain unchanged.

This feature means less downtime as more tables are now supported for online redefinition. Analysis of the table after reorganization is no longer required and PL/SQL packages remain available following an online redefinition, thus maintaining application availability. These enhancements are especially beneficial to customers using Oracle Applications. Online Redefinition of a Single Partition

Individual partitions may now be reorganized online.

With this feature, system downtime is reduced and, during reorganization, storage requirements and rollback space requirements are significantly reduced. Optimized Creation of Logical Standby Database

Creation of a logical standby database no longer requires the creation of a specialized logical standby control file, which could not be used by RMAN. Logical standby databases can now be created easily from a physical standby database.

This feature reduces specialized manual operations for creating a logical standby database and improves Data Guard manageability. Unused Block Compression

By optimizing database backups, only blocks currently used by the database are backed up. In previous releases, NULL compression was utilized, whereby only never-used blocks were excluded from backup. With this new feature, all unused blocks, whether they have been used or not in the past, will be excluded from backup.

This enables faster backups and smaller backup files. Reorganized Object Types Improves User Interface

This feature supports reorganization of additional database object types that are currently not supported (for example, nested tables), enhances capabilities like launch-in-context, and improves usability of the Re-org wizard.

Currently if a tablespace contains objects like nested tables, the tablespace cannot be reorganized. This new feature enhances support for more object types for tablespace reorganization. In addition, this feature provides enhanced support for launch-in-context so that applications such as Policy Violations can pass recommendations to the wizard directly and do not depend on the user to enter the data manually. Restore Point

This feature provides the ability to associate a user-defined name, Restore Point, with a point-in-time. The Restore Point can be used with a Flashback Database or Flashback Table during recovery operations. Restore Point can be specified such that it guarantees the database can be recovered to a particular point-in-time.

Restore Point eliminates the need to manually record an SCN or timestamp to use for Flashback Database and Flashback Table operations. In addition, a special 'guaranteed' restore point ensures that the database can be flashed back to that particular point-in-time. Using guaranteed restore points can achieve efficient disk space usage, as only those flashback logs necessary to meet the guaranteed restore point are required to be retained.

Restore points can also be created as 'guaranteed', meaning it provides enhanced Flashback Database efficiency in recovery and disk storage requirements. And, it guarantees the database can be rewound to a specified point-in-time. Temporary Datafiles Are Re-Created on RMAN Recovery

Temporary datafiles that belong to locally managed temporary tablespaces are automatically re-created during recovery operation.

This eliminates the need to manually create temporary tablespaces after recovery.

1.3 Business Intelligence

The following sections describe new features for Oracle Database 10g Release 2 in the Business Intelligence area.

1.3.1 SQL Support for Analytic Applications

In this release, Oracle extends the SQL language to support more powerful analytics. The MODEL clause of the SELECT statement, which provides a powerful new approach to analytic calculations, adds to its expressiveness and scalability in this release. Bitmap indexes, a vital structure for efficient Business Intelligence query processing, also gain enhanced scalability. Enhanced Upsert Abilities in MODEL Clause

This feature provides new behavior supporting upsert of cells. Rules are now able to insert new cells even when the left hand side includes existential predicates such as ANY, IN, >, <, and LIKE.

The benefit is more expressive rules and greater productivity in specifying the MODEL clause. Enhancements to MODEL Rules Which Use FOR Loops

This feature moves evaluation of MODEL clause rules using FOR loops from compile time to runtime. This permits rules with FOR loops to use reference spreadsheets, nested references, and the ITERATION_NUMBER variable. In certain situations, subqueries within FOR loops can have shared cursors and refer to tables defined in the WITH clause.

The benefit of this feature is that MODEL clauses with certain types of rules perform better, handle larger model sizes, and include more powerful rule expressions. Use of Analytic Functions in the SQL MODEL Clause

This feature provides direct use of analytic functions like moving average and cumulative sums in SQL MODEL clause rules.

Native support of analytic functions is significantly faster than alternative techniques and simplifies expression of complex calculations, such as allocations and forecasts, in the MODEL clause. Intra-Partition DML in the Presence of Bitmap Indexes

Intra-Partition DML (PDML) can now be fully leveraged in the presence of bitmap indexes.

With this feature in Oracle Database 10g Release 2, the last main restriction for PDML is lifted.

1.3.2 Improved Very Large Database (VLDB) Support

Oracle is designed to meet all data requirements for the largest databases. VLDB environments, such as data warehouses, depend on partitioned tables. This release enables more:

  • Partitions for each table

  • More efficient partition management

  • More partition-based query optimization Increased Maximum Number of Partitions Per Object

The maximum number of partitions is now 1024K-1, increased from a previous limit of 64K-1.

Increasing the maximum number of possible partitions to objects enables finer granularities for partitioned objects and even broader flexibility in choosing the right partitioning approach for a business scenario. Enhanced Dynamic Partition Pruning

This feature enhances partition pruning for complex queries.

This feature provides enhanced performance for a broader set of complex queries. Resource Optimized DROP TABLE for Partitioned Tables

When dropping a large partitioned table in the nonrecoverable PURGE mode, the DROP operation is internally split to drop chunks of partitions.

Dropping large partitioned tables can affect tens of thousands of partitions that all have to be logically removed from the system. The capability of transparently dropping such an object in an incremental fashion optimizes the resource consumption and positively affects the run-time behavior.

1.3.3 Reduced Information Cycle Time

A key requirement for business intelligence is to be able to analyze data more rapidly after an event occurs. Oracle provides a wide range of features to support efficient ETL (extraction, transformation, load). This release reduces the information cycle time with enhancements ranging from data loading to query processing. New features minimize rollbacks during bulk DML, enable more Change Data Capture environments, simplify materialized view refresh, and enhance query rewrite. Distributed Asynchronous Change Data Capture (CDC)

The internal Change Data Capture (CDC) tasks of Streams Capture and Apply are decoupled, thereby enabling a heterogeneous CDC setup with different operating systems and Oracle versions. The propagation between source and target database is a pure Streams implementation, enabling asynchronous CDC to leverage any existing Oracle9i Release 2 system as a source.

Asynchronous Change Data Capture (CDC) no longer requires the same operating system for source and target. Furthermore, distributed asynchronous CDC capabilities expands the reach of CDC source systems to include Oracle9i Release 2. DML Error Logging Table

This feature allows bulk DML operations to continue processing, when a DML error occurs, with the ability to log the errors in a DML error logging table. On a DML statement level, you specify whether to log errors or not, the level of detail information, as well as a maximum error threshold, similar to external table and SQL*Loader.

This feature combines the power and speed of bulk processing with the functionality of row level error handling.

See also:

Oracle Database Concepts for details Partition Change Tracking Refresh Without Materialized View Logs

Partition Change Tracking Refresh (PCT) no longer requires materialized view logs. It can be combined with conventional fast refresh to enable a refresh, where one table is using PCT and the other table uses materialized view logs.

This feature improves performance because materialized views can now be created in situations where they were previously not used due to the overhead of materialized view logs. This feature also provides faster refresh due to the mix of PCT and conventional refresh. Query Rewrite Using Multiple Materialized Views

Query Rewrite may now use multiple materialized views.

This feature improves performance as more queries are eligible for query rewrite and reduced maintenance costs because fewer materialized views are required. Skip Level Dimension Hierarchies

Levels in the dimension can now be skipped. For example, this feature allows a geography dimension where the state level might be skipped for cities such as Washington, DC.

The benefit is more complete modeling of the dimensions and performance benefits, because improved query optimizations are now possible.

1.3.4 Online Analytical Processing (OLAP) Support for Analytic Applications

Oracle OLAP provides a complete OLAP server that is fully integrated into Oracle Database. In this release, Oracle OLAP makes cube aggregation more flexible and enhances the OLAP API, enabling an even broader set of analytic applications than in earlier versions. Optimized OLAP cube refresh supports applications demanding the very latest data. Incremental Aggregation of Compressed OLAP Cubes

Incremental aggregation of compressed cubes occurs automatically within the multidimensional engine. The multidimensional engine tracks detail level changes to the cube and aggregates only the summary level data that would be affected by the newly loaded data.

This new feature improves performance for the process of aggregating an OLAP cube that has been stored using cube compression technology. This performance improvement is seen when the compressed cube has been incrementally updated with new data (for example, when adding a new time period to the cube).

See also:

Oracle OLAP DML Reference for details OLAP API Supports Calculated (Virtual) Dimension Members

Within the OLAP API model, application developers can define calculated (virtual) dimension members. For example, a calculated dimension member such as 'New England' in a geography dimension could be created as the sum of stored dimension members 'ME + VT + NH + MA + RI + CT'.

Calculated member support in the OLAP API allows application developers using the OLAP API to create calculated (virtual) dimension members within an OLAP API query. This extends the analytic capabilities of applications that use the OLAP API. OLAP Compressed Cubes Support Semiadditive Aggregation Methods

Semiadditive aggregations are those where the aggregation method on one or more dimensions is something other than sum. Examples include first, last, hierarchical sum, and hierachical weighted average. In Oracle Database 10g Release 1, compressed cubes could not be used with semiadditive aggregation. In Oracle Database 10g Release 2, semiadditive measures can be used with compressed OLAP cubes.

Support for semiadditive aggregation methods with compressed cubes provides the opportunity for significant aggregation performance improvements.

See also:

Oracle OLAP DML Reference for details

1.3.5 Data Mining Support for Analytic Applications

Oracle Data Mining provides integrated mining capabilities directly in Oracle Database. Oracle enriches its data mining for the full spectrum of application developers with enhancements targeted to Java specialists, PL/SQL developers, and data mining newcomers. Highlights include decision tree models, simplified model development, and a new SQL function for predictive data mining. Decision Tree Algorithm

This feature implements a state-of-the-art decision tree algorithm leveraging in-database parallelism.

The benefit of this feature is a transparent, rule-producing algorithm. The presentation of a decision tree in the form of human-interpretable rules increases the confidence, acceptance, and value of the algorithm. The decision tree is optimized to allow fast and scalable data scoring. Predictive Analytics Package

The Predictive Analytics Package provides an easy-to-use PL/SQL interface enabling straightforward automation of the data-mining process.

The Predictive Analytics Package makes reasonable assumptions about the data and the mining process and greatly simplifies the use of data mining for end users. This feature makes data mining more accessible to non-data analysts.

See also:

Oracle Data Mining Concepts for details SQL-Level Data Mining Scoring Capability

This feature provides SQL built-in data mining scoring functions for classification, regression, clustering, and feature extraction data mining models.

This capability allows customers and applications to:

  • Ease deployment of data mining models within the context of existing applications

  • Dramatically improve scoring performance

  • Enable pipelining of results, which can accelerate the presentation of findings to end users Java Data Mining (JDM) Compliant Java API

Oracle Database 10g Release 2 provides a Java Data Mining (JDM) JSR-73 compliant Java API. Oracle Data Mining 10g Release 2 requires a Java API implemented on top of the 10.1 PL/SQL API. This unifies the overall product, enabling interoperability between models.

This feature provides vendor neutral, open standard data-mining application development.

1.4 Clustering

The following sections describe new features for Oracle Database 10g Release 2 in the clustering area.

1.4.1 Real Application Clusters (RAC) Performance

This release provides session management capabilities that improve the ability to balance workloads across instances in a clustered environment. Workload policies are defined which enable connection pools to route work requests to the appropriate instance based on real-time information. Load Balancing Advisory

Real Application Clusters includes a Load Balancing Advisory that monitors the work for each service in an instance. The advisory creates Fast Application Notification (FAN) events to provide feedback that enables routing by the connection pool to respond quickly to changing conditions in the system. In steady state, the system approaches equilibrium with optimal service times across all Oracle Database 10g RAC instances. JDBC and ODP.NET Connection Pools are integrated to provide the best connection to an application request instead of a random connection from the pool. Oracle has integrated its connection pool clients (OCI, ODP.NET, and JDBC) with the Load Balancing Advisory. Runtime connection load balancing enables connection pools to route work requests to the connections that can best serve the work based on the FAN events received from RAC. Requests can be routed according to different policies.

The benefit is improved performance and use of resources. Oracle Cluster File System for Solaris

Oracle Cluster File System (OCFS) will be made available for Sun Solaris environments. This allows for easy management of data that needs to be shared across nodes in a clustered environment. OCFS is designed to support all Oracle Database files and ORACLE_HOME binaries. Note that OCFS for Sun Solaris environments will be released after the release of Oracle Database 10g Release 2.

This feature provides enhanced cluster manageability for clusters in Sun Solaris environments.

1.4.2 Real Application Clusters Robustness

This release improves the RAC experience by verifying the environment before installation, providing tools to assist in diagnosing problems that may occur, and ensuring there are redundant copies of Clusterware Files. Clusterware File Redundancy

There are 2 files, the Oracle Cluster Registry (OCR) and the Voting Disk, that are critical to running a cluster using Oracle Clusterware. Oracle Clusterware automatically maintains two copies of the Oracle Cluster Registry (OCR) file and three or more copies of the Voting Disk files. Mechanisms are also provided for the online replacement of failed OCR devices.

The benefit is that redundant copies of critical clusterware management files are automatically maintained to protect against single points of failure and eliminate the need for third-party storage redundancy solutions. Cluster Verification

Oracle Clusterware provides a Cluster Verification Utility that verifies the availability and integrity of a wide range of cluster elements in Oracle Clusters and Oracle Database 10g RAC environments. These facilities include both a standalone Cluster Verification Tool for use by DBAs and callable routines invoked automatically by Oracle installation, configuration, and system management tools. The Cluster Verification Utility can be invoked at any stage of a cluster configuration.

Cluster verification makes the installation, configuration, and overall management of Oracle Clusterware environments including Oracle Database 10g RAC environments simpler by quickly and easily identifying problems in complex cluster systems. Cluster Tracing and Diagnostic Enhancements

New facilities in Oracle Database 10g Release 2 provide improved diagnostics of cluster wait/hang conditions and more effective logging of Oracle Clusterware error/alert messages. In addition, diagnostic information for faster debugging and problem analysis is provided.

The benefit of these enhancements is greater code reliability and faster problem resolution by Oracle Support Services and the development community.

1.4.3 Application High Availability With Oracle Clusterware

This release extends Oracle Clusterware by providing an open API which allows additional (non-Oracle) components running on the database server to be managed by Oracle Clusterware. High Availability API

An open API is provided to Oracle Clusterware to enable customers and partners to integrate their application system components into Oracle high availability environments. Application system components registered with Oracle Clusterware are managed along with Oracle system components to ensure that failures of any component can be detected and recovery procedures initiated to quickly restore the overall system to an operational state.

This feature ensures that complex cluster systems involving Oracle components and components from customers and partners can be managed together by Oracle Clusterware to provide high overall system availability.

1.5 Content Management

The following sections describe Content Management features for Oracle Database 10g Release 2.

1.5.1 Oracle Text Improvements

Improvements for Text include a new set of document filters that provide better quality and performance, Asian language support for CTXRULE index type, and better manageability of text indexes. Also, the new KWIC feature (also called snippet) returns query keywords marked up in their surrounding text, allowing the user to evaluate them in context. CTXRULE Asian Language Support

Enhancements in this release to CTXRULE enable rule-based classification on Japanese, Chinese, and Korean languages.

CTXRULE is a filtering model for Asian language search and rules and enables rule-based classification. Filtering

A new AUTO_FILTER filter has been added to Oracle Text in this release. This new filter takes advantage of the automatic filtering technology licensed from Verity, Inc.

Oracle Text's automatic filtering technology, licensed from Verity, Inc., enables you to index most document formats. This technology also enables you to convert documents to HTML for document presentation.

See also:

Oracle Text Reference for details Key Word in Context (KWIC)

The Key Word in Context (KWIC) feature has been enhanced in this release with two new procedures, CTX_DOC.SNIPPET and CTX_DOC.POLICY_SNIPPET, that return text fragments containing keywords found in documents.

These new procedures enable you to see the keywords in their surrounding text, allowing you to evaluate them in context.

See also:

Oracle Text Reference for details Online Indexing for Local Partitioned Index

With online indexing for local partitioned indexes, you can now create an index on a partition while updating it; the same as with unpartitioned indexes.

This features improved the manageability and scalability for text indexing.

See also:

Oracle Text Reference for details

1.5.2 interMedia Improvements

interMedia now includes support for the Digital Imaging and Communications in Medicine (DICOM) standard, the most common medical imaging format. Also provided are:

  • APIs that extract embedded content metadata in IPTC, EXIF, and XMP formats from a number of popular image formats and return this metadata as a collection of XML documents.

  • APIs that embed XMP formatted metadata into images. interMedia DICOM Medical Imaging Format Support

interMedia now supports the most common medical imaging format, DICOM version 3. Applications can now use interMedia Java and PL/SQL APIs to extract metadata about patients, physicians, diagnoses, treatments, tests and procedures, and other relevant information included in the DICOM format. Metadata extracted from DICOM images is represented as an XML document returned in an XMLType object. Each returned document conforms to an XML schema that is registered with the database. Once this metadata has been extracted from the image, it can be stored in an Oracle Database, indexed, searched, and made available to applications using the standard mechanisms of Oracle Database. Not all metadata is extracted, but metadata generally present in all DICOM images will be extracted.

This new interMedia capability allows Oracle Database 10g Release 2 to serve as the repository for shared multiterabyte medical imaging archives used by HIMMS and PACS systems, life sciences and medical research centers, and government agencies.

See also:

Oracle Multimedia User's Guide for details interMedia Support for Image Content Metadata and XML DB Integration

In addition to the current support for format metadata (height, width, mimetype, etc.), interMedia now includes Java and PL/SQL APIs that extract embedded content metadata (in IPTC or IIM, EXIF, and XMP format) from TIFF, GIF, and JPEG image formats. Information supplied by digital cameras, image editing software programs, news and wire services, such as photographer, location, date, time, and other application data can now be extracted and represented as a collection of XML documents returned in XMLType. Once metadata is extracted, it can be stored in an Oracle Database, indexed, searched and made available to applications using the standard mechanisms of Oracle Database.

interMedia Java and PL/SQL methods also allow any Adobe XMP (tm) formatted, user-defined metadata to be embedded into TIFF, GIF, and JPEG formats.

By placing the metadata and the image data in the same containing binary image, both types of data can be shared and exchanged reliably as a unit.

See also:

Oracle Multimedia User's Guide for details Java Advanced Imaging 1.1.2 Support

Java Advanced Imaging 1.1.2 provides support for the new release of Sun's Java Advanced Imaging Package (JAI).

Oracle supports the latest production release of the Sun Microsystem Java Advanced Imaging (JAI) package for interMedia applications that use Oracle to store and process image content.

1.6 Database Overall

The following sections describe new features for Oracle Database 10g Release 2 for the database in general.

1.6.1 Database Utilities

The following sections describe new Database Utilities features. Easier Manual Upgrades

Manual database upgrades are now easier to do. For any database version being upgraded to Oracle Database 10g Release 2, only one common SQL script has to be invoked when performing a database upgrade. Specifying a version-specific script is no longer required, because Oracle now automatically determines what version is being upgraded and runs the appropriate upgrade scripts for that database and all of its included components.

The advantage is that less time is required to do manual database upgrades.

See also:

Oracle Database Upgrade Guide for details Enhanced Pre-Upgrade Information Utility

The Pre-Upgrade Information Utility has been enhanced to provide improved resource estimations for tablespace space usage and elapsed upgrade runtime.

This feature improves the manageability of Data Pump jobs and allows users to get accurate information about possible issues with their specific database upgrade prior to running DBUA or performing a manual upgrade. Default DATA_PUMP_DIR Directory Object

This feature creates a default DATA_PUMP_DIR directory object. This simplifies the use of Data Pump, because the DBA does not have to manually create a directory object before trying to use Data Pump Export.

This allows users to quickly start using Data Pump because a default directory object is automatically created.

See also:

Oracle Database Utilities for details Dumpfile Compression

Data Pump Export dump files can contain database metadata objects in a compressed format, allowing dump files to consume less operating system disk space. This metadata is automatically uncompressed during Import.

This feature reduces the amount of disk space consumed by dump files.

See also:

Oracle Database Utilities for details Extended Job Status Display

This feature has extended the job status display so that users are able to see the degree of parallelism used by the Data Pump Workers in Export/Import jobs.

This provides improved manageability of Data Pump jobs. FILESIZE Value for Dumpfiles

This feature allows users to indicate the size of the dumpfile being created when using the Export command line interactive ADD_FILE command by specifying a FILESIZE value. In previous releases, the size of the file was unlimited.

With this feature, if there are storage restrictions, users can control the size of dumpfiles. Monitoring of Data Pump Jobs Started Outside of Enterprise Manager

This new feature helps to:

  • Monitor Data Pump jobs started outside of Enterprise Manager

  • Support Data Pump Export from a read-only database

  • Move to new server APIs

In previous releases, Enterprise Manager only monitored Data Pump jobs that had been created using Enterprise Manager. However, it is a common activity for a DBA to create Data Pump jobs without using Enterprise Manager and it is possible for these jobs to take a very long time (hours or days) to complete. Enterprise Manager is a database monitoring tool and has been enhanced to monitor all Data Pump jobs, whether created by Enterprise Manager or not. New DBMS_DATAPUMP API Calls

The WAIT_FOR_JOB API provides the simplest mechanism for waiting for the completion of a Data Pump job. The GET_DUMPFILE_INFO API analyzes a specified dumpfile and returns dumpfile information such as version, creation date, and character set.

This new features improves the manageability of Data Pump jobs. You can get more information about Data Pump job completion and specific dumpfiles. New PCTSPACE Transform Parameter on Data Pump Import

With this feature, you can reduce the amount of space required for tablespaces by shrinking tablespace storage allocation. Large datafiles in production systems can be reduced so that smaller datafiles are created during Import on test systems.

The benefit of this feature is the ability to reduce the amount of tablespace storage required for datafiles. Object ID (OID) Transform Parameter

With this enhancement to Data Pump Import, you can suppress the generation of the Export OID clause for object types using the new OID transform parameter.

This is useful for duplicating schemas across databases, using Export and Import, when you cannot guarantee that object types will have identical OID values in those databases. SQL*Plus AUTOTRACE EXPLAIN Conversion to DBMS_XPLAN

SQL*Plus SET AUTOTRACE EXPLAIN PLAN_TABLE queries are replaced by a call to the DBMS_XPLAN package.

This feature provides a single Oracle standard for EXPLAIN PLAN_TABLE output.

See also:

SQL*Plus User's Guide and Reference for details SQL*Plus DBMS_OUTPUT Buffer and Line Size Limit Increase

In Oracle Database 10g Release 2, the SQL*Plus SET SERVEROUTPUT command uses the newly increased line size (32767 bytes) and unlimited number of lines of DBMS_OUTPUT.

The benefit of this feature is increased usability of SQL*Plus.

See also:

SQL*Plus User's Guide and Reference for details SQL*Plus XQuery Support

You can enter XQuery 1.0 (an XML Query Language) statements and retrieve results in SQL*Plus.

This feature allows XQueys to be created and tested in a highly available tool (SQL*Plus).

See also:

SQL*Plus User's Guide and Reference for details

1.7 Database Security

The following sections describe new features for Oracle Database 10g Release 2 in the Security and Directory areas.

1.7.1 Privacy and Confidentiality

Privacy, confidentiality, and the protection of personally identifiable information (PII) is a global concern in today's economy. Oracle provides a wealth of server-enforced protections for private and confidential information. Oracle Database 10g Release 2 introduces Transparent Data Encryption that quickly and easily encrypts sensitive information. Existing applications generally continue working without any modification because the data is transparently decrypted when accessed through SQL. Transparent Data Encryption

Oracle uses industry standard encryption algorithms including AES and 3DES to encrypt columns that have been marked to be encrypted. Key management is handled by the Database.

This feature provides transparent data encryption and key management. Businesses can protect sensitive data in Oracle databases without any changes to their application. SQL interfaces to Key Management hide the complexity of encryption. This feature mitigates liability due to storage theft. Key Management for Data Encryption

This feature provides a user interface to the transparent data encryption feature.

The benefit is transparent key management for data encryption.

1.7.2 Improved Manageability

The ability to focus more on security policies and less on low level implementation details is key to securing the enterprise. Oracle Database 10g Release 2 Transparent Data Encryption manages the encryption keys for the application administrator, simplifying the implementation and management of encrypted data. In addition, Oracle Database 10g Release 2 auditing enhancements include writing audit data in XML format to the operating system and providing additional protections for audit data at the operating system level. Enhanced Proxy Authentication

Proxy authentication in Oracle Database has always been available using OCI or JDBC programming. This feature extends this capability to the CONNECT command and enhances the current OCI/JDBC interface. For example, an application user/schema could be managed as a directory user, improving manageability of application schemas across the enterprise.

This feature extends proxy authentication capability to tools such as SQL*Plus which are used by applications for running batch jobs. Application security is improved as the application schema password is not exposed at connection time. It also enhances the existing proxy capability by allowing proxy users to be directory users. Audit Trail in Standard XML Format

Audit trail can be written to the operating system files in standard XML format. These logs can also be viewed using dynamic views providing better access to information in the operating system audit logs.

This feature enhances database operating system audit capability to support XML audit trail format. This allows the DBA to build custom tools to trace abnormal activities and view and mine audit logs. It also enhances the ability of the DBA to view operating system audit information when authenticated to the database.

See also:

Oracle Database Security Guide for details

1.7.3 Secure By Default

A major objective for Oracle Database 10g Release 2 is to ensure you can easily install and configure Oracle Database in a hardened configuration. This includes locking default accounts, providing database roles for privilege management, and protection of sensitive information. Oracle Database 10g Release 2 provides additional security for passwords used in fixed database links. In addition, the much used CONNECT role has been modified to include only those privileges required to connect to the database, namely the CREATE SESSION privilege. Connect Role Privilege Reduction

The connect role privilege reduction feature reduces the number of privileges granted to the connect role to one, the CREATE SESSION privilege. The privileges have been removed from the connect role:








This feature helps you deploy secure configurations by helping enforce the least privileged principle.

1.7.4 Improved Integration and Interoperability

The ability to leverage security with Oracle features, such as Real Application Clusters, is key to deploying secure and scalable solutions. Oracle Label Security has been enhanced in Oracle Database 10g Release 2 to support Oracle Real Application Clusters. This provides customers additional capabilities for protecting sensitive information.

In addition, Oracle Wallets have been enhanced to provide the ability to store username and passwords for command line operations. For example, batch jobs using Oracle SQL*Plus can use a wallet to connect to the database versus having to supply a username and password on the command line.

In addition, Oracle Database 10g Release 2 introduces a new command line proxy capability that allows authorized users to proxy to different accounts upon authentication to the database. Secure External Password Store

Oracle Wallet functionality has been extended beyond PKI credential store to include username and password storage. This enables a user to store his or her username and password in an Oracle Wallet and have the password picked up automatically at connection time. For example, batch jobs using SQL*Plus could type sqlplus / and authenticate using the credentials in the wallet.

Administrators and application developers now have a secure alternative to hardcoding usernames and passwords into scripts and other programs.

See also:

Oracle Database Security Guide for details Enhanced Centralized Password Policies for the Database

This feature enforces password age, disabled accounts, and account lockouts for directory-managed users who access Oracle databases.

This feature allows security administrators to enforce centralized password policies for enterprise users when accessing one or more Oracle databases. It provides for reduced administrative costs as there is a single point of administration. This enhancement allows organizations to better manage security across the enterprise. Oracle Label Security Support for Real Application Clusters

Oracle Label Security support for RAC means that security policies will be synchronized between nodes. Oracle Label Security session security settings can fail over to other nodes automatically.

Oracle customers using RAC can now leverage Oracle Label Security to meet organizational security requirements for protection of sensitive data.

1.8 Grid Computing

The following sections describe new features for Oracle Database 10g Release 2 in the Grid Computing area.

1.8.1 Data Provisioning

This release provides features to simplify data provisioning within a Grid environment, making it easier and faster to archive, move, and copy large sets of database data. These new features enable performing these bulk provisioning operations with no effect on any production database. File Groups Repository

This feature enables storage of point-in-time copies of file and tablespace data for reporting, auditing, and information sharing.

Groups of files (for example, tablespaces) can be copied to the repository, or from the repository to another database or file system, without effecting access to production data. Transportable Tablespace from Backup

This feature provides the ability to create a transportable tablespace without making it read-only.

This enables a DBA to copy or move a tablespace of data using the transportable tablespaces feature without making the tablespace read-only in the source database.

1.8.2 Oracle Scheduler

The Oracle Scheduler has been enhanced to provide additional critical functionality to meet the scheduling needs of enterprise customers. In addition to functionality and usability improvements, jobs can now be chained and scheduled based on external events. Event Scheduling

This feature addresses the need to perform an action based on the occurrence of an event. The Scheduler can perform actions based on external events or actions can be performed based on the Scheduler generated events.

The benefit of this feature is that you can schedule jobs based on events. Job Chaining

A job chain describes a set of jobs that are interrelated. Rules are used to define the dependencies between the jobs.

This feature enables you to define dependencies between jobs. Scheduler Enhancements

This feature enables you to define a custom calendar such as a business fiscal year. Jobs can be scheduled to run on the last work day of every fiscal quarter. This feature also enables the use of rules (for example, Memorial Day is the last Monday of the month of May) to identify dates and save them as a schedule. Existing schedules can be combined to create composite schedules. For example, a HOLIDAYS schedule can be excluded from a DAILY schedule to create a WORKDAYS schedule.

The Scheduler enhancements enable customers to define complex schedules such as the last working day of every fiscal quarter.

1.9 Grid Management

The following sections describe new features for Oracle Database 10g Release 2 in the Grid Management area.

1.9.1 Configuration Plug-ins and Assistants

This release includes enhancements to configuration assistants or plug-ins and post-installation configuration. Enterprise Manager Configuration Plug-in

This feature helps configure Enterprise Manager Database Control with Oracle Database 10g Release 2 Grid Control. EMCA is an executable within the ORACLE_HOME but can also be called as a plug-in from other configuration assistants (for example, DBCA).

The Enterprise Manager Configuration plug-in (EMCA) helps configure Enterprise Manager Database Control under various conditions:

  • Out-of-box configuration using a seed database

  • Out-of-box configuration with a custom database

  • Post-install configuration using DBCA

  • Post-install configuration using custom scripts

  • Migration to Oracle Database 10g Release 2 from earlier versions Migrate Non-ASM Databases to ASM

Automatic Storage Management (ASM) provides simplified storage management within Oracle Database 10g. ASM is integrated with DBCA and Enterprise Manager. DBCA offers functionality to create an ASM instance and configure the disk groups for database creation. Enterprise Manager offers functionality to manage and monitor ASM and disk groups as well as automating the various stages involved in converting an existing non-ASM database onto ASM-based storage.

Enterprise Manager provides an interface to help customers migrate their existing databases onto ASM from non-ASM databases. Standalone Automatic Storage Management (ASM) Configuration

This feature adds a high-level option in DBCA, called ASM Disk Group Management, to create an ASM instance as well as configure disk groups independent of the database configuration.

In Oracle Database 10g Release 1, an ASM configuration using DBCA was supported only as part of database creation. In previous releases, setting up ASM required cancelling out of DBCA after ASM setup was done. In Oracle Database 10g Release 2, ASM Disk Group Management has made creating an ASM instance and configuring disk groups easier. Standalone Disk Group Management

This feature administers ASM disk groups as standalone entities, independent of the creation of a database.

In Oracle Database 10g Release 2 (10.2), you can create an Automatic Storage Management (ASM) instance using an option in the Database Configuration Assistant (DBCA). You can, therefore, prepare an ASM environment for an existing database using the DBCA without concurrently creating a new and unneeded database. However, you can continue to use the DBCA to create a new database and build an ASM instance, if one does not already exist, to manage the new database's storage requirements. Upgrade 10.1 Database to 10.2

The Database Upgrade Assistant guides the DBA through the upgrade process from supported releases of Oracle Database 10g Release 1 to Oracle Database 10g Release 2.

This feature provides a wizard interface to upgrade databases to the new release.

See also:

Oracle Database Upgrade Guide for details

1.9.2 Database Management

This release provides new and improved features to Enterprise Manager that facilitate easier management of the database. Ability to Discover and Update Host Cluster Targets in Enterprise Manager

This feature provides a way to add or configure cluster targets either through auto discovery or manual configuration.

Currently you cannot manually add cluster targets in Enterprise Manager. This feature provides a way to create and configure cluster targets in case cluster discovery fails during installation. Access Advisor Enhancements in Enterprise Manager

Enhancements to Access Advisor include:

  • Template support

  • Quick tune (tune a single statement)

  • Journaling

  • Recommendation implementation status

  • Action implementation status

The Access Advisor user interface in Enterprise Manager has been enhanced to improve the user experience and expose more powerful features of the Access Advisor. Backup and Recovery Configuration Enhancements in Enterprise Manager

Enhancements to Backup include:

  • RMAN script jobs

  • Backup job notifications

  • Recovery catalog configuration

The Backup screens in Enterprise Manager have been enhanced to improve the user experience and expose more powerful RMAN features. Clone External Objects as Part of the Cloning Operation

External object cloning enhancement in the Enterprise Manager database cloning feature ensures that external objects, which are tables stored as external files, get copied over to the destination systems during the clone operation. This ensures that no data is left behind.

The ability to clone external objects extends the database cloning to ensure that all data is cloned, not just objects residing within the database. Database Administration Enhancements in Enterprise Manager

Enhancements to database administration include:

  • Redo log file size advisory

  • All possible actions for an object are provided on the View/Edit object page

  • Search for PL/SQL objects based on object status

  • Delete multiple objects simultaneously

  • Compile multiple PL/SQL objects simultaneously

  • Enable or disable multiple triggers simultaneously

The database administration area has been enhanced through the addition of some minor features that increase the DBA's productivity. Database Auditing Administration in Enterprise Manager

Enterprise Manager provides the following administration capabilities for database auditing:

  • Configuration of audit-related initialization parameters

  • Administration of audited objects: statement auditing and schema object auditing

This feature makes it convenient for the DBA to manage database auditing in the Enterprise Manager Web console, where all other aspects of Oracle Database are managed. Enable or Disable Undo Retention Guarantee

Oracle Database 10g lets you guarantee undo retention. In Enterprise Manager 10g Release 2, you can enable and disable undo retention through the Enterprise Manager user interface. When you enable this option, the database never overwrites unexpired undo data, that is, undo data whose age is less than the undo retention period. This option is disabled by default, which means that the database can overwrite the unexpired undo data to avoid failure of DML operations if there is not enough free space left in the undo tablespace.

By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations. Therefore, use caution when enabling this feature.

A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data. Enhanced Backup Management

Enterprise Manager has added new functionality to Backup Management that provides a single view of all backups run against the databases in a domain. The details of the backup are available by clicking on the specific backup for the database.

By providing a combined view of all database backups and their status, a DBA can quickly pinpoint when a backup job did not complete. A drill down to the log allows the DBA to fix the problem and restart the backup.

See also:

Oracle Database 2 Day DBA for details Enhanced Diagnostics for Cache Coherency Statistics

This release provides better drill down and diagnostics around cache coherency statistics.

Enhanced diagnostics for cache coherency helps users do more diagnostics around the cache coherency statistics. Integrate Segment Advisor with Segment Findings

This feature provides better integration between the Segment Advisor and segment findings which provides a more unified interface with consistent findings and recommendations.

This release supports the new server features provided by the 10.2 Segment Advisor. This feature helps integrate the Segment Advisor with the segment findings. These enhancements help support non-ASSM tablespaces and detection of chained rows in segments. Lock and Unlock Statistics

In previous releases, lock statistics would lock the state of statistics based on object usage in the application. When statistics on a table were locked, all the statistics depending on the table, including table statistics, column statistics, histograms, and statistics on all the dependent indexes were locked.

For Oracle Database 10g Release 2, the database server provides the lock and unlock feature in the optimizer statistic area. Enterprise Manager provides support for these features with user-friendly wizards to help assist DBAs with the operations.

You can now lock the statistics to a state where you know the statistic is good and will not change. For example, you can collect statistics for a volatile table while it is fully populated and freeze the statistics as a set of representative statistics.

You can also lock the state of the statistic when there are no statistics. This is useful to keep the statistics on a table uncollected in order to use Dynamic Sampling.

When you no longer want the statistics to be locked, use the Unlock wizard to unlock the statistics. Management of Directory Objects in the Enterprise Manager Web Console

This feature provides management of Directory Objects in Enterprise Manager including search, view details, and life-cycle operations such as create, delete, and update.

The DIRECTORY object facilitates administering access and usage of BFILE data types. It is convenient for the DBA to manage Directory Objects in the Enterprise Manager Web console where all other aspects of Oracle Database are managed. Miscellaneous Undo Advisor User Interface Enhancements

The following enhancements have been made in this release:

  • The required undo tablespace size is computed as the maximum of the auto tuned value of undo retention and the low threshold value.

  • In the Undo Advisor page, the field name "New Undo Retention" has been changed to "New Low Threshold Undo Retention."

  • "Undo Retention (minutes) No Recommendation" is displayed if auto tuning is disabled. It is not displayed if auto tuning is enabled (the default).

  • In the Undo Management page under the System Activity and Tablespace Usage, the default heading has changed to "Last One Hour" from "Last Seven Days".

This feature provides some user interface-related enhancements to the Undo Advisor pages. New Storage and Configuration Policies

For Oracle Database 10g Release 2, the database Storage and Configuration policies have been enhanced as follows:

  • Storage Policy: Database default temporary tablespace not set

  • Storage Policy: Database permanent tablespace not set

  • Storage Policy: Tablespace not using Automatic Segment Space Management

  • Config Policy: Not using Dynamic SGA and PGA

  • Config Policy: Not using the latest optimizer version

  • Config Policy: STATISTICS_LEVEL parameter not TYPICAL

This feature enhances the existing storage and configuration policies by adding new database storage and configuration policies and consolidating any overlap in the existing policies.

This feature also enhances some existing metrics. The storage policies help the end user ensure that their tablespaces follow best practice rules. For example, they help the user identify dictionary-managed tablespace or tablespace that contains mixed data segments. This helps the end user in correctly setting up tablespace storage, thus avoiding space and performance problems.

The configuration policies check basic database settings to ensure the end user is taking advantage of the latest database features. These policies also help ensure that the databases are configured in the manner recommended by Oracle.

By adding this policy support, Enterprise Manager becomes more competitive in the marketplace. Oracle Spatial Administration in the Enterprise Manager Web Console

Management of Oracle Spatial in Enterprise Manager includes creation and management of spatial tables, metadata, and spatial indexes.

Oracle Spatial provides a SQL schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial features in an Oracle Database. It is convenient for the DBA to manage Oracle Spatial in the Enterprise Manager Web console where all other aspects of Oracle Database are managed. Performance Monitoring for Large Clusters

This feature incorporates additional cache coherency monitoring and improves scalability for large clusters. This feature also provides general improvements in RAC database performance monitoring.

The performance monitoring capabilities have been expanded for RAC environments. RAC DB Home pages scale for 64-128 nodes/instances. There is additional monitoring of cache coherency for RAC instances.

The presented performance data is more meaningful, performance problems are easier to detect, and helps identify the root cause of cluster database performance issues. Restore Statistics From Historic Snapshots

The Restore Statistics wizard enables restoration of statistics of the databases or their related objects to a specified timestamp in the past within the retention period.

For Oracle Database 10g Release 2, the database server provides the restore statistics feature in the optimizer statistic area. Enterprise Manager provides support with a user-friendly wizard to help assist DBAs with the operations.

If the statistics collected in the management window have caused a sub-optimal plan to be generated, you can revert to past statistics using the Restore Statistics wizard. Restore and Recovery Enhancements in Enterprise Manager

Enhancements for Restore and Recovery include:

  • Recover in context of new Oracle home

  • Enhanced Guided Recovery wizard

  • Dry-run restore

Restore and Recovery operations have been further automated in Enterprise Manager. Segment Advisor to Work on the Whole Database

This feature helps support a predefined Segment Advisor task for the entire database.

This feature can be scheduled in a maintenance window. Show Segment Details of Tablespace

For a selected tablespace, this feature displays the number of segments, segment names, segment types, number of extents, total size of extents for each segment (in blocks), the maximum number of extents that can be allocated to each segment, and the percent of the tablespace used.

The ability to display more details on tablespace segments will ease administration. Statistics Collection for Fixed and Dictionary Objects

This feature supports Oracle Database 10g new feature of statistics collection for fixed and dictionary objects.

For Oracle Database 10g Release 2, the database server provides the capability to gather statistics for fixed and dictionary objects in the optimizer statistic area. Enterprise Manager provides a user-friendly wizard to help assist DBAs with the operations through the Gather Statistics wizard.

If your database suffers from performance issues due to out-of-date statistics for fixed and dictionary objects, use the 10.2 Enterprise Manager Gather Statistics wizard to allow statistics collection on these objects. Tabular Display of Tablespace Segments

This feature displays a table of segments with the ability to sort based on individual columns.

The benefit of this feature is an improved display of tablespace segments and the ability to sort into a tabular format. Track Interconnect Information Statistics

Now, you can collect and display additional cluster target properties including interconnect information statistics.

This feature allows Enterprise Manager to monitor the interconnect on a RAC instance. Use RMAN Compression During Cloning

RMAN has a compression capability built in, and Enterprise Manager has been enhanced to offer the use of this capability within the database cloning feature.

The ability to use RMAN compression during database clone operations reduces the network bandwidth requirements for database cloning operations. This benefits anyone who is cloning over low bandwidth network links, and saves disk space when the database clones are saved for later reuse. Wizard-Based Loading of Flat Files in Enterprise Manager

The Enterprise Manager Load Data wizard helps you specify a data file and then steps through some simple screens where you can provide information about the structure of the file (both character delimited and fixed length files are supported). Enterprise Manager uses the supplied information to automatically generate a SQL*Loader control file, and subsequently creates a SQL*Loader job for loading the data file into the database.

SQL*Loader is a powerful and scalable tool for importing data from files into Oracle Database. However, the process of creating SQL*Loader control files is time-consuming and error-prone. The Enterprise Manager Load Data wizard automates this process, thereby making the data loading capabilities of SQL*Loader more accessible.

See also:

Oracle Database 2 Day DBA for details

1.9.3 Patching

These features include enhancements to the patching tool used to patch databases and application server software. OPatch the Patching Tool

OPatch is a tool for patching Oracle Database that is available from Oracle9i Release 2 and later and Application Server (starting ORACLE_HOMEs. It also serves as a backend of Enterprise Manager patching.

This feature helps you apply interim patches to your environments. OPatch serves as the backend for Enterprise Manager patching and can therefore achieve large scale patching in Grid environments.

1.9.4 Software Install and Cloning

This release includes enhancements to Oracle Universal Installer (OUI) framework and individual product installs that are used for deploying Oracle software. Improve Automatic Storage Management (ASM) Installation Process

The improved install process with Database Configuration Assistant (DBCA) and Installer can now place ASM in its own ORACLE_HOME and then configure ASM disk groups. Upon completion, you can run additional Oracle Universal Installer (OUI) sessions to install and configure one or more Oracle databases in separate ORACLE_HOMEs (or database environments) that would then use ASM.

Using the new installation process, you can set up the recommended configuration when more than one database instance runs on a single server or node (rather than having the ASM instance and a database instance share a single ORACLE_HOME). The improved installation process makes it easier to manage multiple database environments on a single server. Real Application Clusters Cloning

This feature provides the ability to clone RAC nodes to add a node to the cluster or to build a new cluster.

The RAC deployment model supports simple cloning mechanisms for extending RAC horizontally. The ability to clone and extend RAC horizontally is the key element of the Grid message. Real Application Clusters Install Framework

The RAC install framework includes framework application programming and command-line interfaces that are needed for Oracle Clusterware and RAC installations on a new cluster or onto a newly added node to an existing cluster.

This feature helps you to provision RAC software and add nodes to an existing RAC environment with minimal manual effort. Silent Install for Real Application Clusters

In this release, Real Application Clusters supports silent installation and operations, and the add note feature.

Silent operations enable you to use non-interactive installs. The add node feature is critical for increasing and decreasing capacity on demand. In summary, RAC provides a simple, scalable, and stable deployment framework.

1.10 Information Integration

The following sections describe new features for Oracle Database 10g Release 2 in the Information Integration area.

1.10.1 Oracle Streams Performance and Usability Improvements

These features provide functionality and performance enhancements, as well as usability improvements, to make Oracle Streams the most powerful, highest performing, and easiest-to-use information sharing solution for Oracle data. Streams Performance

The following Streams components have been improved:

  • Capture processes

  • Propagation

  • Apply processes

This feature improves the performance of Streams. Support for Hot Mining With Downstream Capture

Downstream capture, the ability to off load the capture process from the production source database, can now be configured for near real-time processing (hot mining) of the primary source redo logs at the alternate database.

A capture process can run on a database other than the source database. The redo log files from the source database are copied to the other database, called a downstream database, and the capture process captures changes in these redo log files at the downstream database. To improve the performance of certain configurations, downstream capture can now be configured for near real-time processing (hot-mining) of the primary source redo logs at the alternate database. Buffered Messaging

This feature enables non-persistent message queuing for user applications. This will enable you to build high performance message queuing applications.

This feature provides a faster, less resource-intensive queuing implementation. Commit-Time Ordering of Messages

This feature enables you to use commit-time to control the order in which user-enqueued events in a queue are browsed or dequeued.

Commit-time queues are useful when your environment needs to support transactional dependency ordering during dequeue, or when you need to support consistent browsing of events in a queue. Apply Handler Dependency Declaration

Oracle Streams can apply changes made at the source site in parallel at the destination site while ensuring that dependent changes are applied in the same order as they were done at the source site. Occasionally, there are complex dependencies among tables that cannot be specified through database objects (such as referential constraints).

This feature enhances Oracle Streams to allow for the specification of these complex dependencies and the enforcement of these dependencies when applying the changes in parallel. Simplified Large Object (LOB) Handling in Streams Apply Process

With this feature, DML Handlers can receive LOB logical change records (LCR) as a single column, rather than iterating through multiple LOB LCRs (LOB chunks) for the column.

This feature improves ease-of-use by simplifying access to LOBs within a customized Streams apply process. Declarative LCR Transformations

The declarative logical change record (LCR) transformation feature enables simple configuration for DML LCRs of the most common transformation scenarios.

The ability to rename columns of a table, tables, or the table owner (schemas), as well as adding or removing columns from the LCR, is enabled external to user-supplied PL/SQL functions. Notification Enhancements

Oracle Database 10g Release 2 provides advanced notification features to support cache invalidation.

This feature extends the notification capabilities available with Oracle Streams Advanced Queuing. Oracle Java Message Service (OJMS) Nonpersistent Messaging

This feature utilizes new AQ extensions and exposes "true" nonpersistent messaging through OJMS APIs. Previously, nonpersisent messaging was implemented on persistent, AQ messaging.

The benefit is improved performance and functional correctness for OJMS nonpersistent messaging. Simplified Configuration APIs

In this release, common tasks such as performing an application upgrade or database migration has been simplified.

This feature improves ease-of-use by providing simplified API's for common tasks such as performing an application upgrade or database migration. Also, it completes RAC transparency by providing queue-to-queue propagation. Oracle Streams Administration in the Enterprise Manager Web Console

Enterprise Manager now supports the configuration, administration, and monitoring of Streams in Oracle Database.

This feature makes it convenient for the DBA to manage Oracle Streams in the Enterprise Manager Web console, where all other aspects of Oracle Database are managed.

1.10.2 Improved Client/Server and Server/Server Communications

Client/server and server/server communications have been improved in this release. Notification of failures will speed application failover providing better availability. Other improvements provide streamlined and more robust communications. Local Naming Support for JavaNet

This release provides local naming capability, using tnsnames.ora, for JavaNet. With this feature, you can use either tnsnames.ora or LDAP naming adapters.

The benefit of this feature is that JavaNet performs name lookups the same as the C Net stack. This makes the system administrator's work easier and more consistent when configuring both JavaNet and C Net stack.

Support will continue for the current LDAP resolution that requires you to specify the LDAP server URL and DN in the CONNECT string. However, now you can use an alias for LDAP support which is the same as for the C Net stack. Oracle Streams AQ Administration in the Enterprise Manager Web Console

Enterprise Manager provides the following administration features for Oracle Streams Advanced Queuing (AQ):

  • Manage queue tables

  • Manage queues

  • Manage transformations

Oracle Streams AQ provides database-integrated message queuing functionality. It is now convenient for the DBA to manage Oracle Streams AQ in the Enterprise Manager Web console where he manages all other aspects of Oracle Database. Transparent Application Failover (TAF) Notification

Failover will now be instantaneous, regardless of the state of the client when the failure occurs.

In the event of a failure of a database instance, the server will send a notification to clients to initiate failover.

1.11 Location Services

The following sections describe new features for Oracle Database 10g Release 2 in the Location Services area.

1.11.1 Extend Business Geographics Features to Support Europe

This release extends the Spatial routing engine and geocoder to work with European countries and improve the quality and robustness of these features (including Spatial Analysis and Mining). Oracle Spatial Routing Engine Support for Western Europe

The Spatial routing engine introduced in Oracle Database 10g Release 1 has now been extended to provide driving distances, times, and directions between addresses for over a dozen Western European countries, including (but not limited to) Germany, United Kingdom, and France.

The Spatial routing engine enhancements enable logistics, transportation, and location-based services applications to build driving direction services for over a dozen Western European countries.

See also:

Oracle Spatial Developer's Guide for details

1.11.2 Extend Coordinate System in Core Spatial

This release supports the EPSG-based coordinate systems model for the Oil & Gas Industry. Oracle Spatial Support for EPSG Coordinate Systems

Oracle Spatial now supports the European Petroleum Survey Group (EPSG) coordinate systems definitions.

Oracle Spatial coordinate systems support the European Petroleum Survey Group (EPSG) data model and data set that is essential for oil and gas exploration and production systems. EPSG support provides benefits of standardization, expanded support, and flexibility for oil and gas companies, georaster data vendors, and GIS users in general.

See also:

Oracle Spatial Developer's Guide for details

1.11.3 GeoRaster Compression Standards

This release supports industry standard compression techniques for Spatial GeoRaster. Oracle Spatial GeoRaster Compression

Spatial GeoRaster supports industry standard compression techniques for raster image data, including the JPEG baseline (lossy) and DEFLATE (lossless) standards. All GeoRaster functions now work on both compressed and uncompressed images.

Remote sensing imagery results in very large data sets, growing at the rate of a terabyte or more for each day. The capability to store and manage these images in compressed form is key for users and DBAs alike. You save money on storage costs when image sizes are reduced by up to 80 percent. This is essential for defense/security, agricultural, and environmental monitoring applications.

1.11.4 Improved Performance of Network Data Model

This release provides performance and scalability improvements to support analysis on very large networks. Graph Partitioning for Very Large Networks in Oracle Spatial

The size of a network analyzed using Oracle Spatial network data model is no longer limited by the amount of physical memory available. This release includes graph partitioning, which divides a large network into logical partitions based on partition ID. Logical partitions can be incrementally loaded into memory for analysis.

Oracle Spatial network data model graph partitioning creates scalability that is independent of physical memory. Applications with large networks such as utility networks, street networks, and biological pathway data in life sciences will benefit from this important enhancement.

1.11.5 Improved Topology Operations

This release improves the performance, quality, and robustness of Spatial Topology according to the requirements of US CENSUS. Whole Feature Inserts and Updates for the Oracle Spatial Topology Data Model

The Spatial topology data model has been extended to support feature-level spatial transactions against persistent topology in the database. In the previous release, multiple operations were required to insert or update all of the node, edge, and face elements of a feature. Now, a feature insert or update occurs as a single operation.

Whole feature-level inserts and updates dramatically reduce the code required for updating and maintaining topology data sets. This meets the need of major mapping agencies and land management applications.

1.12 Performance and Scalability

The following sections describe new features for Oracle Database 10g Release 2 in the Performance and Scalability areas.

1.12.1 Improved Large Object (LOB) Performance

Oracle Database 10g Release 2 includes performance optimizations for commonly used features such as LOBs, PL/SQL, and Index Organized Tables. Performance gains are obtained by redesign and code streamlining and do not require changes in customer applications using these features, unless explicitly stated otherwise. LOB Access Statistics in Dynamic Performance Views

Dynamic performance views are essential tools for database administrators to monitor and tune database performance. With the additional LOB access statistics in dynamic performance views, database administrators are offered a clear picture of LOB access activities.

This feature provides LOB access statistics in dynamic performance views. Online Redefinition Supports Parallel Execution for LONG-to-LOB Migration

Online Redefinition now performs parallel execution when converting from the LONG data type to the LOB data type on non-partitioned tables.

The benefit is reduced maintenance time required to convert to the LOB data type. This is especially beneficial to users of SAP and Oracle Applications. Performance Improvements for Loading LOBs

Direct path load in SQL*Loader for out-of-line LOBs has been improved significantly.

Depending on the sizes of out-of-line LOBs, LOB loading performance has improved up to five times faster than in previous releases.

1.12.2 Other Performance Improvements

Oracle Database 10g Release 2 includes a number of additional performance optimizations for long running queries, background activities, and other commonly used features. Asynchronous Commit

Asynchronous commit allows database clients to do more work while the database writes the redo log to disk.

This feature improves transaction throughput by reducing database commit time. Checkpointing Improvements

In Oracle Database 10g Release 2, improvements have been made to checkpointing.

This feature offers better performance and easier configuration of checkpointing. Database Change Notification

You can now receive notification whenever a change occurs in the database on the result set of registered queries.

This feature enables any cache or object holding query results to ensure that it contains the very latest data. Fast Partition Split for Partitioned Index Organized Tables

The SPLIT command is used to split a single partition of an index organized table into two partitions. In earlier releases, the SPLIT command moved each of the rows from the existing partition into one of the two new partitions.

Index organized table performance is improved when a partition of an index organized table is split into two partitions. Rows are not moved if all of the rows in the existing partition fall into one of the new partitions. Improved Aggregation Performance

This feature provides enhancements to aggregation performance.

The benefit is faster execution of SQL operations containing aggregations. Parallel Join Bitmap Filtering

This feature reduces amount of data sent by the right side of a join based on the bitmap created by left side of a join.

This improves parallel join performance, especially on RAC, by reducing the amount of data traffic.

1.13 Server Manageability

The following sections describe new features for Oracle Database 10g Release 2 in the Server Manageability area.

1.13.1 Database Replay

Before making changes, such as hardware or software upgrades, companies typically conduct extensive testing to validate the changes. However, when the change is made on the production system, unexpected problems are often encountered because the testing was not performed with a workload that was a good representation of the production environment. Tools are available in the market to construct "synthetic workloads" involving multiple users. However, they are not able to adequately simulate the complexities of a real-world workload, such as interactions between concurrent activities and unpredictability of the workload profile.

The Database Replay feature addresses this need by enabling users to perform real-world testing by capturing the actual database workload on the production system and replaying it on the test system. It also provides analysis and reporting to highlight potential problems (for example, errors encountered and divergence in performance) and recommend ways to remedy the problems. In this release, you can begin to perform real-world testing by capturing the actual database workload on the production system. The ability to replay the workload, however, is only available in Oracle Database 11g (11.1). This has been done so that when you upgrade from Oracle Database release to 11.1, you can use the Database Replay feature to help with the upgrade.

1.13.2 Simplified Configuration

A number of new features in database and Enterprise Manager make initial database creation and setup even easier. Building on the work done in Oracle Database 10g Release 1, Oracle Database 10g Release 2 automates the configuration of more initialization parameters, such as multiblock read count. In addition, Enterprise Manager can now easily configure and manage a wide variety of advanced net services components, such as advanced security, network data encryption, and Transparent Application Failover (TAF). Automatic Undo Retention Enhancement

This feature tunes undo retention to provide maximum retention for a fixed size undo tablespace.

This feature tunes the system to provide maximum undo retention for the fixed size undo tablespace. This further simplifies Automatic Undo Management, as you no longer need to configure the UNDO_RETENTION parameter even for flashback purposes. Net Services Administration in the Enterprise Manager Web Console

Enterprise Manager can now be used to manage the following Net Services components and features:

  • Configuration of Oracle advanced security strong authentication.

  • Configuration of network data encryption and integrity for Oracle servers and clients.

  • Configuration of secure socket layer authentication.

  • Transparent Application Failover (TAF) configuration.

  • Search functionality for Listeners and Net Service names.

  • Editable location of tnsnames.ora for import to Oracle Internet Directory.

  • Listener target support for IMAP and POP presentation.

  • Rename Listeners.

  • Rename Net Services names.

Oracle Net Services provides enterprise-wide connectivity solutions in distributed, heterogeneous computing environments. It is convenient for the DBA to manage the various components of Oracle Net Services in the Enterprise Manager Web console, where all other aspects of Oracle Database are managed. Self-Tuning Multiblock Read Count

The DB_FILE_MULTIBLOCK_READ_COUNT parameter controls the amount of block prefetching done in the buffer cache during scan operations, such as full table scan and index fast full scan. The value of this parameter can have a significant impact on the overall database performance. This feature enables Oracle Database to automatically select the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.

This feature simplifies manageability by automating the tuning of DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.

1.13.3 Improved Intelligent Infrastructure

Enhancements to Intelligent Self-Management Infrastructure enable more accurate collection of workload and performance statistics, allow quick detection of causes behind the change in system behavior across two comparable time intervals, and make it possible to easily diagnose transient performance problems. Additionally, Enterprise Manager now allows monitoring and troubleshooting of slow or "hung" systems by retrieving necessary information directly from SGA whenever a normal connection is not possible. The alert thresholds can now be configured to automatically adapt to changes in system behavior thanks to Enterprise Manager's new dynamic baselining capability. Adaptive Alert Thresholds in Enterprise Manager

Enterprise Manager now computes statistical alert thresholds using either static (user-defined) or dynamic (self-adjusting) baselines.

Dynamic statistical baselines can significantly improve the accuracy of performance alerting while also reducing exposure to the false positives commonly incurred under fixed threshold schemes. Database Performance Monitoring Enhancements in Enterprise Manager

The enhancements for Performance Monitoring include:

  • System-level activity for any duration in real-time, recent real-time, and historical time

  • CPU-level and Wait Class-level activity for any duration in real-time, recent real-time and historical time

  • Ability to run Automatic Database Diagnostic Monitor (ADDM) on demand

  • Display of Alert thresholds in the Performance pages

These enhancements greatly increase the ability of a DBA to quickly detect performance problems both proactively (using alerts, real-time data, and ADDM) and reactively (using recent real-time, historical data, and ADDM). Diagnosis of Hung or Extremely Slow Databases Through Enterprise Manager

Enterprise Manager provides screens for monitoring slow systems. These screens are populated using the Direct SGA Attach mechanism. An intelligent processing layer transforms the collected raw statistics into useful information that can be used by DBAs to efficiently perform root-cause analysis and take appropriate action.

This feature makes it easier for DBAs to manage databases. In particular, it facilitates analysis of slow systems, and detection and analysis of hung systems. In both cases, the feature provides access to diagnostic information that would otherwise be impossible or extremely difficult to retrieve. Display Session Activity History in Enterprise Manager

Enterprise Manager now provides the ability to view the history of database session activity for both realtime (less than one hour) and historical time contexts.

This feature greatly increases the ability of a DBA to diagnose problems related to a particular database session, as it permits the DBA to go back in time and view the history of the session activity. Performance Graph Normalization in Enterprise Manager

Enterprise Manager now provides the ability to normalize database performance graphs by metric baseline statistics.

When performance information is normalized by the baseline context, it makes it easier for the DBA to detect if something unusual is happening. The definition of "unusual" depends on using a baseline as the measure of "normal." Read Performance Statistics Directly From the System Global Area (SGA)

Enterprise Manager can now collect key performance statistics of the database without making SQL queries. This involves attaching to the SGA directly and reading statistics from the shared memory. The data collected by these direct SGA reads is used to populate the Enterprise Manager database performance screens.

When a database is hung or slow, it is typically facing severe contention in resources. Using SQL to collect performance statistics at such a time is not acceptable. Cleanup System Statistics

This feature rationalizes some of the system statistics especially those concerning I/Os.

The benefit of this feature is easier performance tuning. Enhanced Active Session History

This feature provides additional Active Session History (ASH) information to find blocker and waiter identities and their associated transaction IDs and SQL.

This feature simplifies manageability by providing a more accurate view of recent system activity. This information helps DBAs investigate transient problems due to waiters or blockers that are not detected during a periodic performance analysis. Enhanced Automatic Database Diagnostic Monitor (ADDM) Performance Analysis

The intelligent self-management infrastructure has been enhanced to make Automatic Database Diagnostic Monitor (ADDM) analysis more accurate in the areas related to CPU, paging, and integrated cache. In addition, the scope of ADDM has itself been broadened to include more server components such as Streams, AQs, RMAN, and RAC. These enhancements allow ADDM to detect performance bottlenecks more accurately and provide better recommendations to resolve them.

This feature enhances manageability by making the database resident, self-diagnostic engine more powerful and accurate. Progress Monitoring for Advisors

In this release, the advisor framework has been enhanced to include APIs for progress monitoring of advisors.

You can now externally monitor the progress of advisor tasks execution by querying the V$ADVISOR_PROGRESS view. Enhanced End-to-End Performance Monitoring

This feature enhances the End-to-End Performance Monitoring functionality introduced in Oracle Database 10g Release 1. The usability of SQL tracing has been improved by allowing a complete programmatic access at the session level. In addition, the sessions with tracing enabled can now be identified from V$SESSION view and a new dynamic view DBA_ENABLED_TRACES has been introduced to view the database and instance level settings. Also, a new procedure has been added to DBMS_MONITOR package to provide a single switch for enabling and disabling SQL tracing for all sessions.

This feature enhances manageability by adding ease-of-use functionality to the End-to-End Performance Monitoring feature introduced in Oracle Database 10g Release 1. Active Session History (ASH) Report

This feature uses Active Session History (ASH) data to analyze the performance of the system based on activity in the last few minutes.

This feature enhances manageability by making it easier to diagnose transient performance problems. This feature is intended to help administrators diagnose transient problems that are not detected during a period performance analysis. Automatic Workload Repository (AWR) Compare Periods Report

This feature allows users to generate Automatic Workload Repository (AWR) reports identifying differences between two pairs of snapshots. This report is helpful to users in better understanding changes in workload and correlating it to performance problems pointed out by ADDM or validating tuning actions as a result of implementing ADDM's recommendations.

This feature enhances manageability by enabling simpler and more accurate performance diagnosis. Configurable AWR SQL Collection

This feature allows users to customize how the top resource consuming SQL (TOP N SQL) statements are collected in the Automatic Workload Repository (AWR). By default, Oracle uses a set of predefined values to determine how many SQL statements should be collected for different measurement criteria. You can now change these values or direct AWR to capture all the statements in the cursor cache.

This feature enhances manageability by providing a more flexible mechanism to track the top resource-consuming SQL statements. Using this feature, administrators can capture a large number of SQL statements for historical performance trending and analysis purposes. This feature is also useful for SQL Workload performance comparison across major change activities such as database or application upgrade.

1.13.4 Automatic Storage Management (ASM) Manageability

This release expands the manageability capabilities for Automatic Storage Management (ASM) to both the GUI and command-line interface to provide the database administrator with greater flexibility. ASM Command-Line Interface

The ASM Command-Line Interface (ASMCMD) utility provides an easy alternative for accessing the files and directories within ASM diskgroups.

Adding an additional means by which to access and manage files within ASM makes it simpler to manage. The ASMCMD interface provides easy manipulation of files within Automatic Storage Management (ASM) disk groups.

See also:

Oracle Database Utilities for details ASM Wait on Rebalance Process

ASM ALTER DISKGROUP command, which results in a rebalance, now has the option of specifying the option to WAIT. This enables multiple actions to be done at one time with the rebalance process not starting until a later time. The Power_Limit can also be set to a value of 0 which will result in a wait before rebalance is started.

This makes it easier to manage the disks and rebalance process in ASM. The WAIT keyword allows a script that adds or removes disks to wait for the disk group to be rebalanced before proceeding. Additional ASM Manageability Enhancements

Additional ASM enhancements provide information about the status and space usage of ASM to the DBA. Adding two new fixed V$ASM views provides more access for Enterprise Manager to also reflect status and usage of disk space.

These enhancements make it easier to monitor and manage ASM disks. Enhancements to existing views and Enterprise Manager make information about check disk group and free space more concise and easier to understand. Batch Selection of Multiple Disks for Operations Like DELETE, RESIZE, and CHECK

This feature allows multiple disks to be selected for DELETE, RESIZE, and CHECK operations.

The batch DELETE operation prevents the unnecessary rebalancing that occurs when disks are deleted one by one. It also improves usability and enhances the existing support in Enterprise Manager for ASM.

1.13.5 Automatic Storage Management (ASM) Extensions

This release expands the capabilities of ASM to support mixed Oracle environments. For example, support for multiple Oracle Database versions and support for multiple database-to-ASM connection types. ASM Support for Multiple Database Releases

When mixing software releases, Automatic Storage Management (ASM) functionality reverts to the functionality of the lowest release being used. For example, a database talking to a ASM instance will not be able to exploit any new features in and behaves as if it were talking to a ASM instance. Conversely, a database talking to a ASM instance behaves as if it were a database.

Two new text columns, SOFTWARE_VERSION and COMPATIBLE_VERSION, have been added to V$ASM_CLIENT.

This new support provides more flexibility for ASM in a multirelease database environment. To ensure that ASM transparently supports both older and newer software releases of the database, both forward-compatibility and backward-compatibility are maintained between all Oracle Database 10g Release 1 and all Oracle Database 10g Release 2 releases, and for as long as possible with later releases. Enhanced Protocol Support for Database to ASM Connection

This release provides enhanced protocol for connections between the database and Automatic Storage Management (ASM) for greater connectivity and compatibility. This also allows one clusterwide ASM instance to manage storage for many databases using several types of connection mechanisms.

The benefit is greater flexibility for ASM to manage a clustered pool of storage. Allowing more compatibility for database to ASM connections enables easier consolidation and compatibility of shared storage with a cluster.

1.13.6 File Support in Automatic Storage Management (ASM)

This release provides a user interface that allows the upload and extraction of files into or out of an ASM managed storage pool. ASM/XDB FTP Support

This feature adds new functionality for both XDB and ASM providing a user interface for getting files into and out of an ASM managed pool of storage.

This feature extends ASM to support environments that require movement of application data.

1.13.7 Enhanced Automatic Database Diagnostic Monitor (ADDM)

The self-diagnostic engine of Oracle Database 10g, Automatic Database Diagnostic Monitor (ADDM), has been enhanced to make its analysis more accurate in the areas related to CPU, Paging, and Integrated Cache. In addition, the scope of ADDM has itself been broadened to include more server components such as Streams, AQ, RMAN, and RAC. Automatic Shared Memory Management of Streams Pool

When Streams is used in a single database, memory is allocated from a pool in the System Global Area (SGA) called the Streams pool. The Streams pool contains buffered queues and is used for internal communications during parallel capture and apply. The automatic shared memory management feature is enabled by setting the SGA_TARGET parameter.

The Streams pool is now an automatically sized SGA component to improve manageability.

1.13.8 Enhanced Instance Tuning

Automatic Instance Tuning capabilities of Oracle Database 10g have been further strengthened by enhancements made to Automatic Shared Memory Management and Database Resource Manager features. The Automatic Shared Memory Management feature now automatically manages the Streams pools component of the System Global Area (SGA). Also, the Database Resource Manager is now enabled out-of-the-box and new enhancements have been made to simplify its monitoring and tuning. Automatically Enabled Resource Manager

Starting with this release, the Database Resource Manager will be enabled by default.

This feature enhances manageability by automatically activating the Database Resource Manager. Enhanced Ability to Diagnose Memory Allocation

Oracle Database provides a memory manager to internal subsystems for their dynamic memory allocation needs. Virtually all allocation and deallocation requests include a comment which can be used to maintain statistics, and which Oracle Support Services can use to match memory usage to source code. This feature improves the ability to diagnose memory leaks and out-of-memory errors by maintaining more information, making component usage visible to DBAs and developers, and allowing Oracle Support Services to see memory usage in a more understandable format.

This feature enhances the information needed and improves the ability to diagnose memory allocation by Oracle Support Services. Enhanced Automatic Shared Memory Management

The Automatic Shared Memory Management feature now automatically sizes the Streams pool based on workload demand. The internal algorithm of the feature has also been enhanced to make the transfer of memory across SGA components more effective.

Enhancements to the Automatic Shared Memory Management feature further strengthen the manageability enhancements introduced in Oracle Database 10g Release 1. Enhanced Resource Manager Monitoring

A number of new enhancements are being introduced to help assess the effectiveness of Database Resource Manager and tune Resource Plans if required. The Database Resource Manager is now fully integrated with the database self-management infrastructure (for example, ADDM, Automatic Workload Repository and Active Session History, Server Generated Alerts) in order to benefit from the self-management capabilities of Oracle Database 10g. In addition, a number of new statistics have been introduced and it is now possible to monitor the functioning of Database Resource Manager at the session level.

These features enhance manageability by making it easier to monitor and tune the functioning of the Database Resource Manager.

1.13.9 Enhanced SQL Manageability

Oracle Database 10g Release 2 further enhances the Automatic SQL Tuning capability introduced in Release 1. SQL Profiles can now be shared among SQL statements having different literal values and SQL Tuning Sets can now be transported across different databases to enable application performance comparison during upgrade testing. SQL Access Advisor has also been enhanced to make it interruptible and provide recommendations about function-based indexes. Transportable SQL Tuning Sets

This feature provides the capability to import and export SQL Tuning Sets (STS) from one system to another.

This feature allows the transfer of SQL workloads from one database to another for remote performance diagnostics and tuning. When poorly performing SQL statements are encountered on a production system, you may not want to give developers direct access to it for the purpose of investigation and tuning. This feature allows the DBA to transport the offending SQL statements to a test system where they can be analyzed and tuned. Display SQL History in Enterprise Manager

Enterprise Manager provides the ability to view the execution history of a SQL statement.

This feature greatly increases the ability of a DBA to diagnose problems related to a particular SQL statement, as it permits the DBA to go back in time and view the history of the SQL statement's execution plans. Interruptible SQL Access Advisor

The SQL Access Advisor may be interrupted while generating recommendations allowing the results to be reviewed.

The benefit is that recommendations can be viewed immediately rather than having to wait for the process to complete, which could be long for a complex database and workload. SQL Access Advisor Recommends Function-Based Indexes

The SQL Access Advisor now recommends function-based indexes.

This enhancement provides more indexing types recommended by SQL Access Advisor. SQL Profile Enhancements

This feature allows SQL Profile to match SQL text after literal values have been normalized into bind variables.

SQL Profile has been enhanced to allow applications to use literal values rather than bind variables. SQL Statistics Enhancement

A new view, V$SQLSTATS, has been added in this release.

This view has been added for improved performance, monitoring, and diagnostics. Timeout for Flushing SQL Statistics

This feature updates SQL statistics every 5 seconds for a SQL statement that is still in progress.

This feature makes it easy to monitor long running SQL statements that are still in progress by keeping SQL statistics up-to-date.

1.13.10 Enhanced Space Management

Space management operations have been further simplified in Oracle Database 10g Release 2 as it can automatically identify tables and indexes that can be compressed to reclaim wasted space. It is now possible to drop empty datafiles and the Automatic Segment Space Management (ASSM) feature is now enabled by default. Default Automatic Segment Space Management (ASSM)

In this release, Automatic Segment Space Management (ASSM) is enabled by default. The tablespace attribute, Segment Space Management, now has the default value of AUTO.

All new tablespaces created using default attributes benefit from the manageability and performance advantages of ASSM over Freelist-based space management. Automatic Segment Advisor

Automatic Segment Advisor proactively identifies segments on a nightly basis that have significant wasted space due to data fragmentation and therefore are good candidates for Online Segment Shrink.

The key benefit is that the database administrators are proactively made aware of segments that need reorganization. They do not need to run Segment Advisor to determine what segments have data fragmentation. The advice is already available on the Enterprise Manager home page for immediate implementation. Online Segment Shrink Enhancements

This feature extends Online Segment Shrink capability to large object (LOB) segments and index-organized table (IOT) overflow segments.

Online Segments Shrink capability is now extended to all types of segments. This will further reduce the need to perform other more advanced reorganization methods such as Online Redefinition. Size-Based Tablespace Space Usage Alerts

This feature provides the ability to specify space pressure alerts based on the number of free bytes in the tablespace. Oracle Database 10g Release 1 provided alerts based on the percentage of the tablespace that was full; with the default being 85% for warning and 97% for critical alerts. For large tablespaces, you may want to specify thresholds in absolute values since a tablespace that is 97% full may still have substantial free space available which does not warrant an alert.

With this feature, DBAs can exactly specify how much free space must be available in the tablespace before they get an alert. Drop Empty Datafile

This feature provides the ability to drop an empty datafile. It drops the datafile from the database and deletes it from the file system.

If a DBA adds a datafile in the wrong location or to the wrong tablespace, this feature provides a simple way to correct the error by dropping the datafile.

1.14 Windows

The following sections describe new features for Oracle Database 10g Release 2 in the Windows area.

1.14.1 Improved Availability and Scalability With Oracle Real Application Clusters

ODP.NET has been enhanced with greater ability to intelligently use RAC. When optimizing their application, administrators can allocate the percentage of database connections for each instance, as well as have Oracle automatically detect downed instances and clean up bad connections. Additionally, Oracle clients provide RAC instance affinity for distributed transactions to make managing these transactions easier, especially during failover. ODP.NET: Fast Application Notification (FAN)

ODP.NET connection pool subscribes to notifications that indicate whether nodes and services are available or unavailable. Based on these notifications, the ODP.NET connection pool can free idle connections that were connected to bad nodes and create new ones to healthy nodes, if possible.

The fast connection failover feature is a proactive way of migrating connections from downed services or downed nodes to responsive ones, leading to better availability with a minimal amount of application management needed. ODP.NET: Integration with Real Application Clusters Load Balancing Advisory

The ODP.NET connection pool subscribes to notifications provided by the RAC Load Balancing Advisory. These events provide information on the current service level provided by each instance and a recommendation on how connections are to be directed in a RAC database for a service that is enabled on multiple instances in the cluster. Connections are dispensed or removed from the connection pool based on the current service level.

The load balancing feature is a proactive way of routing work to responsive nodes based on the performance data gathered by the database. This feature allows efficient utilization of database resources to ensure optimum performance by limiting bottlenecks.

1.14.2 Ease of Development

Integration with Microsoft Development Tools and implementation of MS Data Access specifications make it very easy for Windows Developers to develop with Oracle Database.

Enhancements in this area include:

  • Integration with Visual Studio

  • Added .NET Framework 1.1 functionality

  • Database change notification

  • Support for derived parameters

  • Enhanced connection pool administration

  • Control of query cancellation

  • Support for server-side ODP.NET Common Language Runtime (CLR) Stored Procedure Visual Studio Integration

This release provides a wizard in Visual Studio that automatically handles the data type mapping and PL/SQL wrapper code generation. This feature provides a way to deploy the built assembly into the database.

Integration with Visual Studio makes it easy for developers to build and deploy CLR stored procedures into an Oracle Database. ODP.NET: .NET Framework 1.1 Functionality Support

The HasRows property indicates whether a result set is empty unless the application requests a row. The EnlistDistributedTransction method can explicitly decide when and in which transaction it wishes to participate. In .NET Framework 1.0, connections were only allowed to enlist at connection time to the transaction context that was associated with the component and thread. In .NET Framework 1.1, the transaction context can be passed around components (through the ITransaction interface) and have connections enlist in them explicitly.

The HasRows property and EnlistDistributedTransction method that were added to the .NET Framework in its 1.1 release are now supported by ODP.NET, allowing developers to use this additional functionality in an easy-to-use manner. ODP.NET: Database Change Notification Service

Oracle Database Change Notification Service enables applications to receive notifications when there is a change in the query result set of a statement. Using the Database Change Notification Service, the application can specify a notification request for a statement execution, and create a notification registration using the notification framework. When there is a change in the query result set, the notification framework notifies the application about the change. Based on the information provided by the notification framework, the application can then respond to the notification accordingly. For example, the application might need to refresh its own copy of the data for the query result set that is stored locally in the application.

An application can utilize Oracle Database Change Notification Service to be alerted when a database change occurs that affects the results of a query. The changes are propagated to the client to ensure that an accurate, up-to-date data set is used by the user. ODP.NET: Derived Parameters Support

ODP.NET supports the DeriveParameters method on the OracleCommandBuilder which populates the parameter collection for a command, given the stored procedure or function name. This feature is intended to be used at design time, not runtime, as it incurs a database round trip to execute.

The DeriveParameters method automatically populates metadata information needed to bind parameters to a stored procedure or function, saving the user from binding this information explicitly. ODP.NET: Enhanced Connection Pool Administration

This feature provides a way for applications to clear connections in one pool or all pools in an application domain. It provides the facility to refresh the connections and clear the pool of any invalid connections. It also allows applications to destroy the pool all together.

This feature allows developers to explicitly create or destroy connection pools or reset the attributes of existing connection pools to provide greater control in managing ODP.NET connection resources. ODP.NET: Query Cancel/Timeout

This feature allows an ODP.NET command to be canceled if results are not returned within a specified time. Otherwise, a command can continue to block other database calls that need to be executed.

Developers can control how long to wait for a command to complete before canceling the call, providing greater control over an application's service level. Server-Side ODP.NET

The server-side ODP.NET is the same as the client-side ODP.NET with a few exceptions. These exceptions are defined in Oracle Data Provider for .NET Developer's Guide.

A .NET stored procedure or function residing in Oracle may need to access the database. The usual interface to Oracle from .NET is the Oracle Data Provider for .NET. But if the .NET procedure or function is running on the server, there are certain optimizations and limitations that apply. For example, the implicit database session can be used to improve performance. Other features, like connection pooling are not supported.

1.14.3 Performance Improvements

ODP.NET includes a number of improvements that make queries and LOB data retrieval performance faster. Query metadata is now cached so that repeated queries can be executed faster because statements only need to be parsed once. When retrieving LOBs, Oracle has reduced the number of database round trips necessary to retrieve the data to the client. ODP.NET: Improved LOB Retrieval Performance and Functionality

ODP.NET has been improved to make fewer round trips when retrieving LOB data:

  • To ensure full LOB functionality when InitialLOBFetchSize is used.

  • To remove the requirement of the primary key, ROWID, or unique columns to be present in the query to fetch the remaining data for the LOB column when InitialLOBFetchSize is used.

  • To allow InitialLOBFetchSize to fetch greater than 32 KB of data in the first round-trip.

LOB data retrieval now requires fewer database round trips, which improves performance. Additionally, LOBs now have fewer limitations making them easier to use and tune for performance. ODP.NET: Statement Caching

This feature provides and manages a cache of statements for each session. In the server, it means that cursors are ready to be used without the need to parse the statement again. Statement caching can be used with connection pooling and with session pooling, and improves performance and scalability. It can also be used without session pooling.

When identical SQL statements are executed, this feature improves performance by preventing parsing of the SQL statement repeatedly. The SQL statement is parsed just once.

1.14.4 Security

This feature improves and enhances the security of Oracle Database and other components on windows. ODP.NET: Enhanced Virtual Private Database (VPD) Support

This feature allows applications to set the Client Identifier in the Application Context for every database session using ODP.NET.

ODP.NET developers can more easily use Oracle's VPD support by allowing the Client Identifier, which acts like an application user proxy, to be set at the connection string level. Secure Operating System Objects

This feature creates a secure Win32 session environment for the Oracle instance so the process and it's internal state are not accessible from an external environment including sessions with the same privileges.

This enhancement prevents older clients from connecting when they are running on the same machine as the database.

This feature improves security for Oracle on Windows.

1.15 Workspace Manager

This section describes new and changed Workspace Manager features for Oracle Database 10g Release 2 (10.2). See the Oracle Database Workspace Manager Developer's Guide for additional information.


The title of Oracle Database Application Developer's Guide - Workspace Manager has changed to Oracle Database Workspace Manager Developer's Guide.

1.15.1 New Workspace Manager System Parameters

The following are new Workspace Manager system parameters for this release:







1.15.2 New Options for the AlterVersionedTable Procedure

The following new options are available for the alter_option parameter of the AlterVersionedTable procedure:

  • REBUILD_INDEX, which has the following related new parameter_options parameter keywords: index_owner, index_name, and reverse and noreverse

  • USE_SCALAR_TYPES_FOR_VALIDTIME and USE_WM_PERIOD_FOR_VALIDTIME (only one of which can be specified in each call to the procedure)

1.15.3 New Parameter for the EnableVersioning Procedure

The validTimeRange parameter (WM_PERIOD DEFAULT NULL) has been added for the EnableVersioning procedure. With this parameter, if you enable valid time support when you version-enable a table, you can specify an initial valid time range.

1.15.4 New Views for Removed Workspaces

The new ALL_REMOVED_WORKSPACES and USER_REMOVED_WORKSPACES views contain information about workspaces that have been removed during a RemoveWorkspace operation or a MergeWorkspace operation in which the remove_workspace parameter value was true, and while the value of the Workspace Manager system parameter KEEP_REMOVED_WORKSPACES_INFO was ON.

1.15.5 Merging Multiple Tables

You can use the MergeTable procedure to apply changes to multiple tables (all rows or as specified in the WHERE clause) in a workspace to its parent workspace. To specify multiple tables, specify them in the table_id parameter and separate the names with commas (for example, 'table1, table2'). In previous releases, you could specify only a single table name.

1.15.6 Workspace ID Column Added to Static Data Dictionary Views

A numeric WORKSPACE_ID column has been added to the ALL_WORKSPACES and USER_WORKSPACES views.

1.15.7 User-Defined Hints

You can specify user-defined hints, which modify (and thus override) default optimizer hints, with the goal of improving the performance of SQL statements executed by the DBMS_WM package on a specified version-enabled table or all version-enabled tables. To add a user-defined hint, use the new AddUserDefinedHint procedure; to remove a user-defined hint, use the new RemoveUserDefinedHint procedure.

1.15.8 Adding, Merging, or Splitting Table Partitions

You can add, merge, and split table partitions in a version-enabled table by using the alter_option parameter to the AlterVersionedTable procedure.

1.15.9 Null Foreign Key Constraints

SET NULL foreign key constraints are now supported, and any such constraint is reflected in the ALL_WM_RIC_INFO metadata view with a row for which the DELETE_RULE column is set to N. (The value in this column does not affect behavior in any way; it only displays metadata about the table.)

1.15.10 Oracle Label Security Policies and Version-Enabled Tables

If you need to perform DDL operations on a version-enabled table in an Oracle Label Security (OLS) environment, you can use the apply_table_policy, remove_table_policy, enable_table_policy, and disable_table_policy procedures of the SA_POLICY_ADMIN package on the skeleton (_LTS) table, and the changes will be transferred to the version-enabled table.