|Oracle9i Database Administrator's Guide
Release 2 (9.2)
Part Number A96521-01
This section introduces new administrative features of Oracle9i Release 2 (9.2) 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, Release 2, further advances and refines the goals achieved by Oracle9i, Release 1.
The following are summaries of the new features of Oracle9i, Release 2, that are discussed in this book.
Oracle enables you to specify passwords for users
SYSTEM using the following
CREATE DATABASE clauses:
FORCE LOGGING clause of the
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.
RMAN can now be used to create backups of a server parameter file.
LogMiner release 9.2 has added support for several new features and changed some default behavior as follows:
LOBdatatypes are supported for redo logs generated on a release 9.2 or later Oracle database.
NO_SQL_DELIMITERoption suppresses the semi-colon at the end of
PRINT_PRETTY_SQLoption formats the reconstructed SQL statements so that they are easier to read.
CONTINUOUS_MINE, directs LogMiner to automatically add and mine redo log files that are archived after the LogMiner session has started.
NO_DICT_RESET_ONSELECToption is no longer necessary. When DDL tracking is enabled, LogMiner stores old metadata definitions so that a second select operation has all the needed metadata versions.
SET_TABLESPACE,recreates all LogMiner tables in a tablespace other than
SYSTEM, which is the default.
You can now create a locally managed
SYSTEM tablespace. This can be done at create database time by specifying the
EXTENT MANAGEMENT LOCAL clause of the
CREATE DATABASE statement, or you can migrate your existing
SYSTEM tablespace to locally managed by using the
With the introduction of host based Logical Volume Managers (LVM), and sophisticated storage subsystems that provide RAID features, it is not easy to determine file to device mapping. Oracle has created new views and a new DBMS_STORAGE_MAP package to enable you to map files to physical devices.
Existing table columns can now be renamed.
Existing constraints on tables can now be renamed.
You can now use the keyword
DEFAULT as the value-list descriptor for a partition defined for a list-partitioned table. This partition is used for inserting rows into the list-partitioned table when the partition key column does not match any of the literal values specified in the value-list descriptor for the partition.
This release introduces a new type of composite partitioning. You can now partition tables by the range-list method, where partitions are defined as range partitions and the subpartitions are defined as list partitions.
Under certain conditions, Oracle can perform a fast split operation that is more efficient than the normal split operation on a partition or subpartition.
All operations done by user
SYS (includes all
AS SYSDBA and
AS SYSOPER connections) can now be audited.
A new system privilege named
GRANT ANY OBJECT PRIVILEGE allows you to grant object privileges as if you were the owner. It will appear (in views) as if the object owner granted the privilege, but audit records show the real person who granted the privilege.
You can now create
LOB columns in tablespaces that specify automatic segment-space management.
In earlier releases of Oracle, the
DISTRIBUTED_TRANSACTIONS initialization parameter allowed you to specify a maximum number of distributed transactions in which a database can concurrently participate. This parameter has been eliminated and the number of concurrent distributed transactions is not limited. If specified, the
DISTRIBUTED_TRANSACTIONS initialization parameter is ignored.
Also in earlier releases of Oracle, the max number of branches for each distributed transaction was specified by the
MAX_TRANSACTION_BRANCHES initialization parameter. This parameter was eliminated in Oracle8i, but the maximum number of branches of a distributed transaction is still limited to 32. If specified, the
MAX_TRANSACTION_BRANCHES initialization parameter is ignored.
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
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.
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 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 "Start a LogMiner Session".