Oracle Database 10g Release 2 (10.2) New Features

Oracle Database 10g Release 2 (10.2) New Features

Transparent data encryption

With the CREATE TABLE or ALTER TABLE commands, you can specify table columns for which data is encrypted before being stored in the datafile. If users attempt to circumvent the database access control mechanisms by looking inside datafiles directly with operating system tools, encryption prevents such users from viewing sensitive data.

DML error logging

A new error logging clause for all DML statements enables certain types of errors (for example, constraint violations or data conversion errors) to be logged to an error logging table, allowing the statement to continue instead of terminating and rolling back.

Enhancements to Automatic Shared Memory Management

The Streams Pool is now also automatically tuned when automatic shared memory management is enabled. A new view, V$SGA_TARGET_ADVICE, provides information to help with tuning SGA_TARGET.


Improved automatic tuning of undo retention results in fewer "ORA-01555: snapshot too old" messages.

Automatic tuning of undo retention now always tunes for the maximum possible retention for the undo tablespace based on tablespace size and current system activity. This improves the success rate of Oracle Flashback operations and long-running queries.

The Segment Advisor now reports tables with excessive row chaining.

The Segment Advisor now runs automatically during the maintenance window.

Upon installation, a Scheduler job is automatically created to run the Segment Advisor during the maintenance window. You can also continue to run the Segment Advisor manually. The automatically run Segment Advisor (the "Automatic Segment Advisor") examines statistics on the entire database and selects segments and tablespaces to analyze. You can view recommendations with Enterprise Manager, with the DBA_ADVISOR_* views, or with the new DBMS_SPACE.ASA_RECOMMENDATIONS procedure.

Enhancements to the online segment shrink capability

Online segment shrink now supports:

LOB segments

IOT overflow segments (both standalone and as a dependent object of an IOT index segment)

Enhancements to online table redefinition

Online redefinition of tables now supports:

Redefining a single partition of a partitioned table

Clustered tables, Advanced Queuing queue tables, and materialized view logs

Object data types (objects, VARRAYs, and nested tables, including nested table dependent objects)

Check and NOT NULL constraints

Preservation of table statistics

Parallel execution for Long-to-LOB migration

In addition, dependent PL/SQL package recompilation is no longer required when the redefined table has the same number, types, and order of columns as the original table.


Support for XMLTypes in the transportable tablespace facility

Enhancements to space management

The DBMS_SPACE_ADMIN package provides new tools to troubleshoot space management problems


Control files no longer need to be recreated when changing certain configuration parameters

Control files can now dynamically grow in size when you increase the values of the following parameters: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES.

Tablespace low-space alert thresholds by free space remaining

Low-space alert thresholds for locally managed tablespaces can now be by percent full or by free space remaining (in KB). Free-space-remaining thresholds are more useful for very large tablespaces.

Automatic Storage Management (ASM) files are now accessible through an XML DB virtual folder

ASM files can now be accessed through the XML DB repository, either programmatically or with protocols like FTP and HTTP/WebDAV.

Automatic Storage Management now has a command-line utility (ASMCMD)

With ASMCMD you can easily view and manipulate files and directories within disk groups. ASMCMD can list the contents of disk groups, perform searches, create and remove directories and aliases, display space utilization, and more.

Automatic Storage Management (ASM) supports multiple database versions

ASM maintains forward and backward compatibility between most 10.x versions of Oracle Database and 10.x versions of ASM. That is, any combination of versions 10.1.x.y and 10.2.x.y for either the ASM instance or the database instance works correctly, with this caveat: For a 10.1.x.y database instance to connect to a 10.2.x.y ASM instance, the database must be version 10.1.0.3 or later.

The DBMS_FILE_TRANSFER package can now copy files between a local file system and an Automatic Storage Management (ASM) disk group.

The DBMS_FILE_TRANSFER package can use a local file system or an ASM disk group as the source or destination for a file transfer. You can now copy files from ASM to the file system or from the file system to ASM.

The ALTER DISKGROUP command has a new REBALANCE WAIT clause.

ALTER DISKGROUP commands that cause a rebalance of an ASM disk group—commands that add, drop, or resize disks, or the command that starts a manual rebalance operation—can now wait until the rebalance operation completes before returning. This is especially useful in scripts.