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.