Release 8.0
A54650_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8
Server Migration Release 8.0 A54650_01 |
|
This chapter provides an overview of new features of the Oracle8 Server. Discussions in this chapter are organized under the following topics:
This section summarizes enhancements to the Oracle8 Server.
Oracle8 supports up to a maximum of 1000 user-defined columns per table, increased from Oracle7's support for 254 user-defined columns.
This section describes the parallel DML features of Oracle8.
Oracle8 provides the following set of parallel DML functionalities:
In addition to parallel DML, Oracle8 provides new serial functions for inserts:
Direct-load INSERT improves performance by formatting and writing disk blocks directly into datafiles, bypassing the buffer cache and space management bottlenecks. (This is similar to the Direct Loader utility.) Another benefit of direct-load INSERT is that you can load data without logging redo entries, improving the insert performance significantly.
Direct-load INSERT can be done either serially or in parallel. You turn it on by specifying an APPEND hint after the INSERT keyword. The APPEND hint is the default when PARALLEL is specified; use the NOAPPEND hint to override the default.
Direct-load INSERT can be used on partitioned or nonpartitioned tables, in serial or in parallel. Both parallel and serial modes require a commit or rollback to be executed immediately after the INSERT statement.
Insert statements of the type INSERT ... SELECT can be parallelized, but INSERT ... VALUES statements cannot. For parallel insertion of user data, you can use the Parallel Loader utility instead of direct-load INSERT.
Direct-load INSERT appends inserted data after the existing data in a table. Free space is not reused. With the conventional path, in contrast, free space in the object is reused, index maintenance can be performed, and referential integrity can be maintained. However, the conventional path for insertions is not parallelized.
Parallel DML has the following general restrictions:
Direct-load INSERT cannot be used if there are global indexes on the table. There are other restrictions: during parallel direct-load INSERT, no referential integrity is supported. During serial direct-load INSERT, only an explicit commit or rollback is allowed; see Oracle8 Server Concepts.
Oracle8 reduces the time needed to recover from a failed parallel DML operation by performing the recovery operation in parallel. For example, a parallel update that aborts (perhaps for running out of space) during its last record update will roll back in parallel.
Oracle8 supports parallel transaction recovery (undo recovery) during transaction and process failures, but not during instance and system failures.
This section describes new SQL syntax that supports parallel DML. More detailed information is found in Oracle8 Server Concepts and Oracle8 Server Tuning.
The ENABLE/DISABLE PARALLEL DML clause of the ALTER SESSION command enables or disables subsequent DML statements in the session to execute in parallel.
When PARALLEL DML mode is enabled in a session, all DML portions of the statements in this session are considered for parallel execution. However, even if PARALLEL DML mode is set, the DML operation may still execute serially due to the absence of parallel hints or clauses or due to restriction violations.
New hints specify the APPEND and PARALLEL attributes of the INSERT operation. If any of the parallel or append restrictions are violated, Oracle ignores the corresponding hint. INSERT hints are placed immediately after the INSERT keyword.
Note: The NOLOGGING attribute of the INSERT operation is not specified in INSERT hints, but is instead specified at the table, index, or tablespace level using a CREATE or ALTER command.
If the APPEND hint is used, either explicitly or by default, the insert operation appends data directly beyond the high water mark of the segments (for a partitioned table) or inserts data into temporary segments. The operation then merges the temporary segments and appends them to the table segment (for a nonpartitioned table). Existing free blocks in the table segment are not used.
APPEND is the default if parallelism is specified by either the PARALLEL hint or the PARALLEL clause.
The NOAPPEND hint explicitly blocks the APPEND mode for an insert operation. When the table being inserted into has a PARALLEL clause, you can use the NOAPPEND hint to override the default APPEND mode.
The PARALLEL hint specifies the degree of parallelism (the number of query processes) used to access a particular table in a query or DML statement. In Oracle7, this hint was allowed only after an UPDATE, DELETE, or SELECT keyword (not after the INSERT keyword). In Oracle8, the hint syntax stays the same for parallel DML, but the location and semantics of the hint have changed.
The PARALLEL clause specifies table parallelism in the CREATE TABLE and ALTER TABLE commands. In Oracle8, if this clause exists in a table definition, it determines the parallelism of both DML statements and queries. Explicit PARALLEL hints for a table in a command override the effect of PARALLEL clauses.
A new LOGGING/NOLOGGING clause is available in Oracle8 at the tablespace, table, index, and partition levels.
This clause is similar to the SQL*Loader option [UN]RECOVERABLE and the SQL options CREATE TABLE [UN]RECOVERABLE and CREATE/REBUILD INDEX [UN]RECOVERABLE; no redo log is generated for an operation if the NOLOGGING or UNRECOVERABLE option is chosen.
The parallel clauses specified in table definitions which were used for parallelizing queries in Oracle7 are in Oracle8 used also for parallelizing inserts, updates, and deletes. Existing Oracle7 INSERT ... SELECT statements that parallelize their query operations may have their insert parallelized without introducing new hints, and existing Oracle7 update/delete statements that parallelize their query operations may parallelize their update/delete operations. However, you must explicitly enable the session or transaction for PARALLEL DML to get this new behavior.
To maintain existing behavior in a PARALLEL DML enabled session, an explicit NOPARALLEL hint will have to be specified in the statement.
The CREATE TABLE ... AS SELECT statement contains a CREATE part (DDL) and a SELECT part (query). In Oracle7, only the query part can be parallelized. The decision to parallelize the query is determined in Oracle7 by the parallel clause specification in the statement or the parallel hint specifications or the parallel declaration of the tables involved in the select.
Oracle8 can parallelize both parts of the statement. The query part can be parallelized if the query includes a "parallel" hint specification (PARALLEL or PARALLEL_INDEX), or the CREATE part of the statement has a PARALLEL clause specification, or the schema objects referred to in the query have a PARALLEL declaration associated with them.
The CREATE part of the statement can be parallelized in Oracle8 only if it has a PARALLEL clause specification.
This section describes changes to the data dictionary views for parallel DML.
Several Oracle8 dictionary views display information about the LOGGING/NOLOGGING clauses of statements that create or alter tablespaces, tables, indexes, and partitions.
A new LOGGING attribute with the values NOLOGGING and LOGGING has been added to the following data dictionary views:
A new LOGGING attribute with the values YES and NO has been added to the following data dictionary views:
The following fixed views contain new information about parallel DML:
Parallel DML operations are used primarily to speed up large DML operations against large database objects. Large DML operations change at least one per cent of the rows in an object. Large database objects are tables, indexes, and clusters that have at least a few gigabytes of data.
Parallel DML is used in decision support system (DSS) environments where the performance and scalability of accessing large objects are important. In Oracle7, parallel query enables DSS customers to query large objects efficiently; however, they do not get the same performance when updating these large objects. In Oracle8, parallel DML complements parallel query in providing both querying and updating capabilities for your DSS databases.
The overhead of setting up parallelism makes parallel DML operations impractical for short OLTP transactions. However, parallel DML operations can speed up batch jobs running in an OLTP database. Parallel DML can also speed up the loading of large OLTP tables.
The following types of applications can benefit from the use of parallel DML:
This section summarizes enhancements to the Oracle8 partitioning features. For information on partitioning, see Oracle8 Server Administrator's Guide, Oracle8 Server Concepts, and Oracle8 Server Tuning.
Partitioned tables and partitioned indexes let you divide large tables and indexes into smaller and more manageable pieces (partitions). Oracle8 includes features to create partitioned tables and indexes, to manage them on an ongoing basis, to back up and recover them, and to exploit them for better query and DML performance.
The Oracle8 SQL syntax has been expanded to support partitioned tables, including a variety of operations on individual partitions. The new syntax adds PARTITION to expand several commands.
All partitions have the same logical attributes, such as the column and constraint definitions for tables; however, they can have different physical attributes (PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, and STORAGE). Each partition is stored in a separate segment and can reside in a separate tablespace.
Storing partitions in separate tablespaces has the following advantages:
You can control how data is spread across physical devices by specifying where to store partitions of a table or index. With this level of control, you can accommodate the special needs of applications that require fast response time, by reducing disk contention and using faster devices. Infrequently accessed data, such as old historical data, can be moved to slow disks or stored in subsystems that support a storage hierarchy.
Range partitioning uses ranges of column values to map rows or index entries to partitions. The syntax for range partitioning includes the partitioning specification for the object (table or index) and the partitioning specifications for each individual partition.
An ordered list of partitioning columns determines which partition a row or index entry belongs to. Oracle8 supports up to 16 partitioning columns.
The values in the partitioning columns of a particular row or index entry are called its partitioning key, which is specified by the VALUES LESS THAN clause. Collectively, the VALUES LESS THAN clauses define an ordering of the partitions in a table or index. For example, the "first" partition is the partition with the lowest VALUES LESS THAN and the "highest" partition is the partition with the highest VALUES LESS THAN.
Oracle8 introduces partition maintenance operations, which operate on an individual partition rather than on an entire object. Such operations can reduce the impact of scheduled downtime for maintenance operations because they are faster than full object (table or index) maintenance operations.
To further reduce downtime, a partition maintenance operation can take advantage of performance features that are available for table and index-level maintenance operations, such as the PARALLEL, NOLOGGING, and DIRECT options, where applicable.
Partition maintenance operations provide partition independence, that is, they allow concurrent maintenance operations on different partitions of an object at the same time you perform SELECT and DML operations against partitions that are unaffected by maintenance operations. Partitioned objects can reduce the window of unavailability on partitions affected by data movement to a short time, provided there are no interpartition stored constructs such as global indexes or referential integrity constraints.
To provide partition independence, Oracle8 provides DML partition locks, which protect the data in an individual partition during concurrent use by multiple users. A partition lock halts all update activity on the target partition until the partition lock is released. Partition locks fall between table locks and row locks in the DML locking hierarchy. The V$LOCK dynamic performance table provides information about partition-level DML locks.
To improve performance in the Oracle Parallel Server (OPS) environment, you can turn off DML locking on selected tables with the ALTER TABLE DISABLE TABLE LOCK statement. This statement disables both table and partition DML locks. DDL statements are not allowed when DML locking is disabled.
Partitioned tables can improve the performance of DSS queries on very large tables, because an ad-hoc query that requires only rows corresponding to a single partition (or range of partitions) can use a partition scan rather than a table scan. This improves response time and may also substantially reduce the temporary disk space requirement for queries that require sorts.
You can range partition an index if:
You can mix partitioned and nonpartitioned indexes with partitioned and nonpartitioned tables. That is, a partitioned table can have either partitioned or nonpartitioned indexes, or both; and a nonpartitioned table can have partitioned or nonpartitioned indexes, or both.
Oracle8 supports the following three types of range-partitioned indexes:
Both types of local indexes are equi-partitioned with the underlying table. (Equi-partitioned objects have identical logical partitioning attributes.) Oracle partitions the index on the same columns as the underlying table, creates the same number of partitions, and gives them the same partition bounds as corresponding partitions of the underlying table.
A local prefixed index is equi-partitioned with the underlying table and also partitioned on a left prefix of the index columns. Local prefixed indexes can be unique or nonunique.
A local nonprefixed index is equi-partitioned with the underlying table but partitioned on something other than a left prefix of the index columns. In this release, local nonprefixed indexes can be unique only if the partitioning key is a subset of the index key.
The keys of a global may refer to rows stored in more than one underlying partition. An index is global prefixed if it is partitioned on the left prefix of the index columns.
With partition-level Export/Import you can export and import all or a subset of the partitions of a table and its indexes. You can use this feature to limit the scope and duration of maintenance operations, and you can perform parallel maintenance operations on partition boundaries.
Partition-level Export/Import provides the flexibility of grouping or changing physical attributes of selected partitions. You can also delay building a partitioned index on Import, using the INDEXES and INDEXFILE options, thus allowing more efficient index maintenance.
Using partition-level Import, you can perform parallel Import along partition boundaries by operating multiple Import sessions, each working on a partition of the same table.
In Oracle8, both the conventional path and the direct path support two types of Export: Table Export and Partition Export.
With Table Export you can export entire partitioned or nonpartitioned tables, and their associated objects. All three modes of Export (Full, User, Table) support this option.
With Partition Export you can export individual partitions of a partitioned table. Only Table-mode Export supports this option. Incremental Exports (incremental, cumulative, and full) can be done only in Full Database mode, so Partition Export cannot be used for incremental export purposes.
In Oracle8, exported tables and partitions can be imported into tables and partitions at two levels: Table Import and Partition Import.
With Table Import you can import all data in a dump file into a partitioned or nonpartitioned table. Table Import is supported regardless of the mode of Export used to export the data (Full, User, or Table).
With Partition Import you can import the exported partition(s) of a table or the entire exported nonpartitioned table into the partition(s) of the table or the entire nonpartitioned table. Data from one partition of a table can be imported into another partition of the same table only. Partition Import is supported only in Table-mode Import.
SQL*Loader provides the following support for partitioned tables:
The SQL*Loader control file syntax is extended by the addition of the PARTITION keyword. This syntax can be used to load a nonpartitioned table (by omitting the PARTITION keyword), a single partition of a partitioned table, or multiple partitions (a subset of the partitions) of a partitioned table through appropriate use of the SQL*Loader WHEN clause.
The Oracle8 partition exchange operator (ALTER TABLE ... EXCHANGE PARTITION) converts a partition to a table or a table to a partition by exchanging their data segments (and optionally index segments). The table cannot be part of a cluster, and it must be unpartitioned. You can use the partition exchange operator to:
In Oracle8, partitioned tables are to be used instead of partition views.
Oracle8 partitioned tables provide most of the functionality of Oracle7 partition views while also providing additional performance and manageability features. However, on databases to be migrated to Oracle8, be careful to avoid using the following aspects of partition views:
Oracle8 ROWIDs embody new internal and external formats that contain the additional parameters necessary to handle two new Oracle8 features, partitioned tables and tablespace-relative data block addresses (DBAs).
The following Oracle8 ROWID features are important to those who rely on ROWID or use ROWID type columns in applications:
Oracle8 databases automatically use tablespace-relative DBAs-data block addresses that contain a relative, rather than absolute, file number. Tablespace-relative DBAs provide the following functionality:
As a result, Oracle8 databases have performance comparable to Oracle7 databases with the same number of files.
The use of tablespace-relative DBAs is usually invisible to the user. However, user-visible changes in the following areas might need user attention under some circumstances:
Relative file number is a new Oracle8 file attribute. Its introduction required certain changes in the SQL dictionaries as follows:
In general, Oracle8 uses file numbers in views as follows:
Most Oracle8 dictionary base tables contain the relative file number. Its inclusion has more to do with Oracle8 internal architecture than with user-related issues. Nevertheless, some user-visible consequences result from this inclusion:
Oracle8's preservation of absolute file numbers in the dictionary views and V$ tables is compatible with the Oracle7 dictionary format. It also provides a compact alias (a single numeric value) for the file.
Base dictionary tables should not be accessed by regular users. However, some of the changes are described here to make subsequent discussion of views easier to understand.
In Oracle8, the Oracle7 FILE$ dictionary table (which describes datafiles) has been changed by adding the relative file number as a new file attribute and by merging in the Oracle7 FILEXT$ table (which describes autoextensible files). Thus, the Oracle8 FILE$ dictionary table contains the following columns in addition to the contents of the Oracle7 FILE$ table:
|
RELFILE# |
NUMBER |
Tablespace-relative file number |
|
MAXEXTEND |
NUMBER |
Maximum file size |
|
INC |
NUMBER |
Increment amount |
In Oracle8, the UNDO$ base table (which describes rollback segments) has an added tablespace number so the relative file number of the segment header is unambiguous. Thus, the Oracle8 UNDO$ table contains the following column in addition to the contents of the Oracle7 UNDO$ table:
|
TS# |
NUMBER |
Tablespace number for the tablespace in which the segment resides |
Dynamic performance tables, which are actually views, are defined on the fixed X$ tables. Some of the new fields added to the X$ tables as part of Oracle8 enhancements are visible through the V$ views, particularly as follows.
The V$SORT_SEGMENT view on the X$KTSTSSD table is a straightforward select from X$KTSTSSD, which describes the sort segments and has been expanded by the relative file number of the sort segment header:
|
RELATIVE_FNO |
NUMBER |
Relative file number of the sort segment header |
Oracle8 customers using the Oracle Parallel Server (OPS) are supported by the new nontransactional application failover capability. Application failover makes the clients' sessions automatically connect with a backup instance and continue operation following a connection loss (that is, "fail over"). Using the Oracle8 application failover capability:
To minimize impact on applications, most configuration for failover is done at the Oracle Names server. Customers can control how much failover is done by using the following options with the TYPE keyword:
Ensuring fast failover performance can invoke a price on normal operation speeds because of the need for running the backup instance. Oracle8 has different performance/price options for application failover, to be selected and configured by the database administrator in the Oracle Names server. Use one of the following options with the METHOD keyword:
The Heterogeneous Services (HS) extend the Oracle8 server architecture so that clients connected to the server can have direct access to non-Oracle data sources. The non-Oracle data source appears as a remote server, which customers can access through the HS using database links. The HS translates Oracle's remote data access requests to an interface that has no kernel dependencies and is easy to implement.
An implementation of the API is called an HS agent. An HS agent can be implemented using the non-Oracle data source native interface (for example, embedded SQL) or using ODBC. Using the ODBC interface requires relatively little additional programming, mainly fine tuning the Oracle-delivered ODBC code.
The HS reduces the amount of generic gateway code by 50% to 80%, improves performance, and opens the door for future enhancements.
New features developed for the kernel contribute to the HS without the need for the HS to duplicate those features. The Heterogeneous Services:
Oracle8 gives the database administrator an integrated set of backup and restore features for the DBMS, including support for incremental backups:
These features are a major improvement over the pre-Oracle operating design of relying on underlying operating system facilities and possible third-party applications to help manage backup and restore.
The Oracle kernel enhancements supporting backup and restore operations complement the other Oracle8 backup and restore enhancement utility, the Recovery Manager.
The new Oracle8 backup and restore utility suite addresses numerous database administration issues by providing an integrated method for backing up and restoring an Oracle database.
Recovery Manager is both a stand-alone utility and an embedded library. In the stand-alone form, Recovery Manager acts as a command-line interpreter and is controlled by a command language. The library form is intended for link-editing within a Server Manager agent. This allows Server Manager to act as a GUI front end to Recovery Manager. The embeddable components are:
For more information about backup and recovery see the Oracle8 Server Backup and Recovery Guide.
A new Oracle8 Server Manager command option, SHUTDOWN TRANSACTION <time-out>, gives clients the length of the specified time-out period to finish transactions. During this time, no client can start a new transaction on this instance; a client must instead failover to a backup instance to start a transaction. At the end of the time-out, any client still connected to the primary instance is disconnected and automatically reconnects to the backup; the primary instance then shuts down. If time-out is not specified, clients are not disconnected until the last transaction completes.
For more information on this command, see Oracle Server Manager User's Guide.
This command disconnects a session on the first call after its current transaction has been finished. The syntax is:
ALTER SYSTEM DISCONNECT SESSION <SID,serial#> POST_TRANSACTION
SID and serial# are the values of the columns with those names in the V$SESSION view.
The client will get the same error code as it does with Oracle7 when this command is invoked. The application failover code will recognize this error and perform failover.
For more information on this command, see Oracle8 Server SQL Reference.
Oracle8 implements a new parameter for tablespace extent management: MINIMUM EXTENT <integer>. This parameter gives database administrators a mechanism for managing fragmentation of space. It is used with the CREATE/ALTER TABLESPACE commands, with integer designating a number of bytes. (A new column MIN_EXTLEN has been added to the Oracle8 views DBA_TABLESPACES and USER_TABLESPACES to contain this value.)
An Oracle database comprises logical storage units called tablespaces. A tablespace groups related logical structures. Each tablespace consists of segments, which are the spaces within the tablespace allocated to individual logical structures. Space for a segment is managed using extents, which in turn are a specific number of contiguous data blocks. When Oracle allocates new extents to a segment, the free extent closest in size to the required extent is allocated, sometimes splitting a larger free extent into two pieces. If the unused remainder piece is small, it may be unusable by any other segment. To avoid the creation of unusably small remainder extents, users can set a minimum parameter for the tablespace, which will ensure that all extents in the tablespace (used and free) are a multiple of minimum.
When MINIMUM EXTENTS has been specified, Oracle converts the value specified in integer from bytes into data blocks, and rounds up so that all extents created are some whole number multiple of the specified minimum. Users can invoke this parameter to reduce unnecessary fragmentation of space when doing parallel operations such as load. This parameter also provides users the facility to avoid random I/O when scanning tables.
Oracle8 provides new security features related to the establishment, verification, and use of global user accounts. Database servers can "trust" each other, based on rigorous mutual authentication by way of the Oracle Security Server. This trust allows implementation of previously impossible functionalities. For example, Oracle8 can use proxy user authentication between servers, solving distributed security problems based on the need for constant reauthentication.
For more information on maintaining security in distributed systems, see The Oracle Security Server Guide.
Oracle databases can require that the data obey certain rules, called constraints. The database signals an error when it checks and finds any data inconsistent with a constraint. Oracle7.x databases check constraints at the end of execution of each DML statement. Oracle8 expands that functionality to allow deferred constraint checking, in compliance with ANSI SQL `92 standard syntax and semantics.
When a constraint is defined (in a CREATE/ALTER TABLE statement), you can use the CONSTRAINT clause to specify whether the constraint is
NOT DEFERRABLE can be used in conjunction with INITIALLY IMMEDIATE, but cannot be used with INITIALLY DEFERRED.
After initial definition of a constraint, you can use the SET CONSTRAINTS command to set constraint checking to immediate (in all cases) or deferred (unless the constraint was initially defined as NOT DEFERRABLE). You can also set constraint to be immediate at the end of a transaction in order to check whether COMMIT can succeed. However, such a statement is disallowed inside a trigger.
Oracle8 also lets you place deferred UNIQUE and FOREIGN KEY constraints on snapshots, allowing fast and complete refresh to complete successfully.
Oracle8 introduces the current user database link, a new kind of database link that contains no user credentials but allows a connection to be established as the current user at the remote end. This current user must be an authenticated global user with a global user account on the remote database. Both servers must be members in a properly set up distributed system; see "Maintaining Security in Distributed Systems" on page A-20).
With the current user database links feature, an Oracle8 database can provide three kinds of database link connections:
Using such connections, a link can support a dynamic security context switch back to the calling (connected) user, to the defining user (no switch actually, because this is the same context as for the rest of the stored object), or to any named user. By embedding the database link in a stored object (such as a procedure, view, or trigger), the owner/user of the stored object can ensure that connection is made as the owner/user. This is because when any user runs a stored object, the privileges of the object owner are used. Hence, the current user becomes the object owner.
Current user database links provide the additional ability to designate some databases in a distributed system as trusted to connect only as specific global users (by using a list of trusted <database,user> pairs at the central authority, shared by all servers in the system, and a list of locally untrusted pairs kept with each database). By default, an Oracle8 server abides by the information stored at the central authority; that is, it trusts exactly those databases for exactly those users. The default behavior occurs when the untrusted list is empty-for example, after database creation before any entries have been added to the list.
An Oracle8 server provides enough audit information to trace current user links back to the originating connected user. The links are traceable backwards first to the connected user on the destination database (which is the current user on the originating database) and then to the connected user on the originating database.
A reverse-key index reverses the bytes of each column indexed (except the ROWID) but keeps the column order. Such an arrangement can help avoid performance degradation in indexes in an Oracle Parallel Server environment, where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index. Although this arrangement hypothetically could cause cache thrashing and degrade performance, under some circumstances the reduced contention for the hot index blocks can actually improve performance, compared with standard indexing.
When you use reverse-key indexing, you cannot run an index range scanning query on the index. Lexically adjacent keys are not stored next to each other in a reverse-key index, so only fetch-by-key or full-index (table) scans can be performed.
Under some circumstances, using a reverse-key index can make an OLTP Oracle8 Parallel Server application faster. An example is keeping the index of messages in an application: some users keep old messages, and the index must maintain pointers to these as well as to the most recent messages.
The new REVERSE keyword in Oracle8 SQL provides a simple mechanism for easily creating or turning an existing index into a reverse-key index:
CREATE INDEX i ON t (a,b,c) REVERSE; ALTER INDEX i REBUILD REVERSE; ALTER INDEX i REBUILD NOREVERSE;
The keyword REVERSE can be specified along with the optional index specifications in CREATE INDEX and ALTER INDEX statements.
The keyword NOREVERSE can be specified (it is used by default, even if not specified) to REBUILD or CREATE an index that is not reverse keyed. However, rebuilding a reverse-key index without the NOREVERSE keyword produces a rebuilt, reverse-key index.
Oracle8 defaults character set information correctly to indicate the database character set. Normally this function is invisible; however, SELECT statements on the views discussed in "Dictionary Views" on page A-36 may now return the CHARACTER_SET_NAME column or the NLS_NCHAR_CHARACTERSET row.
A V6 or Oracle7 client (which may itself be a server) can interact with an Oracle8 server that holds data in the national character set. If the national character output data passes through a bind or define handle, the OCI handles the conversion to the client's database character set invisibly. If the data is needed as an input bind value, and is used where only a national character set string is allowed, the SQL or PL/SQL code using the value should surround the use of the bind variable, which will be perceived as having the database character set, with a call to TRANSLATE() to convert it to the national character set. The client is restricted in this case to passing the data in the client's database character set, which may not have all characters of the national character set.
Unless a database administrator declares some columns, attributes, or PL/SQL variables, parameters, or return results as using the national character set, this feature will be almost totally invisible to that administrator and to other users.
Oracle's introduction of its ORDBMS in Oracle8 is evolutionary rather than revolutionary. For example, the ORDBMS:
The first evolutionary step is to extend the set of basic built-in types into an object-oriented, extensible type system.
The Oracle8 Export/Import utility lets users upgrade database objects across database systems and releases as well as reclaim space.
The object support feature of Oracle8 Export/Import enables users to export and import objects and types and references to objects and types that are stored in a database. In particular, object support enables the export and import of:
Oracle8 object support:
Oracle8 object Export/Import imposes no restrictions on:
The limits on these numbers derive from the amount of memory and disk space available to the user.
Import continues the pre-Oracle8 restriction that an entire row (except for LOBs) must fit within memory prior to being inserted into the database.
Export continues the pre-Oracle8 restriction that an entire column (except for longs and LOBs) must fit within memory prior to being written to the dump file.
The Oracle8 SQL*Loader is enhanced to support loading partitioned objects into the database. A partitioned object in Oracle8 is a table or index divided into pieces (called partitions) that are typically grouped by common logical attributes. For example, sales data for the year 1994 might be partitioned by month. The data for each month is stored in a separate partition of the sales table. Each partition is stored in a separate segment of the database and can have different physical attributes.
Partitioned object support in the SQL*Loader enables the SQL*Loader to load a single partition of a partitioned table, all partitions of a partitioned table, or an entire nonpartitioned table.
SQL*Loader can operate in three major modes:
The Oracle8 SQL*Loader supports partitioned objects on all three paths.
Oracle8 Server Image Cartridge is an extension to Oracle8 Server and provides image storage, retrieval, and format conversion capabilities through an object datatype (ODT). This cartridge supports image storage using binary large objects (BLOBs) and references to image data residing in external files (BFILEs).
Image Cartridge is a building block for various imaging applications rather than an end-user application in itself. It consists of ODTs along with related methods for managing and processing image data. Some example applications for this cartridge are:
Each such use has its own distinct requirements, but all also have common attributes. The purpose of the image ODTs is to support both their uniqueness and their commonality. With Oracle8 Server Image Cartridge, managing images can be as easy as managing other standard attribute data.
For more information, please refer to the Oracle8 Server Image Cartridge Users Guide.
Oracle8 Spatial Cartridge provides an integrated set of functions and procedures that allow you to store, retrieve, and analyze spatial data quickly and efficiently in an Oracle database. The following new features are provided for enhanced functionality:
Oracle8 Server Spatial Cartridge supports three basic geometric forms that represent data:
The Oracle8 Server provides many new replication features in four basic categories:
For details refer to the Oracle8 Server Replication.
Oracle8 provides significant performance improvements with the following new features:
Snapshots defined with some types of subqueries can now be fast refreshed, which enables subsets of data to be defined and maintained easily. This feature is important for mass deployment applications, such as salesforce or branch automation.
Oracle8 supports the replication of the following types of large objects:
Oracle8 facilitates database management with the following new features:
The new Oracle8 password management facilities give users enhanced ability to maintain and improve the security of the system by managing passwords in a more controlled manner.
Oracle8 provides the following password functionalities:
The DBA can use the Oracle8 CREATE PROFILE statement to specify different PL/SQL routines for users (or groups of users). A profile sets the limits on use of available database resources. Assigned to a user, the profile ensures that the user cannot exceed the specified limits. The Oracle8 default profile, DEFAULT, initially defines unlimited resources. Any user not explicitly assigned a different profile is automatically subject to the limits in the DEFAULT profile. The DBA can change these default limits and can use the CREATE PROFILE statement to create other profiles.
In Oracle8 the CREATE PROFILE statement is enhanced by addition of the following limiting parameters:
Note: Password management acts the same whether resource limitation is enabled or disabled by either the RESOURCE_LIMIT initialization parameter or the ALTER SYSTEM statement.
These catalog views have been enhanced to include the following columns for Oracle8 password management:
Password resource parameters include the following new Oracle8 column:
This new catalog view includes:
Oracle8 provides new features for working with its enhanced password management capabilities:
To use Oracle8 password management features with an Oracle7 application, the following changes to the application are required:
An Oracle8 client can be coded to work with pre-Oracle8 servers, but, of course, this client cannot use any new Oracle8 features.
An Oracle7 client uses Oracle7 OCI calls to connect to the server, so it cannot detect password expiration. But other features of password management do work for Oracle7 clients. If the Oracle7 logon is replaced by the Oracle8 logon, then password expiration can be detected and password management can function fully with minimal change in the application.
Oracle8 server-managed recovery comprises two new components on the Oracle Server:
The new Oracle8 Recovery Manager utility manages the processes of creating backups and restoring or recovering from them. It manages only databases created with Oracle8 or migrated to Oracle8. The recovery catalog database also must be an Oracle8 database.
The work of actually creating and restoring from backup files is done inside the Oracle8 Server. Recovery Manager uses a special PL/SQL interface to the Server to invoke the backup and restore functions.
Recovery Manager maintains a recovery catalog containing information about backup files and archived log files. It uses the recovery catalog to automate both restore operations and media recovery.
Besides using the recovery catalog to restore backups and recover the database, Recovery Manager includes the following features:
The Recovery Manager command language interpreter (RMCLI) is an external utility similar to SQL*DBA. The RMCLI interprets its own specialized command language that operates in interactive command line mode and in batch file command mode. All RMCLI commands are available in both modes.
Oracle8 supports two basic types of datafile backups: backup sets and image copies.
Backup sets can contain multiple input files of the same type (datafiles or archivelogs). A control file backup may be included in a datafile backup set. A restore operation is required to extract files from the backup set.
The backup set can span multiple operating system files. Backup sets may be written either to disk or to tertiary storage. Oracle8 supports both full and incremental backups.
Image copies contain only a single input file (datafile, archivelog, control file), which can be used "as is" to perform recovery; no restore operation is required. An image copy may be written only to disk.
The Oracle8 Recovery Manager provides commands for performing the following types of functions:
For more information, see the Oracle8 Server Backup and Recovery Guide.
Oracle8 backup and restore enhancements provide an integrated method for creating, managing, and restoring backups of a database. Oracle8 supports both full backups and incremental backups. Backup files can be stored on tape or disk.
Several types of parallelism provide high performance for backup and restore operations. The server detects and prohibits operations that would result in unusable backup files or would corrupt restored datafiles.
Prior versions of Oracle required the use of an operating system utility to copy database files and restore them. The DBA had to manage these backup copies manually. To back up a database that is in operation (online backup), the DBA also had to coordinate the BEGIN BACKUP and END BACKUP commands with the operating system's file copy operation. Thus, restoring from backups and then doing media recovery was a complicated and possibly error-prone process in prior releases of Oracle.
For more information, see the Oracle8 Server Backup and Recovery Guide.
The server adds records to the control file whenever a backup is performed. The Recovery Manager then automatically propagates these records into the RCV catalog (and resynchronizes the catalog).
The programmatic interface to the backup/restore feature is encapsulated in a PL/SQL package that includes a number of procedures for requesting various backup and restore services. The package is used "conversationally,", that is, a backup or restore operation consists of multiple procedure calls. The procedure calls are grouped into the following categories:
The dictionary views NLS_DATABASE_PARAMETERS, V$NLS_PARAMETERS, and V_$NLS_PARAMETERS contain two rows describing the character set choices made in the CREATE DATABASE statement. The row with NAME='NLS_CHARACTERSET' has the database character set's name in VALUE$. The row with NAME='NLS_NCHAR_CHARACTERSET' has the national character set's name in the column VALUE$.
These values should always be the same as the values in the column PROPS$.
Various views contain a new column, CHARACTER_SET_NAME, of value:
DECODE(x$.CHARSETFORM,
1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(x$.CHARSETID),
4, 'ARG:'||x$.CHARSETID)
where x$ represents one of the base tables. The DATA_TYPE or COLTYPE column value of the view will not change to indicate the character set choice.
To protect the dictionary from outside attacks, the Oracle8 SYS schema is inaccessible to users with ANY system privileges, such as SELECT ANY TABLE, UPDATE ANY TABLE, and INSERT ANY TABLE.
Note: The term ANY denotes the set of privileges that allow the specified operation on any of the objects in the database, for example, UPDATE ANY TABLE, SELECT ANY TABLE, CREATE ANY INDEX, ALTER ANY TABLE.
The password for the user SYS is not included in the Oracle8 dictionary, and the connections to user SYS are secured by allowing connections to SYS as SYSDBA only with the following restrictions:
Thus, Oracle8 connection to SYS is made exactly identical to Oracle7 connection to INTERNAL. (In Oracle8 CONNECT INTERNAL is available only for backward compatibility and should not be used otherwise.)
This increase in security is effected by restricting Oracle7 functionality as follows:
Note: Set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to TRUE to continue Oracle7 behavior.
In client/server environments, requests are executed immediately, a situation often called online or connected execution of work. In some environments, however, a deferred or disconnected execution is preferred or even required-for example, data entry to be executed at a later time after specific conditions are met.
Oracle8 advanced queueing can defer execution of work. After a request for work is entered, advanced queueing defers processing of that request until the requestor completes the task or process or transaction that created the request.
Oracle8 persistent queueing (an integration of transaction processing with queueing technology) can ensure that a request is processed exactly once, even in the presence of application failure or system failure.
Oracle8 advanced queueing provides an efficient queueing infrastructure through a PL/SQL interface that does not depend on the use of TP monitors or any other message-oriented middleware (MOM) infrastructure such as IBM's MQ Series.
Oracle8 advanced queueing provides the following:
Integration of advanced queueing into the Oracle8 server gives queue administrators access to the data manipulation functionality and recovery capability of the underlying database technology. Oracle tools are available automatically to users of advanced queueing as a result of this integration.
Oracle8 advanced queueing supports the following categories of operations:
Oracle8 advanced queueing supports the following APIs:
The Tuxedo TP monitor provides an API to deal with the communication between application servers. This interface is called Application-Transaction Monitor Interface (ATMI).
The Oracle8 advanced queueing PL/SQL interface supports persistent, deferred, and transactional communication between transactions. Also, the Oracle8 advanced queueing library provides translation routines.
Configuration information can be managed through package procedures in the DBMS_AQADM package. Incorrect use of the administration interface can have substantial performance impact on the database system; therefore the administration interface should be treated as privileged. Only designated queue managers or privileged users should be granted access to the administration package. Initially, only SYS has access privilege to the procedures in DBMS_AQADM. A number of new DBMS_AQADM.xxxx procedures grant and revoke rights to manage or to use advanced queuing.
New DBA and user views have been created for Oracle8 advanced queuing. For more information, see the Oracle8 Server Reference Manual.
Oracle8 incorporates a wide range of data storage enhancements. The following feature list indicates the extensive and customer-oriented accomplishments in data storage technology embodied in Oracle8:
Enterprise Manager has a new interface used to describe which instances are available to serve which applications (instance groups).
A new view, V$SESSION_FAILOVER, has the following fields:
The OCI library's user must be able to perform actions in the event of failover. To assist, the failover application warns the client about such things as:
Oracle8 incorporates additional functionality to conform to selected parts of standards for internationalization features used among telecommunications providers. The Oracle8 server:
For general information on Oracle8 datatypes, refer to Oracle8 Server Concepts. For specific information on support for national character datatypes, see Oracle8 Server SQL Reference, Oracle8 Server Reference Manual, and PL/SQL User's Guide and Reference.
The Oracle8 Open Type System (OTS) specifies the datatypes that are supported in the Oracle8 Object/Relational DBMS (ORDBMS). The OTS is an emerging superset of the Oracle SQL type system.
The Oracle ORDBMS provides facilities for enhancing current and developing future database applications that employ object-oriented techniques. For many database application areas (for example, CASE), object-oriented techniques have a major advantage over relational techniques in ease of modeling and managing complex objects by providing application developers with a more powerful and flexible object modeling facility, including encapsulation and inheritance. A fundamental characteristic of object-oriented modeling is to group objects that have the same behavior into types.
The ORDBMS allows applications implemented in many different third-generation languages (e.g, SQL, OPLS, C, C++, Visual BASIC, Object COBOL, Smalltalk) to share objects. In addition, the ORDBMS interoperates with other object systems (for example, CORBA, ODMG) whose object models have commonalities as well as differences. The Oracle8 ORDBMS object model describes types of objects (structures and operations) and semantics of operations invocation and execution.
The Oracle8 SQL Type Data Definition Language (Type DDL) is part of the Oracle8 Objects Expansion. It enables users to create and manage open type system (OTS) user-defined types in the Oracle8 Object/Relational DBMS (ORDBMS).
The Oracle8 SQL Type DDL is the Oracle SQL facility for defining and managing OTS user-defined types. It provides for the following definition and management capabilities:
The Oracle8 OCI has many new features that can be broadly categorized in the following areas:
All the data structures that are used by the Oracle8 OCI are encapsulated in the form of interfaces called handles. A handle is an opaque pointer to a storage area allocated by the OCI library that stores context information, connection information, error information, or bind information about a SQL or PL/SQL statement. A client allocates a certain type of handle, populates one or more of those handles through well-defined interfaces, and sends requests to the server using those handles. In turn, applications can access the specific information contained in the handle using accessor functions.
The Oracle8 OCI library manages a hierarchy of handles. Encapsulating the OCI interfaces using these handles has several benefits to the application developer:
The Oracle8 OCI provides application developers simplified user authentication and password management in two ways:
The Oracle8 OCI supports two types of login sessions:
The Oracle8 OCI has several enhancements to improve application performance and scalability. Application performance has been improved by reducing the number of client-to-server round trips required; scalability has been facilitated by reducing the amount of state information that needs to be retained on the server side. Some of these features include:
The Oracle8 OCI supports several improvements to provide a single unified interface for transaction management in a variety of configurations. Some of the major improvements are:
The Oracle8 OCI provides the most comprehensive application programming interface for programmers seeking to use the Oracle8 server's object capabilities. These features can be considered in five major categories:
The object cache is a client-side memory buffer that provides lookup and memory management support for objects. It stores and tracks object instances that have been fetched by an OCI application from the server to the client side. The object cache is created when the OCI environment is initialized. Each application running against the same server will have its own object cache. The cache tracks the objects currently in memory, maintains references to objects, manages automatic object swapping, and tracks meta-attributes or type information about objects. The cache provides the following OCI advantages:
Applications using the Oracle8 OCI can access objects in the Oracle8 server through two types of interfaces
The OCI provides a set of functions with extensions to support object manipulation using SQL SELECT, INSERT, and UPDATE statements. To access Oracle8 objects, these SQL statements use a consistent set of steps as if they were accessing relational tables. The Oracle8 OCI provides the following four sets of functions required to access objects using SQL statements:
The Oracle8 OCI also provides a set of functions using a C-style "pointer chasing" scheme to access objects once they have been fetched into the client-side cache by traversing the corresponding smart pointers or REFs. This "navigational interface" provides functions for:
The Oracle8 OCI provides for objects a run-time environment that offers a set of functions for managing how Oracle8 objects are used on the client side. These functions provide the necessary functionality for:
Oracle8 OCI provides three sets of functions to work with Oracle8 objects:
The Object Type Translator (OTT) utility translates schema information about Oracle8 object types into client-side language bindings. That is, the Oracle8 OTT translates open type system (OTS) type information into declarations of host language variables (structures and classes). The OTT takes an "intype" file that contains metadata about Oracle8 schema objects (an Oracle8 data dictionary) and generates an "outtype" file and the necessary header/implementation files that must be included in a C application running against the object schema. Both OCI applications and Pro*C precompiler applications may include code generated by the OTT. The OTT:
The OTT is typically invoked from the command line by specifying the intype file, the outtype file and the specific database connection. With Oracle8, the OTT can generate only C structs, which can be used with either OCI programs or Pro*C precompiler programs.
The enhancements to the new OCI provide several benefits:
Each of these benefits is described in the following subsections.
The OCI provides the most comprehensive support for Oracle8 objects of all the programmatic interfaces. It provides the most highly tunable interface to access, modify, and manipulate Oracle8 object types on the client side. Further, the many tools and features of the OCI significantly enhance developer productivity when creating applications that use Oracle8 objects.
The Oracle8 OCI facilitates improved application performance by reducing the number of client-to-server round trips in three ways:
Applications written to use Oracle8 OCI will have greater scalability due to the OCI's reduced use of server-side memory, its ability to pool concurrent transactions, and its improved support for multithreaded environments.
All data structures used by the Oracle8 OCI are encapsulated in the form of opaque interfaces called handles. This encapsulation of the OCI's interfaces means that changes can be made to the underlying data structures without affecting applications. For example, some services currently provided by the database and externalized through the OCI's APIs could in the future be provided by an application server. By using the OCI's opaque handles, applications will not need to change significantly if accessing these services from the application server; this facilitates application extensibility.
Applications written to work with the Oracle7 OCI have a very smooth migration path to Oracle8 OCI due to the interoperability of the Oracle7 OCI (Oracle7 client) with Oracle8 (server) and Oracle8 OCI (Oracle8 client) with Oracle7 (server). The following three alternatives are available for migrating an existing Oracle7 OCI application:
If you need to use any of the object capabilities of the Oracle8 server, you must upgrade the client(s) also to use Oracle8 OCI.
For more information about features and functionality of the Oracle8 OCI, see the Programmer's Guide to the Oracle Call Interface, Volume I: OCI Concepts.
Oracle8 provides an enhanced Pro*C/C++ for better bulk handling of user data. Oracle8 Pro*C/C++, like Oracle7 Pro*C/C++, supports the use of simple aggregate datatypes for inserting data into, and fetching data from, the database. Moreover Oracle8 Pro*C/C++ extends and simplifies this capability to make programming more intuitive and to give users greater flexibility in organizing data:
Pro*C/C++ is enhanced to provide support for objects and open type system (OTS) types. Supported OTS types include:
With Oracle8, Pro*C/C++ users can declare and use C host variables for the following OTS types:
The specific Pro*C/C++ enhancements that support OTS types can be classified into the following categories:
The Pro*C/C++ front end (parser, semantic analyzer, etc.) and the SQLLIB run-time library have been enhanced to support the new OTS types and to permit object access and update using SQL statements.
An additional Pro*C/C++ input file, the TYPEFILE file, gives the precompiler necessary information about OTS types.
New EXEC SQL statements have been added to support object types, LOB types, and other features.
New user-callable SQLLIB functions are provided to facilitate interoperation of embedded SQL and explicit object-enhanced OCI calls.
Pro*C/C++ can allow binding of C structs to PL/SQL records. A user-defined C structure can be passed into a PL/SQL block embedded within Pro*C/C++ and be bound to a PL/SQL record therein.
Oracle8 Pro*C/C++ support for objects and OTS types enables Pro*C/C++ users to interact with database objects that have new OTS types. Pro*C/C++ programs can declare and use host variables that have C types corresponding to the OTS types (following an Oracle8 defined OTS-to-C mapping). SQL statements can be used in a Pro*C/C++ program to manipulate objects of the new types, thus providing a "higher level" interface than the OCI layer.
New Pro*C/C++ "wrapper" syntax can provide high-level Pro*C/C++ "wrappers" to some lower-level (UPI/OCI/ORT) functions for the new OTS types. For example, Pro*C/C++ syntax can handle allocation and freeing of LOB locators and freeing of objects in the object cache by way of EXEC SQL statements. These statements do not require UPI arguments like hstdef to be mentioned explicitly as a parameter.
The new Oracle8 SQL*Module for Ada 8.0 is now the programmatic interface of choice for Oracle's Ada customers. Several features have been added to the SQL*Module to make the Oracle8 Ada product on par with Pro*Ada.
Oracle8 SQL*Module for Ada 8.0 provides the following capabilities:
These expanded capabilities are provided through an expanded command set. The following new command statements support the enhancements:
Oracle8 adds several features to the Pro*COBOL precompiler. Oracle8 Pro*COBOL 8.0:
Oracle7 snapshot-related DDL statements are fully compatible with Oracle8. By default Oracle8 uses a master's primary key. For various DDLs, a WITH clause is added to allow users to explicitly create ROWID snapshot or primary key snapshots:
Oracle8 users can use PL/SQL or SQL to call external procedures stored in a dynamic link library as if they were PL/SQL procedures and functions. The external procedure label in this context designates external C routines (not C++).
Thus 3GL and C functions can be inserted into a dynamic link library and registered using PL/SQL. At run time, PL/SQL or SQL dynamically loads the library and calls the external procedures.
Oracle8 collections comprise object type attributes, PL/SQL variables, parameters, and results. PL/SQL supports collections in Oracle8 through nested tables and varrays (variable length arrays). Nested tables encompass index-by tables, which were supported in Version 2 of PL/SQL.
Oracle8 PL/SQL programs can access or update the elements of such collections using array-style subscripting notation.
PL/SQL User's Guide and Reference describes the syntax and semantics of the new object features of PL/SQL 8.0 and contains detailed functional specifications to be used by PL/SQL implementors, maintainers, and testers. It also provides rationale for the new features and examples of their usage and can be used as a base document for writing PL/SQL 8.0 technical references and other support documentation.
Oracle8 handles large amounts of unstructured data by providing two new large object (LOB) datatypes in addition to the usual datatypes in relational database engines. (The term LOB commonly refers to the following datatypes):
Important LOB features to note are:
For detailed information, see Oracle8 Server Application Developer's Guide.
This section describes server scalabilty improvements in Oracle8.The following tables summarize some of the key features, the benefits the application designer can realize and the changes required to realize the benefit.
The following table summarizes some of the benefits of the changes in the server process for Oracle8.
The following table summarizes the enhancements in Oracle8 that aenable applications to better utilize available memory
The table below summarizes the key improvements in Oracle8 addressing the limitations in network capacity utilization.
The list of enhancements done to improve CPU usage is long and continues to grow. It is divided into two classes-those that affect SQL applications and those that affect PL/SQL and SQL applications.
The following table summarizes some of the work done to reduce bottlenecks and CPU consumption in Oracle8 for execution of SQL.
S
The following table summarizes the work done in Oracle8 to reduce CPU consumption for execution of PL/SQL, SQL from PL/SQL and PL/SQL from SQL
|
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
|