Oracle Useful Information

New features grouped by Oracle version

Last Updated : 28-Apr-2004
Version : 1.5

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 on 8i and 9i, whilst developing the new release under Oracle 10g is a typical scenario. This is a quick reference guide to each release of Oracle from 7.0 to Oracle 10g and what notable new features where introduced at each major release.

New Features Summary

Oracle 10g Release 2 (10.2.0)

Transparent data encryption
Dynamically change certain parameter MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY without recreating controlfile
Automatic Storage Management Command line Facility (ASMCMD)
Encrypted RMAN Backups
Conditional PLSQL Compilation
DBMS_DDL.wrap to hide generated plsql source code
Data Guard Fast Start Failover

Oracle 10g Release 1 (10.1.0)

Set Database Default Tablespace. Rename Tablespace
Data Pump. Major rewrite of exp/imp utilities introducing Parallelism, Include/Exclude Objects, Files on Server. Command line Monitoring (formerly Export/Import)
Introduced RECYCLEBIN command for storing objects before they are dropped. Required new PURGE command for maintenance.
Automatic Workload Repository (formerly Statspack). Runs automatically without setup & configuration
Automatic Database Diagnostic Manager (ADDM)
sqlplus / as sysdba works without using quotes !
Automatic Storage Management (ASM) - Oracle New Disk Configuration Feature.
SYSAUX tablespace created
Multiple Temporary Tabelspaces can be defined. Useful for Large Index builds
RMAN Compression of backup sets
Automatic Database Tuning of Checkpoints, Shared Memory, Undo Segments
SQL Tuning Advisor, SQL Access Advisor Tools
Automatic Statistics Collection
DBMS_OUTPUT display is procedure /trigger used in a SELECT statement
RAC Enhancements: Integrated Clusterware. Rolling Patch Upgrades.
SDP Protocol SUpport
Many Enhancements to Oracle Streams
Database Scheduler Introduced
DROP DATABASE Command
RMAN Enhancements. ALTER DATABASE BEGIN BACKUP command
Data Guard Enhancements: DG Broker for RAC support. Secure Redo Log Transmission.
Flashback Enhancements: FLASHBACK DATABASE / TABLE statements
Rolling Upgrades using SQL Apply
Security Enhancements: Faster SSL handshake. Column Level VPDs
Improved VLDB Support: 8 million TB of data can be addressed. Partition Manager GUI.
Cross Platform Transportable Tablespaces. External Table UNLOAD.
HTML DB Introduced including Application Builder.
Browser Based Data Workshop and SQL Workshop
PLSQL : UTL_COMPRESS, UTL_MAIL. Compile Time Warnings
SQL: Regular Expression Support. EVALUATE operator. New ROW TIMSTAMP pseudo column

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
Compressed Tables
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

Site Map