Oracle Database 11g Release 1 (11.1) New Features

Simplified and improved automatic memory management

You can now set a single initialization parameter (MEMORY_TARGET) to indicate the total amount of memory that is to be allocated to the database (the SGA and instance PGA). The system then automatically and dynamically tunes all SGA and PGA components for optimal performance. You can still designate minimum sizes individually for the SGA and instance PGA.

New fault diagnosability infrastructure to prevent, detect, diagnose, and help resolve critical database errors

The goals of the fault diagnosability infrastructure are preventing and detecting problems (critical errors) proactively, limiting damage and interruptions after a problem is detected, reducing problem diagnostic time, reducing problem resolution time, and simplifying customer interaction with Oracle Support. The framework includes technologies such as health checks that run when a critical error occurs; proactive in-memory tracing for many database components to permit first-failure data capture; an Incident Packaging Service that packages all diagnostic data for a problem into a zip file for transmission to Oracle Support; and Enterprise Manager Support Workbench, which provides a graphical environment for investigating, reporting, and resolving problems. Also included is integration with the new SQL Repair Advisor, for diagnosing and repairing SQL-related problems, the SQL Test Case Builder, which gathers all required schema and environment information to enable a SQL problem to be reproduced on another Oracle database, and the Data Recovery Advisor, which helps diagnose, evaluate the impact of, and repair data corruptions and other data failures.

Invisible Indexes

Making an index invisible is an alternative to making it unusable or dropping it if you want to test whether overall performance will improve by removing an index. An invisible index is by default ignored by the optimizer, but unlike an unusable index, is maintained during DML statements. You have the option to change an initialization parameter at the system or session level to cause the optimizer to use invisible indexes.

Virtual columns

Tables can now include virtual columns. The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or subpartition key.

Enhanced security for password-based authentication by enabling use of mixed case in passwords.

Beginning with Oracle Database 11g Release 1, passwords in the password file are case sensitive unless you include the IGNORECASE = Y command-line argument.

Database resident connection pooling

Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which are the equivalent of a server foreground process and a database session combined. DRCP enables sharing of database connections across middle-tier processes on the same middle-tier host and across middle-tier hosts. This results in significant reduction in database resources needed to support a large number of client connections, thereby boosting the scalability of both middle-tier and database tiers.

Tablespace-level encryption

You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications. When you encrypt a tablespace, all tablespace blocks are encrypted. All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.

Finer-grained schema object dependencies for increased availability

Invalidation of dependent schema objects in response to changes in the objects they depend upon is greatly reduced in Oracle Database 11g, increasing application availability during maintenance, upgrades, and online table redefinition. Between a referenced object and each of its dependent objects, the database tracks the elements of the referenced object that are involved in the dependency. For example, if a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency. For each dependent of an object, if a change is made to the definition of any element involved in the dependency (including dropping the element), the dependent object is invalidated. Conversely, if changes are made only to definitions of elements that are not involved in the dependency, the dependent object remains valid.

Table compression now supported in OLTP environments

Compressed tables now support the following operations:

DML statements

Add and drop column

Result cache in the system global area

Results of queries and query fragments can be cached in memory in the result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

The result cache occupies memory in the shared pool.

Default automatic undo management mode

A newly installed 11g instance defaults to automatic undo management mode, and if the database is created with Database Configuration Assistant, an undo tablespace is automatically created. A null value for the UNDO_MANAGEMENT initialization parameter now defaults to automatic undo management.

Enhanced online index creation and rebuild

Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. This lock could delay other DML statements and therefore cause a performance spike. This lock is no longer required, making these online index operations fully transparent.

Ability to online redefine tables that have materialized view logs

Tables with materialized view logs can now be redefined online. Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.

Read-only tables

You can set any table to read-only mode with the ALTER TABLE statement. This provides an alternative to placing a table's containing tablespace in read-only mode.

Transportable tablespace enhancements

Data Pump now supports the transportable tablespace function for tablespaces with XMLType tables and with schema objects with XMLTypes.

Optimized ALTER TABLE...ADD COLUMN

For certain types of tables, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:

Add a NOT NULL column with a default value

Add a nullable column without a default value

Add a virtual column

Enhancements to initialization parameter management

The following enhancements are made to the handling of initialization parameters:

The server parameter file (SPFILE) has a new format for compliance with Oracle's HARD initiative. This initiative helps to prevent writing corrupted data to disk, and is implemented at the software and storage hardware levels.

New commands enable you to create a text initialization parameter file (PFILE) or server parameter file (SPFILE) from the current values of initialization parameters in memory.

Upon startup, values of initialization parameters are written to the alert log in such a way as to make it easy to copy and paste them to create a new PFILE.

The name and path of the PFILE or SPFILE used to start the instance is written to the alert log.

Oracle Database automatically resilvers a mirrored copy of the SPFILE when needed.

Data Definition Language (DDL) commands can wait for locks

You can now set a single initialization parameter, DDL_LOCK_TIMEOUT, to specify how long a DDL command waits for the exclusive locks that it requires on internal structures before it fails.

Oracle Database 10g Release 2 (10.2) New Features

Oracle Database 10g Release 2 (10.2) New Features

Transparent data encryption

With the CREATE TABLE or ALTER TABLE commands, you can specify table columns for which data is encrypted before being stored in the datafile. If users attempt to circumvent the database access control mechanisms by looking inside datafiles directly with operating system tools, encryption prevents such users from viewing sensitive data.

DML error logging

A new error logging clause for all DML statements enables certain types of errors (for example, constraint violations or data conversion errors) to be logged to an error logging table, allowing the statement to continue instead of terminating and rolling back.

Enhancements to Automatic Shared Memory Management

The Streams Pool is now also automatically tuned when automatic shared memory management is enabled. A new view, V$SGA_TARGET_ADVICE, provides information to help with tuning SGA_TARGET.


Improved automatic tuning of undo retention results in fewer "ORA-01555: snapshot too old" messages.

Automatic tuning of undo retention now always tunes for the maximum possible retention for the undo tablespace based on tablespace size and current system activity. This improves the success rate of Oracle Flashback operations and long-running queries.

The Segment Advisor now reports tables with excessive row chaining.

The Segment Advisor now runs automatically during the maintenance window.

Upon installation, a Scheduler job is automatically created to run the Segment Advisor during the maintenance window. You can also continue to run the Segment Advisor manually. The automatically run Segment Advisor (the "Automatic Segment Advisor") examines statistics on the entire database and selects segments and tablespaces to analyze. You can view recommendations with Enterprise Manager, with the DBA_ADVISOR_* views, or with the new DBMS_SPACE.ASA_RECOMMENDATIONS procedure.

Enhancements to the online segment shrink capability

Online segment shrink now supports:

LOB segments

IOT overflow segments (both standalone and as a dependent object of an IOT index segment)

Enhancements to online table redefinition

Online redefinition of tables now supports:

Redefining a single partition of a partitioned table

Clustered tables, Advanced Queuing queue tables, and materialized view logs

Object data types (objects, VARRAYs, and nested tables, including nested table dependent objects)

Check and NOT NULL constraints

Preservation of table statistics

Parallel execution for Long-to-LOB migration

In addition, dependent PL/SQL package recompilation is no longer required when the redefined table has the same number, types, and order of columns as the original table.


Support for XMLTypes in the transportable tablespace facility

Enhancements to space management

The DBMS_SPACE_ADMIN package provides new tools to troubleshoot space management problems


Control files no longer need to be recreated when changing certain configuration parameters

Control files can now dynamically grow in size when you increase the values of the following parameters: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES.

Tablespace low-space alert thresholds by free space remaining

Low-space alert thresholds for locally managed tablespaces can now be by percent full or by free space remaining (in KB). Free-space-remaining thresholds are more useful for very large tablespaces.

Automatic Storage Management (ASM) files are now accessible through an XML DB virtual folder

ASM files can now be accessed through the XML DB repository, either programmatically or with protocols like FTP and HTTP/WebDAV.

Automatic Storage Management now has a command-line utility (ASMCMD)

With ASMCMD you can easily view and manipulate files and directories within disk groups. ASMCMD can list the contents of disk groups, perform searches, create and remove directories and aliases, display space utilization, and more.

Automatic Storage Management (ASM) supports multiple database versions

ASM maintains forward and backward compatibility between most 10.x versions of Oracle Database and 10.x versions of ASM. That is, any combination of versions 10.1.x.y and 10.2.x.y for either the ASM instance or the database instance works correctly, with this caveat: For a 10.1.x.y database instance to connect to a 10.2.x.y ASM instance, the database must be version 10.1.0.3 or later.

The DBMS_FILE_TRANSFER package can now copy files between a local file system and an Automatic Storage Management (ASM) disk group.

The DBMS_FILE_TRANSFER package can use a local file system or an ASM disk group as the source or destination for a file transfer. You can now copy files from ASM to the file system or from the file system to ASM.

The ALTER DISKGROUP command has a new REBALANCE WAIT clause.

ALTER DISKGROUP commands that cause a rebalance of an ASM disk group—commands that add, drop, or resize disks, or the command that starts a manual rebalance operation—can now wait until the rebalance operation completes before returning. This is especially useful in scripts.

Oracle Database 10g(10.1) New Features

Oracle 10g(10.1) New Features

Bigfile tablespaces

Oracle Database lets you create single-file tablespaces, called bigfile tablespaces, which can contain up to 232 or 4G blocks. The benefits of bigfile tablespaces are the following:

They significantly enhance the storage capacity of an Oracle Database.

They reduce the number of datafiles needed for an ultra large database.

They simplify database management by providing datafile transparency.

Multiple default temporary tablespace support for SQL operations

You can create a temporary tablespace group that can be specifically assigned to users in the same way that a single temporary tablespace is assigned. A tablespace group can also be specified as the default temporary tablespace for the database.

Rename tablespace

The RENAME TO clause of the ALTER TABLESPACE statement enables you to rename tablespaces


Cross-platform transportable tablespaces

Tablespaces can be transported from one platform to another. The RMAN CONVERT command is used to do the conversion


SYSAUX tablespace

Oracle Database creates an auxiliary system tablespace called SYSAUX at database creation. This tablespace can be used by various Oracle Database features and products, rather than saving their data in separate tablespaces or in the SYSTEM tablespace.

Automatic Storage Management

Automatic Storage Management provides a logical volume manager integrated with Oracle Database, eliminating the need for you to purchase a third-party product. Oracle Database creates Oracle-managed files within user-defined disk groups that provide redundancy and striping.



Drop database

The new DROP DATABASE statement lets you delete a database and all of its files that are listed in the control file.


Oracle Flashback Transaction Query

This feature introduces the FLASHBACK_TRANSACTION_QUERY view, which lets you examine changes to the database at the transaction level. As a result, you can diagnose problems, perform analysis, and audit transactions.



Oracle Flashback Version Query

Using undo data stored in the database, you can now view multiple changes to one or more rows, along with the metadata for the changes.


Oracle Flashback Table

A new FLASHBACK TABLE statement lets you quickly recover a table to a point in time in the past without restoring a backup


Oracle Flashback Drop

Oracle Database now provides a way to restore accidentally dropped tables. When tables are dropped, they are placed into a recycle bin from which they can later be recovered.

Enhanced online redefinition

New procedures have been added to the DBMS_REDEFINITION package that automate the cloning of dependent objects such as indexes, triggers, privileges, and constraints. Some restrictions have been lifted, allowing more types of tables to be redefined.

Automatic statistics collection

You no longer need to specify the MONITORING keyword in the CREATE TABLE or ALTER TABLE statement to enable the automatic collecting of statistics for a table. Statistics are now collected automatically as controlled by the STATISTICS_LEVEL initialization parameter. Automatic statistics collection is the default.

Scheduler

Oracle Database provides advanced scheduling capabilities through the database Scheduler.

Simplified recovery through resetlogs

The format for archive log file naming, as specified by the ARCHIVE_LOG_FORMAT initialization parameter, now includes the resetlogs ID, which allows for easier recovery of a database from a previous backup.

Automated shared server configuration and simplified shared server configuration parameters.

You no longer need to preconfigure initialization parameters for shared server. Parameters can be configured dynamically, and most parameters are now limiting parameters to control resources. The recommended method for enabling shared server now is by setting SHARED_SERVERS initialization parameter, rather than the DISPATCHERS initialization parameter.

Consolidation of session-specific trace output

For shared server sessions, the trcsess command-line utility consolidates in one place the trace pertaining to a user session.

Block remote access to restricted instances

Remote access to a restricted instance through an Oracle Net listener is blocked.

Dynamic SGA enhancements

The JAVA_POOL_SIZE initialization parameter is now dynamic. There is a new STREAMS_POOL_SIZE initialization parameter, which is also dynamic. A new view, V$SGAINFO, provides a consolidated and concise summary of SGA information.

Irreversible database compatibility

In previous releases you were allowed to lower the compatibility setting for your database. Now, when you advance the compatibility of the database with the COMPATIBLE initialization parameter, you can no longer start the database using a lower compatibility setting, except by doing a point-in-time recovery to a time before the compatibility was advanced.

Flash recovery area

You can create a flash recovery area in your database where Oracle Database can store and manage files related to backup and recovery


Copying Files Using the Database Server

You do not have to use the operating system to copy database files. You can use the DBMS_FILE_TRANSFER package to copy files.

Deprecation of MAXTRANS physical attribute parameter

The MAXTRANS physical attribute parameter for database objects has been deprecated. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

Deprecation of use of rollback segments (manual undo management mode)

Manual undo management mode has been deprecated and is no longer documented in this book. Use an undo tablespace and automatic undo management instead.

Deprecation of the UNDO_SUPPRESS_ERRORS initialization parameter

When operating in automatic undo management mode, the database now ignores any manual undo management mode SQL statements instead of returning error messages.

Deprecation of the PARALLEL_AUTOMATIC_TUNING initialization parameter

Oracle Database provides defaults for the parallel execution initialization parameters that are adequate and tuned for most situations. The PARALLEL_AUTOMATIC_TUNING initialization parameter is now redundant and has been deprecated.

Oracle Database 9i(9.2) New Features

Oracle 9i(9.2) New Features

Automatic Server Parameter File Backup and Configuration

Recovery Manager can now automatically back up and restore the server parameter File (SPFILE) configuration file, simplifying the backup and restoration processes of the system.

Performance Tuning Intelligent Advisories

The current release of Oracle9i provides a number of built-in intelligent advisories for performance tuning. These advisories are set to go "out-of-the-box", and allow the administrator to simulate a variety of hypothetical scenarios. These advisories use minimal resources and are available though the standard SQL interface.

These advisories show Shared Pool usage to improve parse time and to minimize CPU usage. They also show SQL execution memory to shorten SQL execution time and to minimize unnecessary CPU and I/O usage.
An advisory for the Mean-Time-To-Recover setting makes it possible for the administrator to set time requirements to recover from a system failure without jeopardizing run-time performance.
An advisory for the PGA Aggregate Target makes it possible for the server to control dynamically the amount of PGA memory allotted to SQL work areas according to the PGA_AGGREGATE_TARGET limit that is set by the DBA


Actual Operation-Level Query Statistics

Performance tuning in the current release of Oracle9i is simplified with intelligent tuning features that provide actual operation-level query execution statistics rather than estimates. This new information helps database administrators identify what tables, partitions, and indexes are most heavily accessed.

Dynamic Sampling of Optimizer Statistics

At compile time, this feature, if enabled, will dynamically gather statistics if the existing statistics are incomplete or known to be inaccurate. With default settings, this feature addresses missing optimizer statistics, providing for a possible source of poor optimization. With optional settings, this feature addresses additional sources of inaccurate statistics. Dynamic sampling of optimizer statistics may improve performance by improving the quality of the statistics used by the query optimizer.

Locally Managed SYSTEM Tablespace

The SYSTEM tablespace can be locally managed to simplify the database administrator's tasks.

Specifying FORCE LOGGING Mode

The FORCE LOGGING clause of the CREATE DATABASE, CREATE CONTROLFILE, and CREATE TABLESPACE statement enables you to force redo log records to be written even when NOLOGGING has been specified in a DDL statement.

LogMiner Enhancements

LONG and LOB Datatype Support for Redo Logs
LONG and LOB datatypes are supported for redo logs generated on a release 9.2 or later Oracle database.

Supplemental Logging Turned Off by Default

Supplemental logging is turned off by default. This is a change from release 1 (9.0.1), in which minimal supplemental logging was turned on by default. In release 9.2, the level of supplemental logging desired must be specified.

New Formatting Options

Two new options have been added that affect the formatting of returned data. The DBMS_LOGMNR.NO_SQL_DELIMITER option suppresses the semi-colon at the end of SQL_REDO and SQL_UNDO statements. The DBMS_LOGMNR.PRINT_PRETTY_SQL option formats the reconstructed SQL statements so that they are easier to read.

New Option, DBMS_LOGMNR.CONTINUOUS_MINE, in LogMiner

A new option, DBMS_LOGMNR.CONTINUOUS_MINE, directs LogMiner to automatically add and mine redo log files that are archived after the LogMiner session has started.

DBMS_LOGMNR.NO_DICT_RESET_ONSELECT Option Unnecessary

Use of the DBMS_LOGMNR.NO_DICT_RESET_ONSELECT option is no longer necessary. When data definition language (DDL) tracking is enabled, LogMiner stores old metadata definitions so that a second select operation has all the needed metadata versions.


New Procedure, DBMS_LOGMNR_D.SET_TABLESPACE, in LogMiner

A new procedure, DBMS_LOGMNR_D.SET_TABLESPACE,recreates all LogMiner tables in a tablespace other than the default tablespace, SYSTEM.


Minimizing the Production Impact of Upgrading

The Oracle9i Database release 2 introduces new features to lessen the time spent upgrading applications and database systems. These new features include the ability to rename CONSTRAINTS and COLUMNS as well as a significant reduction in the recompilation of PL/SQL packages and procedures. The database system also provides for fast loading of wrapped source code, automatically determines to do nothing upon loading an unchanged package, view or synonym, and removes double invalidation and parallel compilation in dependency code.

Domain Indexes Enhancement

It is now possible to create and rebuild domain indexes and local domain index partitions in parallel.

Segment-level Statistics Collection

With the capacity to collect of segment-level statistics in Oracle9i release 2, users can specify that relevant statistics be collected on the segment level to pinpoint hot spots and performance bottlenecks in the system. You can query these statistics by using the V$SEGMENT_STATISTICS to do a SELECT * FROM V$SEGMENT_STATISTICS. You can also query V$SEGSTAT_NAME for statistics properties and V$SETSTAT for additional efficient access to statistics.

Oracle9i Database New Features

Oracle9i Database (9.0.1) New Features

Online redefinition of tables

The new DBMS_REDEFINITION PL/SQL package provides a mechanism to redefine tables online. When a table is redefined online, it is accessible to DML during much of the redefinition process. This provides a significant increase in availability compared to traditional methods of redefining tables that require tables to be taken offline.

ONLINE option for ANALYZE VALIDATE STRUCTURE statement

The ANALYZE statement can now perform validation while DML is ongoing within the object being analyzed.

Controlling Archive Lag


Oracle now provides a time-based means of switching the current online redo log group. In a primary/standby configuration, where all noncurrent logs of the primary site are archived and shipped to the standby database, this effectively limits the number of redo records, as measured in time, that will not be applied in the standby database.

Suspending a database

Oracle9i includes a database suspend/resume feature. The ALTER SYSTEM SUSPEND statement suspends a database by halting all input and output (I/O) to datafiles and control files. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state. The ALTER SYSTEM RESUME statement resumes normal database operation.

Quiescing a database

Oracle9i allows you to place the database into a quiesced state, where only DBA transactions, queries, or PL/SQL statements are allowed. This quiesced state allows you to perform administrative actions that cannot safely be done otherwise. The ALTER SYSTEM QUIESCE RESTRICTED statement places a database into a quiesced state.

More archiving destinations

The maximum number of destinations to which you can archive the online redo log, has been increased from 5 to 10.

Automatic segment- space management

Locally managed tablespaces allow extents to be managed automatically by Oracle. Oracle9i allows free and used space within segments stored in locally managed tablespaces to also be managed automatically. Using the SEGMENT SPACE MANAGEMENT clause of CREATE TABLESPACE you specify AUTO or MANUAL to specify the type of segment space management Oracle will use.

Update of global indexes when partition maintenance is performed

By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) global indexes. You must then rebuild the entire global index or, if partitioned, all of its partitions. Oracle9i allows you to override this default behavior. When you specify the UPDATE GLOBAL INDEX clause in your ALTER TABLE statement for the maintenance operation, the global index is updated in conjunction with the base table operation.

Multiple block sizes

Oracle now supports multiple block sizes. It has a standard block size, as set by the DB_BLOCK_SIZE initialization parameter, and additionally up to 4 nonstandard block sizes. Nonstandard block sizes are specified when creating tablespaces. The standard block size is used for the SYSTEM tablespace and most other tablespaces. Multiple block size support allows for the transporting of tablespaces with unlike block sizes between databases.

Dynamic buffer cache

The size of the buffer cache subcomponent of the System Global Area is now dynamic. The DB_BLOCK_BUFFERS initialization parameter has been replaced by a new dynamic parameter, DB_CACHE_SIZE, where the user specifies the size of the buffer subcache for the standard database block size. The buffer cache now consists of subcaches when multiple block sizes are specified for the database. Up to four DB_nK_CACHE_SIZE initialization parameters allow you to specify the sizes of buffer subcaches for the additional block sizes.

Dynamic SGA

The initialization parameters affecting the size of SGA have been made dynamic. It is possible to alter the size of SGA dynamically through an ALTER SYSTEM SET statement.

Automatic undo management

Historically, Oracle has used rollback segments to store undo. Undo is defined as information that can be used to roll back, or undo, changes to the database when necessary. Oracle now enables you to create an undo tablespace to store undo. Using an undo tablespace eliminates the complexities of managing rollback segment space, and enables you to exert control over how long undo is retained before being overwritten.

Oracle managed files

The Oracle managed files feature of Oracle9i eliminates the need for you to directly manage the files comprising an Oracle database. Through the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n initialization parameters, you specify the file system directory to be used for a particular type of file comprising a tablespace, online redo log file, or control file. Oracle then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.

Automatic deletion of datafiles

Oracle9i provides an option to automatically remove a tablespaces's operating system files (datafiles) when the tablespace is dropped using the DROP TABLESPACE statement. A similar option for the ALTER DATABASE TEMPFILE statement, causes deletion the operating system files associated with a temporary file.

Metadata API

A new PL/SQL package, DBMS_METADATA.GET_DDL, allows you to obtain metadata (in the form of DDL used to create the object) about a schema object.

External tables

Oracle9i allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. The CREATE TABLE ... ORGANIZATION EXTERNAL statement specifies metadata describing the external table. Oracle currently provides the ORACLE_LOADER access driver which provides data mapping capabilities that are a subset of the SQL*Loader control file syntax.

Server parameter file

Oracle has traditionally stored initialization parameters in a text initialization parameter file, often on a client machine. Starting with Oracle9i, you can elect to maintain initialization parameters in a server parameter file, which is a binary parameter file stored on the database server. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running persist across instance shutdown and startup.

Default temporary tablespace

The new DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement allows you to create a default temporary tablespace at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.

Database Configuration Assistant changes

The Database Configuration Assistant has been redesigned. It now provides templates, which are saved definitions of databases, from which you can generate your database. Oracle provides templates, or you can create your own templates by modifying existing ones, defining new ones, or by capturing the definition of an existing database.

When creating a database with the Database Configuration Assistant, you can either initially include, or later add as an option, Oracle's new Sample Schemas. These schemas are the basis for many of the examples used in Oracle documentation.

Monitoring index usage

A MONITORING USAGE clause has been added for the ALTER INDEX statement. It allows you to monitor an index to determine if it is actively being used.

Fine-grained auditing

In Oracle's traditional auditing methods, a fixed set of facts is recorded in the audit trail. Audit options can only be set to monitor access of objects or privileges. A new PL/SQL package, DBMS_FGA, allows applications to implement fine-grained auditing of data access based on content.

Oracle 8i Database New Features

Oracle 8i New Features

Drop Column

A new drop column capability allows the DBA to easily remove unused columns in the database. Previously, it was necessary to use the export/import utilities.

Locally Managed Tablespaces

Prior to Oracle8i, management of free and used extents within a tablespace relied heavily on data dictionary tables. Now, Oracle introduces a new mechanism for managing space within a tablespace: locally managed tablespaces. All extent information is tracked in the tablespace itself, using bitmaps.


Online Index Creation, Rebuild, and Defragmentation

Creating or rebuilding an index on a table improves query performance when completed, but can be a very time-consuming operation. For large tables, it can require several hours of downtime because it forces a lock on the table and prevents concurrent DML. This is called offline index build.

Oracle8i supports the online creation or rebuilding of an index that works for partitioned or nonpartitioned B*-tree indexes, including index-organized tables. Since the table is not locked, DML operations and queries can execute on the base table while the index is being built.

Alternatively, if you do not want to rebuild the index because of space or other considerations, then the new COALESCE keyword may be specified to defragment the index. In either case, performance is improved and space recovered.


Nonpartitioned Table Reorganization

A new MOVE clause used in an ALTER TABLE statement provides a means to easily reorganize a nonpartitioned table by allowing the user to move data into a new segment while preserving all views, privileges, etc. defined on the table. The operation is performed offline.

Online Read-Only Tablespaces

Oracle8i improves the performance of the operation that places a tablespace in read-only mode. A tablespace in Oracle8i can be placed in read-only mode when there are no outstanding transactions in that tablespace alone, unlike previous versions of Oracle where the operation completed only when there were no outstanding transactions in the entire database.


Temporary Tables

A temporary table is a table with session-specific or transaction-specific data. It is empty when the session or transaction begins, and discarded at the end of the session or transaction. Its definition is visible to all sessions but the data is visible to, and can be queried by, only the session that inserts the data into the table

Note: Above is not complete list of new features for more details please refer documentation.