|Oracle9i Database Administrator's Guide
Release 1 (9.0.1)
Part Number A90117-01
This section introduces new administrative features of Oracle9i Release 1 (9.0.1) that are discussed in this book and provides pointers to additional information.
For a summary of all new features for Oracle9i, see Oracle9i Database New Features.
The following section describes the new features discussed in the Oracle9i Database Administrator's Guide.
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.
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.
ANALYZE statement can now perform validation while DML is ongoing within the object being analyzed.
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.
The maximum number of destinations to which you can archive the online redo log, has been increased from 5 to 10.
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
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
K_CACHE_SIZE initialization parameters allow you to specify the sizes of buffer subcaches for the additional block sizes.
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.
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
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.
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.
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.
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.
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
Oracle now allows you to assign a name to a transaction. The transaction name is helpful in resolving in-doubt distributed transactions, and replaces a
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.
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.
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.
In this release, support has been added for partitioning index-organized tables by the hash method. Previously, they could be partitioned, but only by the range method.
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:
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
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:
SQL_REDOcolumn. Information regarding the original database user is also returned.
SQL_UNDOwith 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:
V$LOGMNR_CONTENTSdata 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".