Oracle9i Release 1 (9.0.1) New Features

Oracle9i brings a major new release of the Oracle database server. It includes features to make the database more available. More online operations reduce the need for offline maintenance. Management of the database requires less effort. Oracle9i can automatically create and manage the underlying operating system files required by the database. There is a theme of self management.

Performance is enhanced. The Database Resource Manager has new options that allow for more granular control of resources. The performance level required of a resource consumer group can be better sustained. Partitioning enhancements allow tables and indexes to be better partitioned for performance. Security enhancements are an important part of this release. Applications have available more and finer grained methods of implementing security and auditing.

The following are summaries of the new features of Oracle9i that are discussed in this book.

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.

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.

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.

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.

Oracle provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables you to take corrective action, instead of the Oracle database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes.

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.

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.

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.

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.

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.

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_ninitialization 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.

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.

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.

Enhancements to the USING INDEX clause of CREATE TABLE or ALTER TABLE allow you to specify the creation or use of a specific index when a unique or primary key constraint is created or enabled. Additionally, you can prevent the dropping of the index enforcing a unique or primary key constraint when the constraint is dropped or disabled.

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.

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.

The CREATE DATABASE statement now has a SET TIME_ZONE clause that allows you to set the time zone of the database as a displacement from UTC (Coordinated Universal Time--formerly Greenwich Mean Time). Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. Additionally, a new session parameter TIME_ZONE has been added to the SET clause of ALTER SESSION.

The Oracle 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.

Oracle introduces list partitioning, which enables you to specify a list of discrete values for the partitioning column in the description for each partition. The list partitioning method is specifically designed for modeling data distributions that follow discrete values. This cannot be easily done by range or hash partitioning.

The job queue process creation has been made dynamic so that only the required number of processes are created to execute the jobs that are ready for execution. A job queue coordinator background process (CJQ) dynamically spawns Jnnn processes to execute jobs.

The following new functionality has been added to the Database Resource Manager:

Ability to create an active session pool. This pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will abort.

Automatic switching of users from one group to another group based on administrator defined criteria. If a member of a particular group of users creates a session that executes for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.

Ability to prevent the execution of operations that are estimated to run for a longer time than a predefined limit

Ability to create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.

Oracle9i enables you to authorize a middle-tier server to act on behalf of a client. The GRANT CONNECT THROUGH clause of the ALTER USER statement specifies this functionality. You can also specify roles that the middle tier is permitted to activate when connecting as the client.

Oracle provides a mechanism by which roles granted to application users are enabled using a designated PL/SQL package. This feature introduces the IDENTIFIED USING package clause for the CREATE ROLE statement.

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.

LogMiner release 9.0.1 has added support for many new features. Some of the new features work with any redo log files from an Oracle 8.0 or later database. Other features only work with redo log files produced on Oracle9i or later.

New Features for Redo Log Files Generated by Oracle9i or Later

For any redo log files generated by Oracle9i or later, LogMiner now provides support for the following:

Viewing user-executed DDL in the SQL_REDO column. Information regarding the original database user is also returned.

Generating SQL_REDO and SQL_UNDO with primary key information for updates. That is, updated rows are identified by primary keys and ROWIDs (provided supplemental logging is enabled), thereby making it easier to apply the statements to a different database.

New Features for Redo Log Files Generated by Oracle Release 8.0 or Later

For any redo log files generated by Oracle release 8.0 or later, LogMiner now provides support for the following:

Limiting V$LOGMNR_CONTENTS data to rows belonging to committed transactions only. This option enables you to filter out rolled back transactions and transactions that are in progress. See the information about options in "Starting LogMiner".