Oracle Database 11g Editions

http://www.oracle.com/database/product_editions.html

Oracle Database 10g Editions Information

Oracle 10g 10.2

http://youngcow.net/doc/oracle10g/license.102/b14199/editions.htm

• The Oracle Database Product Family
• Feature Availability by Edition
• Special-Use Licensing

The following Oracle Flashback features are not part of Standard Edition or Standard Edition One:

• Oracle Flashback Table
• Oracle Flashback Database
• Oracle Flashback Transaction Query
• Restore Points

Oracle 10g 10.1

http://www.itk.ilstu.edu/docs/Oracle/license.101/b13552/editions.htm

Oracle database version history

The major Oracle versions, with their latest patch-sets are:

• Oracle 7: … – 7.3.4.5
• Oracle 8: 8.0.3 – 8.0.6
• Oracle 8i: 8.1.5.0 – 8.1.7.4
• Oracle 9i (Release 1): 9.0.1.0 – 9.0.1.4
• Oracle 9i (Release 2): 9.2.0.1 – 9.2.0.8
• Oracle 10g (Release 1): 10.1.0.2 – 10.1.0.5
• Oracle 10g (Release 2): 10.2.0.1 – 10.2.0.4
• Oracle 11g (Release 1): 11.1.0.6

If running on one of the above releases, it is recommended to always install the latest patch-set.

Currently supported versions

Oracle 9iR2 will be desupported on 31 July 2007. Customers on Oracle 9.2.0.8 will have the Extended Support fee waived until July 31, 2008. For details about this, see http://www.oracle.com/features/hp/database-9i-support.html

All Oracle 10g and Oracle 11g releases are fully supported.

From http://www.orafaq.com/wiki/Oracle_database#Database_features

Oracle 8i (8.1.7) – August 2000
Oracle 9i Release 1 (9.0.1) – June 2001
Oracle 9i Release 2 (9.2.0) – May 2002
Oracle 10g Release 1 (10.1.0) – January 2004
Oracle 10g Release 2 (10.2.0) – September 2005
Oracle 11g Release 1 (11.1.0.6) – July 2007

Features introduced in the various Oracle server releases

This document summarizes the differences between Oracle Server releases.

Most DBA’s and developers work with multiple versions of Oracle at any particular time. This document describes the high level features introduced with each new version of the Oracle database. It is intended to be used as a quick reference as to whether a feature can be implemented, or if a upgrade is required.

Oracle 10g Release 2 (10.2.0) – September 2005

• Transparent Data Encryption
• Async commits
• CONNECT ROLE can not only connect
• Passwords for DB Links are encrypted
• New asmcmd utility for managing ASM storage

Oracle 10g Release 1 (10.1.0)

• Grid computing – an extension of the clustering feature (Real Application Clusters)
• Manageability improvements (self-tuning features)
• Performance and scalability improvements
• Automated Storage Management (ASM)
• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitor (ADDM)
• Flashback operations available on row, transaction, table or database level
• Ability to UNDROP a table from a recycle bin
• Ability to rename tablespaces
• Ability to transport tablespaces across machine types (E.g Windows to Unix)
• New ‘drop database’ statement
• New database scheduler – DBMS_SCHEDULER
• DBMS_FILE_TRANSFER Package
• Support for bigfile tablespaces that is up to 8 Exabytes in size
• Data Pump – faster data movement with expdp and impdp.

Oracle 9i Release 2 (9.2.0)

• 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 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)
• Oracle Data Guard Enhancements (SQL Apply mode – logical copy of primary database, automatic failover
• Security Improvements – Default Install Accounts locked, VPD on synonyms, AES, Migrate Users to Directory

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 fetch data and hand it to Oracle Text to be indexed.
• Oracle Nameserver is still available, but deprecate in favour 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 Clusters (RAC). Full Cache Fusion implemented. Any application can scale in a database cluster. Applications doesn’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. 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 consider memory and CPU, not only disk access cost as before.
• PL/SQL programs can be natively compiled to binaries.
• Deep data protection – fine grained security and auditing. Put security on DB level. SQL access do 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.
• OLAP – Express functionality included in the DB.
• Data Mining – Oracle Darwin’s features included in the DB.

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 Integration 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 Set Migration utility 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 separated
• 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 priorities – 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.
• Standby 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 moved 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 – June 1997

• 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 – June 1992

• 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

Oracle 6.2

• Oracle Parallel Server

Oracle 6 – July 1988

• Row-level locking
• On-line database backups
• PL/SQL in the database

Oracle 5.1

• Distributed queries

Oracle 5.0 – 1986

• Supporting for the Client-Server model – PC’s can access the DB on remote host

Oracle 4 – 1984

• Read consistency

Oracle 3 – 1981

• Atomic execution of SQL statements and transactions (COMMIT and ROLLBACK of transactions)
• Nonblocking queries (no more read locks)
• Re-written in the C Programming Language

Oracle 2 – 1979

• First public release
• Basic SQL functionality, queries and joins

From http://www.orafaq.com/faq/features_introduced_in_the_various_server_releases

What is the difference between the Enterprise, Standard and Personal Editions of Oracle?

Oracle Enterprise Edition:

Enterprise Edition is the full (top of the range) version or the Oracle Database Server. Options like RAC, Partitioning, Spatial, etc. can be purchased separately to enhance the functionality of the database.

Oracle Standard Edition:

Standard Edition is designed for smaller businesses and enterprises. It offers a subset of the features/ functionality implemented in Enterprise Edition. Database options like Data Guard, Partitioning, Spatial, etc. is not available with Standard Edition (from 10g one can use RAC with Standard Edition). Standard Edition can only be licensed on servers with a maximum capacity of four processors.

Oracle Standard Edition One:

Standard Edition One is a low cost, entry-level version of the Oracle Standard Edition database server. Standard Edition One can only be licensed on small servers with a maximum capacity of two processors.

Oracle Personal Edition:

Personal Oracle is a single user version of the database server. It is mostly the same as Enterprise Edition, but doesn’t support advanced options like RAC, Streams, XMLDB, etc.

Oracle Lite:

Oracle Light is a database engine that can be used on mobile platforms like cell phones and PDA’s.

Oracle XE:

Express Edition (XE) is a free, downloadable version of the Oracle database server. Oracle XE can only be used on single processor machines. It can only manage up to 4 GB of data and 1 GB of memory. ISVs can embed XE in 3rd party products and redistribute it freely.

Additional Reading:

Oracle10g Database Licensing Guide
(Part of the Oracle 10g Documentation Set)

Oracle9i Database New Features Guide
Chapter 5: Database Feature and Option Availability

Oracle Store
http://store.oracle.com/

From http://www.orafaq.com/faq/what_is_the_difference_between_the_enterprise_standard_and_personal_editions_of_oracle

Check Oracle version and components

The easiest way to determine the database version

SELECT * FROM v$version;

BANNER 
---------------------------------------------------------------- 

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi 
PL/SQL Release 10.2.0.3.0 - Production 

CORE    10.2.0.3.0      Production 
TNS for Solaris: Version 10.2.0.3.0 - Production 

NLSRTL Version 10.2.0.3.0 - Production 

The output shows the edition (Enterprise), the version of the database (Oracle10g), the release with the installed patch-set (10.2.0.3.0), and the 32-bit/64-bit version (64-bit).

In general, if Oracle is a 64bit version, the word ‘64bit’ will be displayed in the connection information of the opening banners of the Oracle executables such as SQL*Plus. It will also be displayed in the headers of Oracle trace files. Otherwise if the word ‘64bit’ is not displayed, it can be assumed that Oracle is a 32bit version.

Alternatively, when starting SQL*Plus and connecting to the database, the connect message also displays the version of the database:

> sqlplus system/manager

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 31 09:27:19 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved. 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options 

Subsequent cumulative patch-sets installations, such as the 10.2.0.3.8 patch-set bundle for the Windows platform, will not always be shown in the banner. Starting with Oracle10g Release 2 (10.2.0.x) the patchset and installed Critical Patch Update information can also be examined inside the database, e.g.:

SET lines 100 numwidth 12 pages 100 
COL action_time FOR a30 
COL action FOR a12 
COL version LIKE action 
COL comments FOR a30 

SELECT action_time, action, version, id, comments 
  FROM dba_registry_history 
 ORDER BY action_time;

ACTION_TIME                   ACTION   VERSION           ID COMMENTS

----------------------------- -------- ----------- -------- --------------------------
15-MAR-06 03.01.00.987490 AM  UPGRADE  10.2.0.2.0           Upgraded from 10.2.0.1.0

18-JAN-07 02.10.05.942649 PM  UPGRADE  10.2.0.3.0           Upgraded from 10.2.0.2.0

18-MAR-07 04.11.23.119000 PM  CPU      10.2.0.3.0   5846376 Patch2  

When specifying the version of the database, please use the latest and full database version, for example:

Oracle10g Enterprise Edition 32-bit 10.2.0.3.8

select * from PRODUCT_COMPONENT_VERSION;

select comp_name, version, status, modified from dba_registry;

select comp_name, version, status, modified from dba_registry
where comp_name like '%OLAP%';

set serveroutput on
EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);

How to find the version of the Export and Import utilities.

The version of the Export and Import utilities can be obtained by invoking the utilities with the parameter HELP=Y. The banner of the help page shows the version of the utility. For example:

> exp HELP=Y
Export: Release 8.1.7.4.1 - ... (other output)

> imp HELP=Y
Import: Release 9.2.0.8.0 - ... (other output)

> expdp HELP=Y
Export: Release 10.1.0.5.0 - ... (other output)

> impdp HELP=Y
Import: Release 10.2.0.3.0 - ... (other output)