Oracle Useful Information New features grouped by Oracle version

Last Updated : 05-Jun-2002
Version : 1.4

Description

Many DBA's and developers find themselves working with more than one version of Oracle at one time. e.g Maintainance on a production system in 8i, whilst developing the new release under Oracle 9i is a typical scenario. This is a quick reference guide to each release of Oracle from 7.0 to Oracle 9i and what new features where introduced at each major release.

New Features Summary

Oracle 9i Release 2 (9.2)

• Enhanced XML Support (XML DB, XML repository)
• Oracle JVM compliant with JDK 1.3
• Oracle Data Guard Enhancements (SQL Apply mode - logical copy of primary database, automatic failover
• Oracle Streams - new data sharing/replication feature
• Locally Managed SYSTEM tablespaces
• Real Application Clusters Guard II
• Security Improvements - Default Install Accounts locked, VPD on synonyms, AES, Migrate Users to Directory

Oracle 9i Release 1 (9.0.1)

• Log Miner Viewer
• 9i Data Guard (previously Standby Database Enhancements)
• 9i Real Application Clusters (formerly Oracle Parallel Server)
• 9i Real Application Clusters Guard (formerly Oracle Failsafe)
• Fast Instance Freeze > Resume
• 9i Flashback Query
• Online Maintenance Features - redefine tables/indexes and resize database sga online
• System managed Rollback Segments
• Database Configuration Assistant (DBCA) - Support for Database Templates
• Suspend and Resume Sessions
• Multiple Database Block Sizes in one database
• Mean Time to Recover (MTTR) Specification
• New View V$SQL_PLAN gives actual plan being used
• RMAN - restartable backup/restores, persistent configuration, automatic obsolete management, block media recovery
• Trial Media Recovery
• Enterprise Java Engine (JVM) support for J2EE
• New Datatypes - TIMESTAMP, XMLtype
• New SQL Functions - CURRENT_DATE,CURRENT_TIMESTAMP,FIRST,LAST,NULLIF,COMPOSE,COALESCE
• New Packages - DBMS_METADATA
• ANSI SQL CASE statements introduced
• Version 1.2 if IFS Shipped
• Oracle Ultra Search
• Updateable Scrollable Cursors
• New Extraction, Transformation, Loading (ETL) Capabilities - External Tables, INSERT... MERGE
• List Partitioning Option

Obsolete Features in 9i

• ANALYZE statement => DBMS_STATS
• CONNECT INTERNAL => CONNECT / AS SYSDBA
• INCREMENTAL EXPORT/IMPORT
• Oracle Names Desupport => Directory Naming
• Server Manager (svrmgrl) => SQL*Plus
• ESTAT/BSTAT desupport => STATSPACK

Oracle 8i (8.1.7)

• Static HTTP server included (Apache)
• JVM Accelerator to improve performance of Java code
• Java Server Pages (JSP) engine
• MemStat - A new utility for analyzing Java Memory footprints
• OIS - Oracle Intergration Server introduced.
• PLSQL Gateway introduced for deploying PL/SQL based solutions on the Web
• Enterprise Manager Enhancements - including new HTML based reporting and Advanced Replication functionality included.
• New Database Character Ser Migration utilility included.

Oracle 8i (8.1.6)

• PL/SQL Server Pages (PSP's)
• DBA Studio Introduced
• Statspack
• New SQL Functions (rank, moving average)
• ALTER FREELISTS command (previously done by DROP/CREATE TABLE)
• Checksums always on for SYSTEM tablespace allowing many possible corruptions to be fixed before writing to disk
• XML Parser for Java
• New PLSQL encrypt/decrypt package introduced
• User and Schemas sepapated
• Numerous Performance Enhancements

Oracle 8i (8.1.5)

• Fast Start recovery - Checkpoint rate auto-adjusted to meet roll forward criteria
• Reorganize indexes/index only tables which users accessing data - Online index rebuilds
• Log Miner introduced - Allows on-line or archived redo logs to be viewed via SQL
• OPS Cache Fusion introduced avoiding disk I/O during cross-node communication
• Advanced Queueing improvements (security, performance, OO4O support
• User Security Improvements - more centralisation, single enterprise user, users/roles across multiple databases.
• Virtual private database
• JAVA stored procedures (Oracle Java VM)
• Oracle iFS
• Resource Management using proirities - resource classes
• Hash and Composite partitioned table types
• SQL*Loader direct load API
• Copy optimizer statistics across databases to ensure same access paths across different environments.
• Stanby Database - Auto shipping and application of redo logs. Read Only queries on standby database allowed.
• Enterprise Manager v2 delivered
• NLS - Euro Symbol supported
• Analyze tables in parallel
• Temporary tables supported.
• Net8 support for SSL, HTTP, HOP protocols
• Transportable tablespaces between databases
• Locally managed tablespaces - automatic sizing of extents, elimination of tablespace fragmentation, tablespace information managed in tablespace (i.e noved from data dictionary) improving tablespace reliability
• Drop Column on table (Finally !!!!!)
• DBMS_DEBUG PL/SQL package, DBMS_SQL replaced by new EXECUTE IMMEDIATE statement
• Progress Monitor to track long running DML,DDL
• Functional Indexes - NLS, case insensitive, descending

Oracle 8.0

• Object Relational database
• Object Types (not just date, character, number as in v7
• SQL3 standard
• Call external procedures
• LOB >1 per table
• Partitioned Tables and Indexes
• export/import individual partitions
• partitions in multiple tablespaces
• online/offline, backup/recover individual partitions
• merge/balance partitions
• Advanced Queuing for message handling
• Many performance improvements to SQL/PLSQL/OCI making more efficient use of CPU/Memory. V7 limits extended (e.g. 1000 columns/table, 4000 bytes VARCHAR2)
• Parallel DML statements
• Connection Pooling ( uses the physical connection for idle users and transparently re-establishes the connection when needed ) to support more concurrent users.
• Improved “STAR” Query optimizer
• Integrated Distributed Lock Manager in Oracle PS (as opposed to Operating system DLM in v7).
• Performance improvements in OPS - global V$ views introduced across all instances, transparent failover to a new node
• Data Cartridges introduced on database (e.g. image, video, context, time, spatial)
• Backup/Recovery improvements - Tablespace point in time recovery, incremental backups, parallel backup/recovery. Recovery manager introduced
• Security Server introduced for central user administration. User password expiry, password profiles, allow custom password scheme. Privileged database links (no need for password to be stored)
• Fast Refresh for complex snapshots, parallel replication, PL/SQL replication code moved in to Oracle kernel. Replication manager introduced.
• Index Organized tables
• Deferred integrity constraint checking (deferred until end of transaction instead of end of statement).
• SQL*Net replaced by Net8
• Reverse Key indexes
• Any VIEW updateable
• New ROWID format

Oracle 7.3

• Partitioned Views
• Bitmapped Indexes
• Asynchronous read ahead for table scans
• Standby Database
• Deferred transaction recovery on instance startup
• Updatable Join Views (with restrictions)
• SQLDBA no longer shipped.
• Index rebuilds
• db_verify introduced
• Context Option
• Spatial Data Option
• Tablespaces changes - Coalesce, Temporary Permanent,
• Trigger compilation, debug
• Unlimited extents on STORAGE clause.
• Some init.ora parameters modifiable - TIMED_STATISTICS
• HASH Joins, Antijoins
• Histograms
• Dependencies
• Oracle Trace
• Advanced Replication Object Groups
• PL/SQL - UTL_FILE

Oracle 7.2

• Resizable, autoextend data files
• Shrink Rollback Segments manually
• Create table, index UNRECOVERABLE
• Subquery in FROM clause
• PL/SQL wrapper
• PL/SQL Cursor variables
• Checksums - DB_BLOCK_CHECKSUM, LOG_BLOCK_CHECKSUM
• Parallel create table
• Job Queues - DBMS_JOB
• DBMS_SPACE
• DBMS Application Info
• Sorting Improvements - SORT_DIRECT_WRITES

Oracle 7.1

• ANSI/ISO SQL92 Entry Level
• Advanced Replication - Symmetric Data replication
• Snapshot Refresh Groups
• Parallel Recovery
• Dynamic SQL - DBMS_SQL
• Parallel Query Options - query, index creation, data loading
• Server Manager introduced
• Read Only tablespaces

Oracle 7.0

• Database Integrity Constraints (primary, foreign keys, check constraints, default values)
• Stored procedures and functions, procedure packages
• Database Triggers
• View compilation
• User defined SQL functions
• Role based security
• Multiple Redo members - mirrored online redo log files
• Resource Limits - Profiles
• Much enhanced Auditing
• Enhanced Distributed database functionality - INSERTS, UPDATES,DELETES, 2PC
• Incomplete database recovery (e.g SCN)
• Cost based optimiser
• TRUNCATE tables
• Datatype changes (i.e VARCHAR2 CHAR, VARCHAR)
• SQL*Net v2, MTS
• Checkpoint process
• Data replication - Snapshots

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal