What's New in Oracle 9i (New features in Oracle 9i)

The following new features were introduced with Oracle 9i:

Oracle 9i Release 1 (9.0.1) - June 2001

• Traditional Rollback Segments (RBS) are still available, but can be replaced with automated System Managed Undo (SMU). Using SMU, Oracle will create it's own "Rollback Segments" and size them automatically without any DBA involvement.
• Flashback query (dbms_flashback.enable) - one can query data as it looked at some point in the past. This feature will allow users to correct wrongly committed transactions without contacting the DBA to do a database restore.
• Use Oracle Ultra Search for searching databases, file systems, etc. The UltraSearch crawler fetches data and hand it to Oracle Text to be indexed.
• Oracle Nameserver is still available, but deprecate in favor of LDAP Naming (using the Oracle Internet Directory Server). A nameserver proxy is provided for backwards compatibility as pre-8i client cannot resolve names from an LDAP server.
• Oracle Parallel Server's (OPS) scalability was improved - now called Real Application Cluster (RAC). Full Cache Fusion implemented. Any application can scale in a database cluster. Applications don't need to be cluster aware anymore.
• The Oracle Standby DB feature renamed to Oracle Data Guard. New Logical Standby databases replay SQL on standby site allowing the database to be used for normal read write operations. The Data Guard Broker allows single step fail-over when disaster strikes.
• Scrolling cursor support. Oracle9i allows fetching backwards in a result set.
• Dynamic Memory Management - Buffer Pools and shared pool can be resized on-the-fly. Introduced sga_max_size parameter. This eliminates the need to restart the database each time parameter changes were made.
• On-line table and index reorganization.
• VI (Virtual Interface) protocol support, an alternative to TCP/IP, available for use with Oracle Net (SQL*Net). VI provides fast communications between components in a cluster.
• Build in XML Developers Kit (XDK). New data types for XML (XMLType), URI's, etc. XML integrated with AQ.
• Cost Based Optimizer now also considers memory and CPU, not only disk access cost as before.
• Automatic Segment Space Management introduced in Oracle 9.0.1
• PL/SQL programs can be natively compiled to binaries.
• A new SQL feature introduced in Oracle Database 9i, which allowing us to return values from DML, by using DML RETURNING clause. For example: INSERT INTO customers (...) VALUES (...) RETURNING cust_id into l_cust_id;
• Deep data protection - fine grained security and auditing. Put security on DB level. SQL access does not mean unrestricted access.
• Resumable backups and statements - suspend statement instead of rolling back immediately.


• List partitioning- partitioning on a list of values.
• ETL (Extract, Transformation, Load) Operations - with external tables and pipelining.
• Oracle OLAP- Express functionality included in the DB.
• Data Mining - Oracle Darwin's features included in the DB.
• DBA can specify a default temporary tablespace for the database.
• In Oracle9i, significant improvements have been made to materialized view refresh:
  • Fast refresh is now possible on materialized views that contain joins and aggregates even when base table data has changed using DMLs. In Oracle8i, fast refresh was possible, on materialized views that contain joins and aggregates, only if base table data was inserted using SQL*Loader direct path.
  • Fast refresh is possible after partition maintenance operations, such as TRUNCATE PARTITION, on tables referenced in the materialized view.
  • A new mechanism called Partition Change Tracking (PCT) has been introduced. This mechanism keeps track of the base table partitions that have been updated since the materialized view was last refreshed. This allows Oracle to identify fresh data in the materialized view.

Oracle 9i Release 2 (9.2.0) - May 2002

• Locally Managed SYSTEM tablespaces.
• Oracle Streams - new data sharing/replication feature (can potentially replace Oracle Advance Replication and Standby Databases).
• XML DB (Oracle is now a standards compliant XML database).
• Data segment compression (compress keys in tables - only when loading data).
• Cluster File System (CFS) for Windows and Linux (raw devices are no longer required).
• Create logical standby databases with Data Guard.
• Java JDK 1.3 used inside the database (JVM).
• New system privilege, "GRANT ANY OBJECT PRIVILEGE" is introduced to control the grant and revoke object privileges.
• Oracle Data Guard Enhancements (SQL Apply mode - logical copy of primary database, automatic failover).
• DBMS_XPLAN Introduced in Oracle 9.2, displays execution plans in EXPLAIN plan table.
• Security Improvements - Default Install Accounts locked, VPD on synonyms, AES, Migrate Users to Directory.

What's New in 10g...