Oracle Database 10g Release 2: What's New, Release History & Support Lifecycle

What Is New in Oracle Database 10g Release 2

What does Transparent Data Encryption add to Oracle Database 10g Release 2?

Transparent Data Encryption (TDE) lets you encrypt individual table columns using AES or 3DES without any changes to application code -- the database handles encryption and decryption transparently at the SQL layer. Key management is handled server-side through Oracle Wallet, so application developers never touch raw encryption keys.

In practice, TDE is the answer for organizations facing regulatory requirements around data-at-rest protection. Storage theft no longer exposes plaintext data, because the encrypted column is unreadable without the open wallet. The feature is complemented by an enhanced Secure External Password Store, which lets batch jobs connect via sqlplus / using wallet-stored credentials instead of hardcoded passwords in scripts.

Watch out for one breaking change bundled alongside these security improvements: the CONNECT role has been reduced to CREATE SESSION only. If any application grants CONNECT and then relies on the legacy privileges that came with it (ALTER SESSION, CREATE CLUSTER, CREATE TABLE, etc.), those grants will silently disappear after upgrade. Audit your role grants before upgrading.

-- Enable wallet and encrypt a column
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "wallet_password";

ALTER TABLE employees
  MODIFY (ssn VARCHAR2(11) ENCRYPT USING AES256);

-- Connect using external password store
sqlplus /@myalias

How does Data Guard fast-start failover work in Oracle Database 10g Release 2?

Fast-Start Failover allows Data Guard to automatically fail over to a pre-selected standby database without any DBA intervention when the primary server, site, or network becomes unavailable. A dedicated observer process monitors the configuration and triggers the failover as soon as the conditions are met.

This matters most for 24x7 OLTP environments where manual failover procedures add minutes of extra outage. After the failover completes, the old primary can be automatically reinstated as a standby using Flashback Database, eliminating the need to rebuild from scratch. Both Maximum Protection and Maximum Availability redo transport modes are supported, but this feature requires Flashback Database to be enabled on both sides.

Additional Data Guard improvements in this release include optimized asynchronous redo transmission (the log writer is no longer blocked on network timeouts), faster SQL Apply and Redo Apply failover paths, automatic deletion of applied archive logs from the logical standby, and the ability to convert a physical standby into a reporting database and then flash it back to standby role when done.

• Flashback Database Through Resetlogs -- you can now flash back past a RESETLOGS operation, useful when a recovery mistake was discovered late.
• Named Restore Points -- associate a user-defined name with an SCN instead of recording raw numbers manually; guaranteed restore points ensure flashback logs are retained.
• Database Transport Across Same Endian Platforms -- fast Windows-to-Linux or Solaris-to-HP-UX migrations using RMAN instead of slow Export/Import.

What performance and diagnostic improvements came in Oracle Database 10g Release 2?

Release 2 significantly widens the self-management net: ADDM now covers Streams, AQ, RMAN, and RAC in addition to the core database, and the AWR Compare Periods Report lets you diff two snapshot pairs to correlate workload changes with plan regressions or tuning actions.

Most teams will notice the following changes immediately after upgrade:

• Self-Tuning DB_FILE_MULTIBLOCK_READ_COUNT -- the parameter is now automatically set based on OS optimal I/O size and buffer cache. Remove manual overrides from your spfile unless you have a measured reason to keep them.
• Asynchronous Commit -- clients can return from a COMMIT before the redo is fully written to disk. Useful for high-frequency, low-criticality inserts; use with caution for financial transactions.
• LOB Direct Path Load 5x Faster -- SQL*Loader direct path for out-of-line LOBs sees dramatic improvement. If you load large BLOB or CLOB data nightly, benchmark this immediately.
• V$SQLSTATS View -- a new lightweight alternative to V$SQL for high-frequency SQL statistics polling with lower latch contention.
• SQL Profile Literal Normalization -- SQL profiles now match after bind variable normalization, so applications using literals rather than bind variables can still benefit from stored profiles.
• Interruptible SQL Access Advisor -- interrupt a long-running Access Advisor run and view partial recommendations via the new V$ADVISOR_PROGRESS view.

-- View the new AWR compare periods report
-- Run from DBMS_WORKLOAD_REPOSITORY
SELECT * FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(
    :dbid, :inst_num,
    :begin_snap1, :end_snap1,
    :begin_snap2, :end_snap2
  )
);

-- Check advisor progress
SELECT TASK_NAME, STATUS, PCT_COMPLETION_TIME
FROM V$ADVISOR_PROGRESS;

How does Oracle Database 10g Release 2 improve storage and space management?

Release 2 makes Automatic Segment Space Management (ASSM) the default for new tablespaces, replacing freelist-based management out of the box. If you are still creating tablespaces with SEGMENT SPACE MANAGEMENT MANUAL for compatibility reasons, reconsider -- the performance and concurrency benefits of ASSM are well established by this point.

The Automatic Segment Advisor now runs nightly as part of the maintenance window and flags segments with significant fragmentation for Online Segment Shrink without a DBA manually initiating the scan. Segment Shrink has also been extended to LOB segments and IOT overflow segments, covering most of the table types that were previously excluded.

• Drop Empty Datafile -- finally possible to drop a datafile that has no allocated extents. Useful when a datafile was added to the wrong tablespace by mistake.
• RMAN Unused Block Compression -- backups now skip previously-used-but-now-empty blocks (not just never-touched blocks), producing meaningfully smaller backup sets for tables that have seen heavy DELETE activity.
• RMAN Backup Encryption -- three modes: transparent (wallet-based), password-based, or dual-mode. Satisfies most regulatory backup encryption mandates without third-party tools.
• ASMCMD -- a shell-like command-line interface for navigating and managing files within ASM disk groups, including ls, cp, mv, and rm semantics.
• Partition limit raised to 1,024K-1 -- up from 64K-1, enabling much finer partitioning granularity for large data warehouses.

-- Use ASMCMD interactively
$ asmcmd
ASMCMD> ls +DATA/ORCL/DATAFILE/
ASMCMD> cp +DATA/ORCL/DATAFILE/system.dbf /backup/system.dbf

-- Enable RMAN backup encryption
RMAN> SET ENCRYPTION ON IDENTIFIED BY "backup_pass" ONLY;
RMAN> BACKUP DATABASE;

What developer-facing features are new in Oracle Database 10g Release 2?

Release 2 delivers several PL/SQL and SQL features that reduce workarounds developers have relied on for years. PL/SQL Conditional Compilation is arguably the most impactful -- it allows a single codebase to target multiple Oracle versions using $IF / $ELSIF / $END directives, so ISVs can ship one package body that compiles cleanly on 10.1, 10.2, and later releases.

DML Error Logging deserves immediate attention for anyone running bulk loads. Instead of an entire INSERT or UPDATE rolling back on a single row error, you can redirect failing rows into a dedicated error table and let the rest of the DML complete. This combines the speed of bulk processing with row-level error visibility previously only available with slow row-by-row FORALL exception handling.

-- Create error log table and use DML error logging
BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG('orders', 'orders_err$');
END;
/

INSERT INTO orders
SELECT * FROM orders_staging
LOG ERRORS INTO orders_err$ ('batch_20250101') REJECT LIMIT UNLIMITED;

Other notable developer additions:

• XQuery via XMLQUERY and XMLTABLE -- native XQuery execution against XMLType data and the XML DB repository, with XMLTABLE projecting XQuery results as relational rows.
• DBMS_OUTPUT line limit raised to 32,767 bytes -- and buffer size can now be set to NULL for unlimited output. The old 255-byte line cap is gone.
• New XML manipulation functions -- InsertXML(), AppendChildXML(), InsertXMLBefore(), and DeleteXML() allow adding and removing nodes directly from SQL without DOM API loops.
• JDBC Runtime Connection Load Balancing -- the JDBC connection pool queries RAC workload metrics and routes new connections to the least loaded instance automatically.
• Rules Manager -- a SQL/XML-driven engine for event-based processing stored inside the database; rules can trigger user-defined PL/SQL procedures or external application callbacks.
• DBMS_DDL.CREATE_WRAPPED -- obfuscation of dynamically generated PL/SQL at runtime, not just at source-delivery time, closes the gap for ISVs whose installed products generate custom stored procedures.