|Getting to Know Oracle8i
This chapter describes new features, options, and enhancements available in Oracle8i.
The following topics are included.
The Web and the Internet bring many new challenges to organizations. Today's Web sites are often a series of Web pages that take too much time and too many human resources to develop, deploy, and manage. In addition, their infrastructure may not be stable or robust enough to support mission-critical applications. Everything necessary to transform data into powerful Internet applications and content-driven Web sites is included with Oracle8i. It provides a Web development environment that allows software developers to easily build dynamic, data-driven Web sites with a standard Web browser and the Oracle8i database.
WebDB is an HTML-based development tool for building HTML Web pages with content based on data stored in the Oracle database. Using a browser-based interface, Web pages can be created containing reports, charts, calendars, menus, and forms. WebDB provides build wizards for step by step guidance through the process of creating these components. The wizards include steps for building the PL/SQL statement that selects data used in the components, and steps for setting look and feel attributes of the component such as color and size.
There is no need to know PL/SQL to create a component. Selecting options in the build wizard guides WebDB to automatically write the PL/SQL code for building the component.
WebDB also provides a wizard to create a Web site within the Oracle database and assign a site administrator to control its development of the site. The Web site administrator in turn assigns owners who are responsible for providing content to the site. Content can include Web pages containing WebDB components, links to other URLs, and files uploaded to the Web site.
Once a site is created, it is maintained using options within the site itself. Content owners and end users never have to see the WebDB user interface. For example, the look and feel of the site can be updated using options provided within the site. End users perform searches of the site's content using options within the site.
For more information, see Oracle WebDB Release 2.0: Getting Started - Installation and Tutorial.
Oracle iFS combines the power of Oracle8i with the ease of use of a file system. Completely integrated with Oracle8i, the Internet File System is a Java application that runs within the Oracle8i Java Virtual Machine. It enables the database to become and Internet development and deployment platform.
Oracle iFS makes it possible to treat the database as if it were simply a shared network drive. Users can store and retrieve files managed by the database as if they were files managed by a file server. Because iFS supports a variety of standard protocols, users have universal access to their data. Whether a user accesses the contents of iFS through Windows Explorer, a Web browser, an FTP client, or an e-mail client, the files appear the same.
Not only can relational data appear as files, but hybrid documents that combine relational and non-relational data into single objects can be created and viewed. Files and folders in iFs are stored in a repository. They are indexed on content and file properties, allowing for intelligent text searches and queries.
From a developer's standpoint, iFS is the single data store containing the data for many different applications. For the system administrator, iFS provides a single system for file storage and messaging, rather than several separate systems to maintain and administer. In short, iFS is a simpler way of storing different types of files in the database.
Web applications require advanced data management services that support the rich datatypes used in Web repository, e-commerce, and other Internet applications. Oracle8i interMedia adds support that enables the database server to manage multimedia content, both for Internet and traditional applications that require access to image, audio, video, text, and location information. Oracle8i interMedia includes the following:
Integral to interMedia is the interMedia Clipboard and Web Agent. It provides a clipboard and Web agent working together to Web-enable interMedia. The clipboard can be used to:
The Oracle8i interMedia Web agent decodes URLs to retrieve multimedia objects for display by Web clients, such as Web browsers. The Oracle8i interMedia Web Agent supports Netscape Enterprise Server, Netscape FastTrack Server, Microsoft Internet Information Server, and Oracle Application Server.
For release 8.1.5, the Clipboard and Web Agent Components of Oracle8i interMedia are not available on the Oracle8i distribution media. Instead, you can download these components from the "Free Software" download area of the Oracle Corporation Web site:
The documentation, which includes readme files and the book Using Oracle8i interMedia with the Web, is included in the download.
For a more detailed summary of interMedia, and references to specific documentation, see below.
Oracle8i allows you to integrate all your data into its datastore. The Oracle8i extensibility framework and object relational technology have been exploited to extend the reliability, availability, and data management capabilities of the database server to multimedia data. A new multimedia option, Oracle8i interMedia, services text, document, image, audio, video, and locational data in a single integrated package. The Spatial, Time Series, and Visual Image Retrieval options, available separately, also have been designed to utilize the Oracle8i extensibility architecture.
For each of the media types, customers and partners can easily create "plug-ins" to support additional formats, new digital compression and decompression schemes (codecs), specialized indexes, custom query optimization and methods, external media data sources and even specialized data processing algorithms.
The following is a summary of functionality available with each option.
Oracle8i interMedia allows multimedia data to be managed in an integral fashion with other enterprise data. Applications can access interMedia through both object and relational interfaces. Database applications written in Java, C++, or traditional 3GLs can interface to interMedia through class libraries, PL/SQL, and the Oracle Call Interface (OCI).
For a more general description of interMedia, refer to "interMedia and the Web". This section presents specific features of the interMedia components and points you to supporting documentation.
Oracle8i interMedia Text delivers powerful text management and retrieval capabilities. It indexes any documents or textual content to deliver fast, accurate retrieval of information from document archives, online product catalogs, news services, media asset management systems, job postings, customer call reports, and other text information sources in multiple languages. Several techniques are used to ensure fast and accurate text searching, from traditional full-text search to document theme analysis.
The base for interMedia Text is Oracle's ConText Cartridge. Several new features have been added, in addition to its integration into interMedia. Discussions of these new features follow.
This is made possible through the tight integration of interMedia Text with SQL, PL/SQL, Oracle Enterprise Manager, SQL*Loader, and other components of the Oracle8i database engine. Some examples:
Creating an index is now much simpler because it is created and maintained using standard SQL. This is made possible through the Oracle8i extensible indexing framework as described in "User-Defined Operators and Extensible Indexing".
Through new functionality provided by the Oracle8i extensible optimizer, described in "Extensible Optimizer", the optimizer can be used to select the best plan for executing a CONTAINS query. It does so by analyzing collected statistics of all the tables and indexes affected by a CONTAINS query.
Application developers and users can import a thesaurus to augment the built-in KB. User-specific concepts, categories, words, or phrases can be added to improve theme capabilities. Information in the thesaurus takes precedence over information in the built-in KB when there is a conflict.
By default during indexing, interMedia Text uses the Inso Corporation filtering technology, which can filter most document formats. This filtering technology automatically recognizes document formats. Thus, this filter can be used to index single or mixed column formats. A custom or other filter may also be specified to filter documents during indexing.
For document presentation, the Inso filtering technology is used to create plain text or HTML versions of formatted documents.
Given a query expression, interMedia Text returns related query term information (broader term, narrower term, related term) that can help refine queries.
The behavior of theme highlighting procedures has been changed. For theme queries, interMedia Text procedures highlight and mark up words or phrases that best represent the theme query. This behavior is different from ConText, where paragraphs are highlighted for theme queries.
Certain languages use alternate spellings or representations for characters that cannot be stored as single characters: for instance, the letter "ö" in German is alternately represented as "oe". interMedia Text now checks for these alternate spellings during indexing and queries.
ConText allows stoplists to specify words that are not to be indexed (stopwords). The following additional stoplists are introduced in interMedia Text.
Also, stoplists can now be changed dynamically, after an index has already been created.
A new section type (field section), allows text within the field section to be indexed as a subdocument separate from the rest of the document.
Oracle8i interMedia provides foundational support for audio, image, and video digital information. Services provided by interMedia facilitate the integral storage of information in these forms into the Oracle8i datastore as BLOBs, or as references to external BFILEs. These interMedia services also include methods for retrieval of this multimedia digital information, inserting new information, and for limited manipulation.
The following table summarizes the support for audio, image, and video data provided by Oracle8i interMedia.
|Media Type||Description of Support|
(Note that Oracle's Image Cartridge has been subsumed by Oracle8i interMedia.)
For more information, see the Oracle8i interMedia Audio, Image, and Video User's Guide and Reference.
Where a greater degree of manipulation or modification of image, audio, or video data is required, Oracle provides the Oracle8i interMedia Audio, Image, and Video Java Client.
The Oracle8i interMedia Audio, Image, and Video Java Client allows the user to use local (client side) applications to manipulate or modify multimedia data stored in a network-accessible (server side) database. It allows the user to connect to a server side multimedia object, copy that object from the server side to the client side, perform various operations on the client side object, and transfer the new multimedia object back to the server side.
For situations where the user does not have permission to modify the server side object, Oracle8i interMedia Audio, Image, and Video Java Client can retrieve the multimedia data from the server side for display purposes only.
In addition, Oracle8i interMedia Audio, Image, and Video Java Client gives the user the ability to integrate multimedia objects with various media frameworks, such as the Java Media Framework (JMF), RealPlayer, or Oracle Video Client (OVC).
For more information, see Oracle8i interMedia Audio, Image, and Video Java Client User's Guide and Reference.
Oracle8i interMedia Locator is the component of Oracle8i interMedia that supports online geocoding facilities for locator applications and proximity queries. It supports geocoding of spatial point data of interest, stores this geocoded data locally in the Oracle8i database server, and performs within-distance queries on the geocoded data.
Geocoding represents addresses and locations of interest (postal codes, demographic regions, etc.) as geometric features (points). Geocoding services can be used to add the exact location (latitude and longitude) associated with points of interest to existing data files stored in Oracle8i. Once data is geocoded, Oracle8i interMedia Locator enables distances to be calculated and sites to be represented graphically in Web, data warehousing, customer information system, and enterprise resource planning applications.
A sample application might use interMedia Locator to locate restaurants and hotels within a given point-to-point distance of a specified location, such as a business or tourist information kiosk.
Locator is not designed to be an end-user application in itself. It consists of an interMedia locator object datatype, a geocode result object datatype, geocoding functions, a spatial locator index, and a spatial operator for performing within-distance queries. It supports only point geometry types. Web application developers can use the basic function provided here to build specialized Web-enabled interMedia Locator applications. Oracle8i interMedia Locator also supports server-based geocoding and data scrubbing operations for data warehouse applications.
For more information, see Oracle8i interMedia Locator User's Guide and Reference.
Spatial data is any data with a location component. The location component could be the geocoded addresses of customers or suppliers, the course of a river and the outline of its floodplain, the locations of thousands of utility poles, or X and Y coordinates on a blueprint. Spatial data is not limited to the land surface but includes the subsurface, aquatic, marine, and lower atmospheric regions.
Oracle8i Spatial is designed for two groups of users:
This section describes the enhancements to Oracle8i Spatial, formerly known as the Oracle8 Spatial Cartridge, for the current release. Additionally, this release completes the implementation of the OpenGIS Consortium RFP1 - Simple Features guidelines. Oracle8i Spatial conforms to the specifications associated with SQL92 representation of points, lines, and polygons.
For more information about Oracle8i Spatial, see Oracle8i Spatial User's Guide and Reference.
The structure of the spatial data model has changed such that ordinates are stored using the VARRAY storage model. A spatial object type can be created that represents single element or multi-element geometries. Where in release 8.0, the cartridge had individual columns for geometry attributes, Oracle8i combines these into a single column of type SDO_GEOMETRY, similar to the way Oracle8i interMedia defines specialized objects for images, text, audio, and video.
For dynamic window queries, the release 8.0 cartridge required that users create and maintain a window layer that stored the definition of an area-of-interest and its corresponding spatial index entries. In Oracle8i, the spatial operators take a bind variable of type SDO_GEOMETRY, and the index creation and usage are handled internally.
The spatial index mechanism now uses Oracle8i extensible indexing features, including support for maintaining the index on insert, update, and delete. As a result, it is no longer necessary to explicitly call SDO_ADMIN.POPULATE_INDEX() or SDO_ADMIN.UPDATE_INDEX().
The RELATE() operator has been enhanced such that it can perform both primary and secondary filter operations. At query time, the Oracle8i extensible optimizer then determines which process to use based upon information in the query plan. The new syntax for the RELATE() operator greatly simplifies queries.
Two new spatial operators have been added.
Circle and circular arc geometric datatypes have been added, and several new spatial functions further extend the capabilities of this product.
Oracle8i Visual Information Retrieval provides content-based retrieval for images stored in Oracle8i. Content-based retrieval--where the query takes the form "find me objects that look like this one"--reduces the time and effort required to obtain image-based information.
Users can tap into image content with self-service applications using flexible query criteria. They can locate similar images in large databases by searching visual criteria, such as color, pattern, and texture. Examples of database applications where content-based retrieval is useful include:
Oracle8i Visual Information Retrieval includes foundational datatype support for images in Oracle8i, that complements and is completely compatible with Oracle8i interMedia. Oracle8i Visual Information Retrieval is based on the VIRTM technology from Virage, Inc., a leader in content-based retrieval software.
This section describes the enhancements to the Oracle8 Visual Information Retrieval Cartridge, now repackaged as Oracle8i Visual Information Retrieval. Also available with this release is the Oracle8i Visual Information Retrieval Java Client, similar in function to that described in "Oracle8i interMedia Audio, Image, and Video Java Client".
Oracle8i Visual Information Retrieval allows users to create an index to support image objects. Oracle8i and Visual Information Retrieval cooperate to define, build, and maintain an index for image data. Once created, the index automatically updates every time an image is inserted or removed from the database table. This feature significantly speeds up query time and improves performance.
Consider the case where a thin client requests a scaled-down image from the database. Rather than storing the scaled-down image in a table, it is now possible to use temporary LOBs, described in "Temporary LOBs". Methods are provided which allocate and operate on temporary LOBs. Additional performance benefits are achieved by utilizing OCI LOB buffering routines provided in Oracle8i, instead of local buffering mechanisms.
Scaling is now allowed to absolute dimensions. The process() method in release 8.0 only allowed scaling by a floating point number. The new scaling mechanism allows, for example, a much easier method to create a column of thumbnails for a table with images of various sizes in it.
Because it is not practical to provide native support for all of the image formats in use today, Visual Information Retrieval provides a canonical raw image format gateway to ease the import of proprietary image data. This benefits the many image processing programs that can directly support input and output of uncompressed image data.
Oracle8i Time Series enables time-stamped data to be stored efficiently in an Oracle database. It supports a basic set of functions--calendar, time series, and time scaling--to retrieve and process data. Its collection-based interface enables product developers and third-party providers to add functions to the base cartridge. By offering basic functions and datatypes, it allows customers and independent software vendors (ISVs) to develop data provisioning, data warehousing, and full-analysis time series applications using the power of Oracle8i.
Oracle8i Time Series provides the following kinds of functions:
A summary of the new features of Oracle8i Time Series, formerly called the Oracle8 Time Series Cartridge, is presented below.
For more information on Oracle8i Time Series, see the Oracle8i Time Series User's Guide.
Support is provided for irregular time series, that is, time series without an associated calendar. Irregular time series provide a means to conveniently handle high volumes of unpredictable data, as well as many applications with predictable data where timestamp validation is not needed.
Added are week, 10-day, semimonthly, quarter, and semiannual calendar frequencies.
Flexible timestamp precisions in Oracle8i Time Series relax the precision requirements of the previous release. For example, a monthly calendar now can be defined where all timestamps are on almost any specific day of the month (such as the 15th), whereas before they had to be on the first of the month.
The calendar pattern has been extended to provide flexible support of a repeated pattern of included and omitted timestamps. The pattern specification extensions allow for more semantic (behavioral) options with scaleup and scaledown functions.
Time scaling enhancements in Oracle8i include the implementation of scaledown functions and additional overloadings of scaling functions to provide an alternative to SQL null semantics.
Previously, the Time Series Cartridge supported a storage model for time series data based on a flat index-organized table (IOT). Oracle8i Time Series provides alternative support for a nested IOT model, enabling time series data to be stored in a nested IOT. Either method has advantages and disadvantages that must be weighed by the user to select the method providing the better performance and scalability.
Administrative tools and procedures are provided to simplify the creation and use of time series schema objects. For most users, these procedures are a convenient alternative to explicitly defining the tables, views, and triggers used by Oracle8i Time Series.
Java is becoming the standard language of the Internet. In Oracle8i, Oracle delivers an enterprise-class Java platform to develop and deploy Internet applications. Additionally, Oracle is making a significant strategic commitment to Java and is integrating it in a large part of its product offering.
Oracle8i's Java offering includes:
Each of these is summarized separately below.
For more information about Oracle's Java support, refer to the following books.
Oracle's Java VM is a complete JDK 1.1.6-compliant Java execution environment. The Java VM runs in the same process space and address space as the database server, sharing its memory heaps and directly accessing its relational data. This design optimizes memory use, increases throughput, and delivers an open, highly available, secure, and manageable Java server.
The Java VM provides a runtime environment for Java objects. It fully supports Java data structures, method dispatch, exception handling, language-level threads, and all the core Java class libraries. It embeds the standard Java namespace in database server schemas allowing Java programs access to Java objects stored in Oracle databases and application servers across the enterprise.
Components of the Java VM include a library manager, bytecode compiler, interpreter, class loader, verifier, native compiler (separate option), server-side JDBC driver, and server-side SQLJ translator. Only the native compiler (JServer Accelerator), JDBC driver, and SQLJ translator components are discussed in this document.
A complete description of Oracle's Java Virtual Machine can be found in Oracle8i Java Developer's Guide .
JServer Accelerator is a native code compiler that speeds up the execution of Java code by eliminating interpreter overhead. It translates standard Java binaries into C programs that are processed by a platform-dependent C compiler into native libraries that the Oracle Java VM can load dynamically. Unlike just-in-time (JIT) compilers, JServer Accelerator is portable to all OS and hardware platforms. To speed up applications, the Java VM is supplied with natively compiled versions of the core Java class libraries, ORB, and JDBC.
Oracle offers two programmatic interfaces (Application Programmer Interfaces, or APIs) for Java Developers: JDBC (Java Database Connectivity) and SQLJ (embedded SQL in Java).
JDBC is a standard Java interface for connecting to relational databases from Java. The JDBC standard was defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers. JDBC is based on the X/Open SQL Call Level Interface, and complies with the SQL92 Entry Level standard. In addition to the standard JDBC API, Oracle drivers have extensions to properties, types, and performance.
Oracle offers three versions of its JDBC driver.
Using low-level entry points, a specially tuned JDBC driver runs directly inside the database server. As a result, it provides the fastest access to Oracle data from Java stored procedures. The Server JDBC Driver complies fully with the Sun JDBC specification. Tightly integrated with the database server, it supports Oracle-specific datatypes, NLS character sets, and stored procedures. Also, the client-side and server-side JDBC APIs are the same, allowing for flexibility in where the application is deployed.
This driver is meant for developers of client/server Java applications and Java-based middle tiers. It converts JDBC invocations into OCI calls, which are sent via Net8 to the Oracle database server.
The OCI JDBC Driver supports various configurations including stateful, multi-tier configurations in which browser-based applets communicate with middle-tier servlets. However, it is not downloadable and requires the installation of OCI and Net8 libraries on each client machine or on the middle-tier Java application server.
This driver is meant for developers of Java applications and applets. It establishes a direct connection to Oracle over Java sockets and implements a lightweight TCP/IP version of Net8 protocol. So, unlike the OCI JDBC Driver, it works only with TCP/IP-based networks.
Written in Java, the Thin JDBC Driver is about 150 K (300 K uncompressed), so it can be downloaded with Java applets into a Web browser or Network Computer. No preinstallation of software on the client is required.
SQLJ enables SQL statements to be embedded in Java programs. SQLJ code is much more concise and easier to write than JDBC and features static analysis and type checking.
The SQLJ translator, itself a Java program, takes as input a file of Java source code in which SQLJ clauses are embedded. Then, it translates the SQLJ clauses into Java class definitions that implement the specified SQL statements. The Java type system ensures that objects of those classes are called with the correct arguments. Oracle provides both a server-side and client-side SQLJ translator.
The server-side SQLJ translator is a highly optimized SQLJ translator that runs directly inside the database server, where it provides runtime access to Oracle data via the Server JDBC Driver. SQLJ forms can include queries, DML, DDL, transaction control statements, and calls to stored procedures. The client-side and server-side SQLJ APIs are the same, allowing for flexibility in where the application is deployed.
Java VM supports a variety of development models, including:
These are discussed in more detail in the following sections,
Stored procedures are Java procedures or methods published to SQL and stored in an Oracle database for general use. With stored procedures, you can implement business logic at the server level, thereby improving application performance, scalability, and security.
Except for GUI (Graphical User Interface) methods, any Java method can run in the database server as a stored procedure. The runtime contexts are:
To publish Java methods, call specifications (call specs for short) are written, which map Java method names, parameter types, and return types to their SQL counterparts. A call spec is not a wrapper. Without adding a layer of execution, a call spec simply publishes the existence of a Java method. So, when the method is called (through its call spec), the runtime system dispatches the call with minimal overhead. And, because the stored procedure is executing in the database, there is efficient access to SQL data.
Anything that can be written with PL/SQL can now be written with Java and run in the database server in the same contexts as PL/SQL. The database server allows a high degree of interoperability between Java and PL/SQL. Java applications can call PL/SQL stored procedures using an embedded JDBC driver. Conversely, PL/SQL applications can call Java stored procedures directly.
Enterprise JavaBeans (EJB) is a component model introduced by JavaSoft that enables developers to create custom component applications. These applications consist of server-side beans developed by yourself or by third parties. The beans provide the business logic in EJB applications.
The EJB model is flexible in that you can use different components from different vendors. For example, you can use configuration and management software from one company, bean containers from a second company, and business logic beans from a third company that specializes in providing beans for just that purpose.
CORBA is a standards-based distributed component model proposed by the Object Management Group (OMG). It supports a development environment for building, deploying, and managing distributed object applications that are interoperable across platforms.
CORBA objects communicate using OMGs Internet Inter-ORB Protocol (IIOP), the standard for communication between and among distributed objects running on the Internet, intranets, and in enterprise computing environments. Oracle8i integrates a Java-based CORBA 2.0 compliant Object Request Broker (ORB) that provides users with the ability to call into and out of the database server using CORBA's IIOP. CORBA components written in different languages, running on different platforms, can transparently communicate and interoperate.
Oracle provides utilities and tools for the development and deployment of Java applications. Some of these are discussed here.
JPublisher is a utility, written entirely in Java, that translates user-defined types to Java wrapper classes. It is similar to the Object Type Translator (OTT) utility used in C/C++ environments. Developers are required to have Java classes that correspond to user-defined types for developing:
JPublisher helps create Java language applications that use user-defined types in the Oracle server. For a Java-language application to access object data, it is necessary to represent the data in a host language format. JPublisher helps do this by creating the mapping between object types and Java classes, and between object attribute types and their corresponding Java types.
Using JPublisher to automatically generate Java wrapper classes for user-defined types is less time consuming and error prone than manually coding them.
With the release of Oracle8i, Oracle offers a new version of the JDeveloper product. JDeveloper 2.0 provides complete support for building and deploying Java applications on Oracle8i. It provides a standard GUI based Java development environment that is well integrated with Oracle's Application Server and Database.
The following are new features included in JDeveloper 2.0:
JDeveloper runs on Windows NT. General availability will follow the production Oracle8i release. For more information on JDeveloper 2.0, refer to the product specific documentation.
Provided with Java VM, the command-line utilities loadjava and dropjava manage Java library units. Java stored procedures can be written, compiled, and partially tested and debugged on the client side in JDeveloper, or other popular Java IDE. Then, the command-line utility loadjava can be used to upload the resulting Java sources, binaries, and resources into the database server. The companion utility dropjava can be used to drop given Java sources, binaries, and resources from a schema.
Oracle8i offers many new features and enhancements for the data warehousing and VLDB environment. The performance of queries is dramatically increased by materialized views and summary management, which allow frequently requested summaries to be stored and maintained. Parallelism is improved with more operations offered in parallel. The new database resource management facility, discussed in "Database Resource Management" , provides an effective way of managing CPU usage.
Moving data from one database to another is made simpler and faster with transportable tablespaces. New data mining and relational online analytical processing (ROLAP) functions are introduced. If you are using the partitioning option, the "Partitioning Enhancements" section discusses two new methods of partitioning, providing more partitioning options for the data warehouse environment.
Only a few of the many Oracle8i changes relating to data warehousing and the VLDB environment have been mentioned above. Read on for descriptions of these and allied new features and enhancements introduced in Oracle8i.
A materialized view is a stored summary containing precomputed results. Materialized views allow for significantly faster data warehouse query processing. With the Oracle8i summary management feature, the Oracle database server automatically rewrites queries to use the summary data, rather than retrieving data from detail tables by doing expensive joins and aggregate operations. This query rewrite facility is totally transparent to the application, which is not aware of the existence of the materialized view.
The DBA's first step in creating materialized views is to define dimensions. These represent the hierarchies that are present in the real world; for instance, multiple months make up a quarter, multiple districts make up a region, etc. The CREATE MATERIALIZED VIEW statement is used to create a materialized view. This statement includes a subquery, typically a join or a data aggregation (GROUP BY), the results of which comprise the materialized view.
A materialized view is maintained by a refresh process. The refresh process can be done automatically when a commit is done on a detail table, or it can be controlled manually by the DBA. A refresh is specified as complete or incremental. A complete refresh truncates existing data, then repopulates the summary with new data from the detail tables. An incremental refresh updates only changed data.
To create a materialized view in a users own schema, the user must have the CREATE SNAPSHOT or CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE INDEX, and CREATE VIEW system privileges. To create a materialized view in another user's schema, a user must have the CREATE ANY SNAPSHOT or CREATE ANY MATERIALIZED VIEW system privilege.
To enable a materialized view for query rewrite:
The schema that contains the materialized view must have sufficient quota in the target tablespace to store the materialized view's base table and index or have the UNLIMITED TABLESPACE system privilege.
To create and refresh a materialized view, both the creator and materialized view owner must be able to issue the defining query of the materialized view. This capability depends directly on the database link that the materialized view's defining query uses.
For ease in managing materialized views, Oracle provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program. These are packaged in the DBMS_OLAP package, which provides recommendations and supporting data such as the following:
This feature allows a user to move a subset of an Oracle database into another Oracle database. It is a lot like unplugging the subset from the original database and plugging it into another one. It is also possible to clone a tablespace in one database and plug it into another, thereby copying the tablespace between databases.
Moving data using the transportable tablespace feature can be orders of magnitude faster than either export/import or unload/load of the same data, because it involves only copying data files and integrating the tablespace metadata. When transporting tablespaces, index data can also moved, eliminating the need to rebuild indexes after importing or loading the table data.
In the data warehouse environment, where huge amounts of data flow from the initial OLTP databases into the enterprise data warehouse and on to data marts, this feature presents an opportunity for a faster and more innovative means of data movement. And, its use can be extended to the archiving of data or publishing data to other databases.
The direct path load API is a set of OCI interfaces that provides an application access to the direct path load engine in the Oracle server. Previously, the only client of the direct path load engine has been Oracle's SQL*Loader utility. Now, it is possible for ISVs to develop applications which can take advantage of the performance benefits provided by the direct load engine, including parallel load. There is no longer a need to write SQL*Loader control and data files in order to access the direct path load engine.
The GROUP BY clause of SQL queries can now specify additional OLAP aggregate operations. These new operations are called ROLLUP and CUBE, and are effective for computing subtotals and cross-tabulations across multiple dimensions.
While these same operations could be done using previous SQL syntax, the SQL was difficult to generate, and the execution was inefficient because the optimizer had no hints about what the user wanted to accomplish. These new operations allow client reporting and analysis tools to perform aggregate operations with less effort and greater efficiently. They are useful for creating materialized views.
The FROM clause in a query has been modified with a SAMPLE clause to allow the user to specify that the results are to be based on a random (row or block) sample of a whole table. This feature can be a useful for data mining tools, and in a data warehousing environment can be used to avoid full table scans.
Indexes can now be created on functions and expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. In a data warehouse, query performance is dramatically improved when a computationally intensive expression is used to build the index, rather than using that expression in the WHERE clause of a query.
The function used for building the index can be:
For more information, see Oracle8i Concepts.
The DESC keyword on the CREATE INDEX statement is no longer ignored. It specifies that the index should be created in descending order. Indexes on character data are created in descending order of the character values in the database character set. Neither this, nor the ASC keyword, may be specified for a domain index. DESC cannot be specified for a bitmapped index.
For more information, see Oracle8i SQL Reference.
Several changes enhance query performance when bitmap indexes are used. One of these changes lifts a restriction whereby the use of the ALTER TABLE statement could invalidate a bitmap index.
A new feature introduces two new ALTER TABLE statement options:
These options allow for tuning of the rowid-to-bitmap mapping. The MINIMIZE option can be used to optimize bitmap indexes for a query-only environment by requesting the most efficient possible mapping of bits to rowids. The NOMINIMIZE option disables this optimization.
The parallelization of aggregate distinct queries is now possible even if they do not contain a GROUP BY clause. This is done through new hints for specifying distribution methods.
For more information, see Oracle8i Tuning.
The sort routines used throughout the Oracle database server have been rewritten to eliminate performance problems. Sort now performs more predictably and consistently, given the same input, system configuration, and parameter settings. Memory is utilized more efficiently, enabling more rapid sorts when additional memory is specified. The following table shows initialization parameters that have been dropped or added as a result of this rewrite:
|Dropped Initialization Parameters||Added Initialization Parameter|
For more information, see Oracle8i Tuning.
Using the new SINGLE TABLE option when creating a hash cluster specifies the creation of a hash cluster with only one table. The benefit is faster access to the rows in the table.
To support the speed requirements of queries running in a data warehouse environment, it is critical that the optimizer selects the best plan of execution out of all those that are available for consideration. The optimizer selects the best plan based on cost, as determined by the statistics which have been gathered on the data. Therefore, it is vitally important that the optimizer has good and recent statistics on the data. Oracle8i introduces new options for the gathering and manipulation of statistics.
A new package, DBMS_STATS, is provided for:
The copying of statistics between databases is useful for tuning studies. Statistics can be copied from the production database into a test database. DBMS_STATS can also be used to back up statistics. The backup may be restored later, if necessary, or used to study the change in data characteristics over time.
Additionally, the syntax of the CREATE and ALTER INDEX statements has been changed to include a COMPUTE STATISTICS clause that allows statistics to be gathered incidentally, and at minimum cost, while the index is being built.
This enhancement simplifies tuning of parallel query (PQ) execution and provides better system utilization. PQ users can simply specify that a query or table is to be parallelized, and the system will determine the optimal single user setting, then reduce it based on the system utilization at the time the query starts execution. There are four parts to this enhancement.
For more information, see Oracle8i Parallel Server Concepts and Administration and Oracle8i Tuning.
In large databases, operations such as complex queries, index builds, and backup operations can take a long time to complete. The dynamic V$SESSIONS_LONGOPS view provides a way for users and DBAs to monitor the progress of such operations and to estimate their completion times. In Oracle8i, this view is enhanced by more internal server components collecting a greater number of statistics. Additionally, the package DBMS_APPLICATION_INFO contains a procedure SET_SESSION_LONGOPS to populate the view from an application.
The following new features are implemented through syntax changes to the ALTER TABLE and CREATE TABLE statements. For additional information on either of these enhancements, see the Oracle8i Administrator's Guide and Oracle8i SQL Reference.
It is now possible to modify the attributes of an existing constraint through the ALTER TABLE...MODIFY CONSTRAINT clause. This enhancement also introduces a new constraint property, RELY/NORELY. This flag can be used by the DBA to indicate validity of data without enabling or validating a constraint. It is used by the query rewrite function of materialized views.
Data warehouses may benefit from the DISABLE VALIDATE constraint state. This state saves space because it requires no index on a unique or primary key, yet it guarantees the validity of all existing data in the table. This is at the expense of disallowing DML and index lookups, but those may be less important considerations in some situations.
The DISABLE VALIDATE constraint state also enables efficient loading of data from a nonpartitioned table into a partitioned table using the EXCHANGE PARTITION clause of the ALTER TABLE statement.
To enable this feature, DISABLE/ENABLE and VALIDATE/NOVALIDATE have been made independent properties of constraints, where all four combinations are now allowed.
There are numerous enhancements for index-organized tables intended to make them usable for very large databases and mission-critical applications. These enhancements can be broadly classified into two areas:
Some of the key enhancements include:
For more information, see Oracle8i Concepts.
ROWIDs provide the fastest possible access to a given row in a given table. They contain the physical address of a row down to the specific block and allow retrieval of the row in a single block access. To expand this functionality to index-organized tables (IOTS), whose rows do not have permanent physical addresses, Oracle8i introduces primary key-based logical identifiers called logical ROWIDs.
Rows in index-organized tables do not have permanent physical addresses--they are stored in the index leaves and can move within the block or to a different block as a result of insertions--therefore their row identifiers cannot be based on physical addresses. Instead, Oracle provides index-organized tables with logical row identifiers, called logical ROWIDs, that are based on the table's primary key. Oracle uses these logical ROWIDs for the construction of secondary indexes on index-organized tables.
Also introduced is the UROWID datatype, which makes logical ROWIDs usable by applications in the same sense that ROWIDs are currently used (e.g., selecting ROWIDs for later update or as part of a cursor). UROWID can be used to store ROWIDs from other databases accessed via gateways, and it can be used to reference physical ROWIDs. PL/SQL fully supports the UROWID datatype.
For more information, see Oracle8i Concepts.
Other features are also useful in the data warehouse environment, but are discussed elsewhere in this document.
With Oracle8i, the Oracle database server provides a new extensibility framework, making it easier for ISVs and other developers to extend the database to meet their needs. Many object relational enhancements have been incorporated into this release, and this functionality has been extended to all Oracle8i product configurations.
Online transaction processing (OLTP) systems are usually concerned with entering and retrieving mission-critical data from day-to-day operations. Availability, reliability, and performance are extremely important for these systems. Oracle8i delivers significant enhancements in these areas, along with improvements in scalability, serviceability, and security.
Advance Queuing has been significantly enhanced, continuing to develop the publish/subscribe messaging paradigm. National Language Support now has a new book to help users better learn how to use its new and already existing functionality.
These and other new database features are discussed in this section.
The extensibility framework in Oracle8i extends the Oracle database server to support specialized datatypes and to provide core services for external use. Developers are able to provide their own implementations of database server services and register them with the server. The framework is safe and protects the integrity of Oracle8i.
ISVs and other developers can exploit this extensibility framework to more easily build user-defined software components, also referred to as data cartridges, that are easier to use, and that integrate seamlessly with each other and the Oracle database server. Multimedia users especially can benefit from the greater flexibility and fuctionality provided by this framework as it enables independent software components to interoperate in a seamless manner. For example, a query can now be based on both image and spatial data.
This section summarizes the features and enhancements that comprise the Oracle8i extensibility framework. For more information on the functionality described below, see the Oracle8i Data Cartridge Developer's Guide and Oracle8i Concepts.
Increasingly, databases are being used to store more varied types of data, such as spatial, audio, or video data. This brings a need for indexing complex datatypes and for specialized indexing techniques. Oracle8i provides an interface that enables developers to define domain-specific operators and indexing schemes and integrate them into the Oracle database server.
Oracle provides a set of built-in operators, for use in SQL statements, which include arithmetic operators (+, -, *, /), comparison operators ( =, >, <), logical operators (NOT, AND, OR), and set operators (UNION). These operators take as input one or more arguments (operands) and return a result. The extensibility framework of Oracle8i allows developers to define new operators. Their implementation is provided by the user, but the Oracle database server allows these user-defined operators to be used in SQL statements in the same manner as any of the predefined operators provided by Oracle.
The framework to develop new index types is based on the concept of cooperative indexing, where an application and the Oracle database server cooperate to build and maintain indexes for complex datatypes. The application software is responsible for defining the index structure, maintaining the index content during load and update operations, and searching the index during query processing. The index structure itself can be stored either in the Oracle database as tables or externally as files. Indexes created using these new index types are referred to as domain indexes.
For specific information about the extensions to SQL that provide this support, see Oracle8i SQL Reference.
The optimizer functionality has been extended to allow authors of user-defined functions and domain indexes to create statistics collection, selectivity, and cost functions that will be used by the optimizer in choosing a query plan.
The ANALYZE command can now make a call to a user-specified statistics collection function whenever a domain index is analyzed. User-defined statistics collection functions can also be defined for individual columns of a table and for user-defined datatypes.
Users can specify user-defined selectivity functions for predicates containing user-defined operators, standalone functions, or type methods.
Users can define costs for domain indexes and for user-defined standalone functions, package functions, and type methods. The user-defined costs can be in the form of default costs that the optimizer simply looks up, or they can be computed by functions called by the optimizer.
In addition to the above, other basic services have been packaged for use by user-defined components. These, and the other extensibility and object enhancements in Oracle8i, have allowed the uniform integration of Oracle's former data cartridge options (ConText, Image, Visual, Time Series, Spatial) into the Oracle database server.
The services include:
Additionally, the Oracle Software Packager, discussed in "Oracle Software Packager and Oracle Universal Installer", allows cartridges to be packaged and installed into Oracle8i.
The object relational paradigm introduced in Oracle8 gains new functionality in Oracle8i. Additionally, object relational technology is now available in all configurations of the Oracle database server. Enhancements target more flexible storage and retrieval of objects, and there is greater transparency between object views and object tables. Specifics of these changes are described below.
For more information on object relational enhancements, see the Oracle8i Concepts, Oracle8i Application Developer's Guide - Fundamentals, and PL/SQL User's Guide and Reference. Additional references may be provided below in specific descriptions.
Several LOB-related enhancements are included in this release. Additional references for these include the Oracle Call Interface Programmer's Guide, the Oracle8i Application Developer's Guide - Large Objects (LOBs), and Oracle8i SQL Reference.
Oracle8 provided support for permanently storing large unstructured data by means of large object (LOB) datatypes. But many applications have a need for temporary LOBs that act like local variables. These LOBs do not exist permanently in the database, but exist mainly for the purpose of performing transformations on LOB data.
Oracle8i supports the creation, freeing, access, and update of temporary LOBs. Their default lifetime is a session, but they may be explicitly freed sooner by the application. They are ideal as transient workspaces for data manipulation, and because no logging is done or redo records generated, they have better performance than persistent LOBs.
The system-generated object identifiers (OIDs) provide globally unique identifiers that are important for many applications, especially distributed processing and replication. However, they carry an overhead of increased storage requirements, and increased loading time. For applications that do not require the advantages of system-generated OIDs and which must save on overhead, Oracle8i allows users to specify their own identifier, such as a primary key, as an OID.
A related enhancement is referential integrity constraint support on REF columns.
These enhancements target making the handling and storing of large nested tables more efficient. Additional references for nested table enhancements are the Oracle Call Interface Programmer's Guide, Oracle8i SQL Reference, and Oracle8i Supplied Packages Reference.
Oracle8i provides for more efficient handling and storage of large varrays. Varray storage can now be specified. If specified, it is stored out of line in a LOB and the user can specify the storage characteristics of the LOB.
The notion of collection datatypes (nested tables and varrays) was introduced in Oracle8. Users are allowed to create columns of collection types, and functions and procedures with parameters of collection types. When it is necessary to view these collection types in a flat (relational) form, Oracle provides a way of unnesting them.
This unnesting is implemented using the TABLE() operator found in the table_collection_expression of the SQL statement. When introduced in release 8.0, the TABLE() expression took an argument of a set of rows and allowed that set to be treated like a table. Oracle8i extends this functionality so that a collection column of a table in a query may be used like an independent table.
Object views are an extension to the basic relational view mechanism to provide an object abstraction over existing relational and object data. They allow for the retrieval, update, insertion, and deletion of relational data as if such data were stored as object types. In this release, object views are enhanced to provide more features than are available in object tables, such as, updatability of nested tables and creation of new objects in the OCI object cache. The performance of object views is improved to be comparable with that of object or relational tables.
Triggers are PL/SQL procedures which are fired when various events occur in the system, such as updates to a row. In release 8.0, the trigger mechanism was defined over rows of tables or as an INSTEAD OF mechanism over views. Oracle8i expands the application of triggers to allow them to be defined over nested table view columns. As a result, it is now possible to update an element of a collection synthesized using the CAST...MULTISET operation.
The following enhancements are options that can be set for the object cache:
For more information, also see the Oracle Call Interface Programmer's Guide.
The ANALYZE command can now set dangling REFs to NULL. In this case, these REFs no longer raise an error condition and terminate execution.
For more information, also see Oracle8i SQL Reference.
In release 8.0, if a relational table or an object table contained REF type columns, there was no simple way of inserting a relational row or a row object into the table. First NULLs were required to be inserted into the columns and then an update statement was issued to set their values. This inefficiency is resolved in Oracle8i, by allowing a value subquery expression in the INSERT...VALUES list.
A value subquery expression may also be used as an argument to a function or a type constructor.
The SQL*Loader, used to move data from external files into tables in an Oracle database, has been extended to support the loading of objects, collections, and LOBs. For a summary of SQL*Loader enhancements, see "SQL*Loader Enhancements" .
For more information, see Oracle8i Utilities.
The partitioning of tables with columns containing objects, collections (varrays and nested tables), and REFs is provided in Oracle8i. The partitioning of object tables is also supported.
Oracle8i provides parallel query support on object tables.
After carefully tuning an application, a user may wish to ensure that the optimizer generates the same execution plan whenever the same SQL statements are executed. Oracle8i provides a method for stabilizing execution plans, regardless of changes to the system configuration, parameters, statistics, or even changes to the optimizer itself. This also benefits high-end OLTP sites by having SQL execute without having to invoke the cost-based optimizer at each SQL invocation.
This feature is especially useful for ISVs, who can distribute the execution plans of the SQL in their package applications so that all installed sites will be executing the same execution plans.
Through a new SQL statement, CREATE OUTLINE, the user is able to create a stored outline containing a set of attributes used by the optimizer to create an execution plan. Stored outlines can also be created automatically by setting the system parameter CREATE_STORED_OUTLINES to TRUE. The system parameter USE_STORED_OUTLINES can be set to TRUE, FALSE, or to a category name to indicate whether to make use of existing stored outlines for queries that are being executed. The OUTLN_PKG package provides procedures used for managing stored outlines.
Advanced Queuing (AQ) was introduced in release 8.0 of Oracle8 Enterprise Edition. It integrated a message queuing system with the Oracle database through a set of queue tables and queue functions, and allowed for either synchronous or asynchronous communication between programs. Oracle8i provides enhancements to the functionality of Advanced Queuing. These are discussed in this section.
For more information, see the Oracle8i Application Developer's Guide - Advanced Queuing.
Additional features are introduced in Oracle8i Advanced Queuing to support a publish/subscribe messaging paradigm. In this model, database queues serve as a repository for the messages, rules, and control information. There is a decoupling of addressing between message senders and message recipients. It is the recipients who specify which messages they wish to receive. Sending applications anonymously publish messages and receiving applications independently and autonomously subscribe to such messages.
The components of this publish/subscribe model are:
Publishers are entities that publish information (as messages) to queues. They do not know or care about the interest of other applications in the messages; they only know that they are to publish the information. There are two types of publishing: database event publishing where triggers cause messages to be enqueued when certain database or system events occur, and publishing from an application. Database event publishing is new in Oracle8i and is discussed separately later.
Subscribers are entities that receive information by expressing interest in certain types of messages. They do not care about the origins of the messages. They specify certain filtering rules for messages, allowing them to receive only the messages whose content satisfies the rules.
The rules engine, new in Oracle8i, provides rules-based subscriptions by evaluating incoming messages from publishers against a set of rules specified by subscribers. Only if the message satisfies a rule is it delivered to the subscriber. Subscribers can express rules using the full power of SQL. Since AQ messages are normal database objects, any SQL operation that applies to data objects can also be applied to messages.
An application (subscriber/client) may receive messages that are published in one of three ways: explicit dequeuing, dequeuing with listen, or by using OCI functions to register a callback. For more specific information, also see the Oracle Call Interface Programmer's Guide.
The database event publication feature allows applications to subscribe to database events just as they subscribe to messages from other applications. Trigger syntax is extended to support system and other data events on DATABASE and SCHEMA, Trigger syntax also supports a CALL to a procedure as the trigger body.
Users can enable the publication of the following events.
The database event publication subsystem tightly integrates with the AQ publish/subscribe engine. For a complete description of triggers for data and system events, see Oracle8i SQL Reference.
For increased scalability, the performance of basic queue operations has been enhanced and changes have been made to underlying data structures. Message propagation scheduling is improved. AQ propagation uses message streaming and eliminates the current two-phase commit mechanism. Additionally, the identity of the sender is now made part of message properties. This meets a proposed standard for messaging services and enables messaging services such as TIBCO's RVCM to distribute messages in near real time to a large number of applications.
Statistics are now maintained on the scheduled propagation, enhancing the administrator's ability to manage and tune propagation. Also, a single view is provided for the administrator that can be used to determine the propagation schedule, its status, and last error.
There is better history management for multi-consumer queues, and the history itself will now contain a new message state and property for distinguishing between a message being propagated and its being processed.
Other Advanced Queuing enhancements are as follows:
Oracle8i provides enhanced block corruption repair capability through the new DBMS_REPAIR package. It provides the DBA with a three-stage approach to addressing corruptions.
Depending upon the nature of the repair, data may be lost and logical inconsistencies may be introduced. The DBA must determine whether the potential loss warrants the use of this feature, and to help the DBA, this package also provides a report mechanism that aids in the determination of whether to use this feature or not.
Log files contain a wealth of useful information about the activities and history of an Oracle database, but until Oracle8i there has been no easy tool that could tap into this information. LogMiner allows online and archived redo log files to be read, analyzed, and interpreted by the user using SQL. Analysis of the log files with LogMiner can be used for the following:
No additional collection overhead is incurred to obtain data for LogMiner.
For more information about LogMiner, see Oracle8i Backup and Recovery Guide.
A new drop column capability allows the DBA to easily remove unused columns in the database. Previously, it was necessary to use the export/import utilities. New syntax for the ALTER TABLE statement allows a column to be marked as unusable, without freeing up space in the table, or to be dropped from the table with the data deleted.
This feature complies with Transitional SQL92.
Prior to Oracle8i, management of free and used extents within a tablespace relied heavily on data dictionary tables. Now, Oracle introduces a new mechanism for managing space within a tablespace: locally managed tablespaces. All extent information is tracked in the tablespace itself, using bitmaps.
Bitmaps manage space allocation very efficiently, and require no dictionary access to allocate or update extents for the tablespace. The result is better space management, reduced fragmentation, and increased reliability
Creating or rebuilding an index on a table improves query performance when completed, but can be a very time-consuming operation. For large tables, it can require several hours of downtime because it forces a lock on the table and prevents concurrent DML. This is called offline index build.
Oracle8i supports the online creation or rebuilding of an index that works for partitioned or nonpartitioned B*-tree indexes, including index-organized tables. Since the table is not locked, DML operations and queries can execute on the base table while the index is being built.
New SQL syntax specifies the online index creation or rebuild. Specifically, the ONLINE keyword may be specified on the CREATE INDEX or ALTER INDEX statement.
Alternatively, if it is not desired to rebuild the index because of space or other considerations, the new COALESCE keyword may be specified to defragment the index. In either case, performance will be improved and space recovered.
A new MOVE clause used in an ALTER TABLE statement provides a means to easily reorganize a nonpartitioned table by allowing the user to move data into a new segment while preserving all views, privileges, etc. defined on the table. The operation is performed offline.
Oracle8i improves the performance of the operation that places a tablespace in read-only mode. A tablespace in Oracle8i can be placed in read-only mode when there are no outstanding transactions in that tablespace alone, unlike previous versions of Oracle where the operation completed only when there were no outstanding transactions in the entire database.
Now, an ALTER TABLESPACE...READ ONLY statement will make the tablespace read only as soon as all transactions on that tablespace have completed. This capability is especially important for the transportable tablespaces feature described in "Transportable Tablespaces" .
For more information, see Oracle8i Concepts.
A temporary table is a table with session-specific or transaction-specific data. It is empty when the session or transaction begins, and discarded at the end of the session or transaction. Its definition is visible to all sessions but the data is visible to, and can be queried by, only the session that inserts the data into the table. It is created in the user's temporary tablespace. Undo (rollback to savepoint) is supported, but not redo (crash recovery). Temporary tables can be useful for saving intermediate results that can be joined back into another table. They also might facilitate migration from other databases.
For more information about temporary tables, see Oracle8i Concepts.
Oracle8i National Language Support includes significant new enhancements that make it easier to deploy applications requiring local language support, or that require country-specific formatting.
These are some general enhancements to NLS.
For more information, refer to the new Oracle8i National Language Support Guide.
Oracle8i makes it possible to change both the character set the database uses to store data, and the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2, after the database has been created. The old character set must be a strict subset of the new one.
A user with SYSDBA system privilege, may issue the following command statements:
For more informations, see Oracle8i SQL Reference.
This enhancement implements the ANSI standard TRIM function. It combines the functionality of the existing LTRIM and RTRIM functions, allowing the user to trim leading or trailing characters, or both, from a character string.
For more informations, see Oracle8i SQL Reference.
An external routine, previously referred to as an external procedure, is a routine written in another language and stored in a dynamic link library (DLL), or libunit in the case of a Java class method. The routine is registered with the base language, and can be called to perform special-purpose processing.
First introduced in Oracle8, external procedures allowed the writing of C functions as PL/SQL bodies. These C functions are callable from PL/SQL and SQL (via PL/SQL). In Oracle8i, a special-purpose interface, the call specification, is provided that lets external routines be called from other languages.
While this service is designed for intercommunication between SQL, PL/SQL, C, and Java, it is accessible from any base language that can call these languages. For example, a routine can be written in a language other than Java or C and still be usable by SQL or PL/SQL, provided that is callable by C. Therefore, a C++ routine, would use a C++ extern "C" statement in that routine to make it callable by C.
Below is a brief description of the new call specification, and a discussion of other changes targeting enhancement of external routine performance.
For information about external routines, see Oracle8i Application Developer's Guide - Fundamentals.
Until now, an external routine has been published to Oracle via an AS EXTERNAL clause in a PL/SQL wrapper. This wrapper defines the mapping to, and allows the calling of, external C routines. Oracle8i introduces call specifications, which include the AS EXTERNAL wrapper as a subset of a new AS LANGUAGE clause. AS LANGUAGE call specifications allow the publishing of external C routines, as before, but also Java class methods.
Call specifications also allow publishing with the AS EXTERNAL clause, introduced in Oracle8. For new applications, however, the AS LANGUAGE clause should be used.
A DLL caching mechanism has been developed to allow reuse of already loaded libraries for subsequent external routine invocation. This avoids costly repetitive operations, thus improving the performance, usability, and scalability of external routines. DLL caching applies to both server-side external routines, invoked through the extproc agent, and to client-side external routines.
Certain object support, previously available to trusted external routines only, is now available to untrusted and distributed routines. Specifically, all external routines now support object types as argument types, and clients are allowed to issue object-related OCI callbacks.
For a discussion of the virtual private database feature, whose components are fine-grained access control and application context, see "Securing Data in the Oracle Database Server". This feature secures data in the database by providing security at the row-level, across all applications, by attaching a security policy directly to a table or view.
Range partitioning was introduced in Oracle8, release 8.0. Oracle8i provides enhancements for range partitioning, but more significant is the expansion of Oracle's repertoire of partitioning methods by introducing two new methods: hash and composite.
A new statement, ALTER TABLE...MERGE PARTITIONS, may be used to merge the contents of two adjacent partitions of a table partitioned using the range method. This is the inverse of a SPLIT PARTITION operation.
A new clause, ENABLE ROW MOVEMENT, of the ALTER TABLE or CREATE TABLE statement allows rows to be moved between partitions. When row movement is enabled, updates affecting partitioning keys in such a way that a row no longer belongs in its current partition will proceed, resulting in a row migrating to the appropriate partition. The default behavior (DISABLE ROW MOVEMENT) is that row movement is disabled and such updates are disallowed, resulting in an error being returned to the user.
The new partitioning methods introduced in Oracle8i are hash and composite. Along with range partitioning, these present a rich set of partitioning methods that allow the DBA to choose the partitioning method that will offer the best performance depending on the database workload and the application's profile. As with range partitioning, all partitioning techniques are transparent to applications and standard DML statements run against the partitioned tables.
Hash partitioning provides a very simple way to break data into evenly sized containers to be spread across multiple I/O devices, or even multiple machines in a shared-nothing cluster. Query performance is improved by spreading I/O across multiple devices, and the performance of parallel DML may also be improved. Note, however, that hash partitioning is inappropriate for rolling change windows of historical data because the data for any period of time can be spread across multiple partitions.
Composite (range/hash) partitioning provides the manageability and availability benefits of range partitioning with the data distribution advantages of hash partitioning. The user specifies ranges of values for the primary partitions of the table or index, then specifies a number of hash subpartitions. Data skew is unlikely, because the user can always add or drop subpartitions within a partition to maintain even distribution of each container. Rolling change windows of historical data are easily maintained by adding or dropping primary partitions with no effect on subpartitions in other primary partitions.
Local indexes are supported with hash and composite partitioning. All existing SQL query and DML statements work with both new partitioning methods and new or extended DDL statements are provided for maintenance.
Enhanced partitioning includes many performance enhancements, some of which have already been discussed. Not yet discussed are the following two.
Partition elimination is the skipping of unnecessary index and data partitions (or subpartitions) in a query. Because Oracle8i enhanced partitioning provides the database with greater knowledge of data placement, it can perform more advanced partition elimination. Particular focus is on queries using disjunctive OR or IN predicates.
In Oracle8, as the first step when two tables are being joined in parallel, each table is split into some number of separate pieces, scanned, then redistributed on the join column. In Oracle8i, the redistribution step can be skipped if the data is already partitioned on the join key. This reduces memory and temporary storage requirements and increases overall performance.
Full support for partitioned tables with LOB columns is now provided. The LOB value can be stored inline in the row or out of line in a separate segment. All LOB types are supported: BLOB, CLOB, NCLOB, BFILE.
Very large databases, historical databases, and OLTP databases with special availability requirements can benefit from the partitioning of index-organized tables (IOTs). Oracle8i delivers this support for partitioning of IOTs and the secondary indexes defined on IOTs. The bulk loading of partitioned IOTs with secondary indexes via SQL*Loader direct path is supported and provides the most efficient way of loading data.
This section is concerned with the new functionality and tools that greatly enhance your ability to control resource usage, enable better recoverability and availability in the database, and make your database a lot easier to manage.
Traditionally, resource management decisions have been left to the operating system, rather than the database server. This results in the following problems.
The database resource manager feature addresses these issues by giving the database more control over the management of resources. Oracle8i's database resource manager feature provides the DBA with the ability to control and limit the total amount of processing resources available to a given user or set of users.
For example, suppose that a data warehouse is shared by the marketing department and the sales department. Using the database resource manager, a warehouse administrator could specify that the marketing department receives at least 60 percent of the CPU resources of the machines, while the sales department receives 40 percent of the CPU resources. The warehouse administrator could further specify limits on the total number of active sessions, and the degree of parallelism of individual queries for each department.
Specifically, using the database resource manager facility, the DBA can:
In addition, a user can select the priority of a session from a given set of priorities that the DBA has assigned to that user.
DBAs use resource consumer groups, resource plans, resource allocation methods, and resource plan directives to implement database resource management. There functions are:
resource consumer group
They allow the administrator to group user sessions together based on their requirements for processing resources.
It contains resource plan directives that specify the resources that are to be given to each resource consumer group.
resource allocation method
It determines what method (or policy) to use when allocating for any particular resource. Resource allocation methods are used by both plans and consumer groups.
Resource plan directives
There is one of these for each consumer group in the plan. They allow the administrator to assign consumer groups to particular plans and partition resources among consumer groups by specifying parameters for each resource allocation method.
Resource plans, resource consumer groups, and resource plan directives are created using the PL/SQL package DBMS_RESOURCE_MANAGER. Users are assigned to consumer groups using the DBMS_RESOURCE_MANAGER_PRIVS package. The initialization parameter RESOURCE_MANAGER_PLAN specifies which top plan to use for a given instance. The database resource manager loads this top plan as well as all its descendants (subplans, directives, and consumer groups).
The Oracle database server's industry leading availability and reliability are further extended in Oracle8i. Significant enhancements and new features expand our customers' ability to provide highly available data center solutions and meet service level objectives.
Unless otherwise noted, information about the new recovery and availability features discussed here can be found in the Oracle8i Backup and Recovery Guide.
Oracle8i provides the means to allow the background archive process (ARCH), or its foreground equivalent, to archive online redo log files to multiple destinations. Up to five destinations can be specified with the initialization parameter: LOG_ARCHIVE_DEST_n. A destination can be a local disk-based file or it can be a user-specified standby database that is either local or remote to the primary database.
It has been observed that two archival destinations is not enough, while more than five is excessive and provides no additional benefit.
Oracle8i allows the user to specify multiple archive processes to be invoked at instance startup. This is intended to alleviate bottlenecks that can occur when the LGWR (log writer) process is able to write to the redo log file faster than ARCH can read from the log file and write to one or more destination archivelogs. It will no longer be necessary for users to attempt manual foreground archival operations in order to recover, as multiple archive processes will automatically handle the situation.
Automated standby databases provide the means to create and maintain multiple remote copies of a production database for disaster recovery scenarios. This is one of the key technologies available to protect against a disaster to the primary database system or data center. The backup or automated standby database can reside in the same or different data center and take over the processing from the primary production database providing near continuous database availability. Oracle has provided the standby database feature for a number of years, but the technology is significantly extended in Oracle8i.
A standby database is initially created by copying or duplicating the production database. As archived redo logs are generated on the production database, they are applied to the standby database. This allows the standby database to remain synchronized with the production database. In releases prior to Oracle8i, the redo logs were manually transported or copied to the standby database and manually applied.
With the new automated standby database feature, the archived redo logs can be automatically transferred and applied. This eliminates the need for manual procedures to copy and transmit the redo logs and the need for the operator at the backup site to manually specify which logs to apply. The standby database is placed in constant recovery mode by the user, causing it to wait for archived log files from the primary database. When a primary archived log file arrives at the standby site, it is verified and then applied to the standby database.
Another new capability is the ability to use a standby database as a read-only database. All databases, not just standby databases, benefit from this new feature, but it is especially useful for standby databases. This option can be used to make a standby database available for queries and reporting, even while archive logs are being copied from the primary database site.
Transparent application failover (TAF) was introduced in Oracle8 and masks system failures from applications. It is discussed in "Transparent Application Failover (TAF)". To allow the Oracle database server to quickly recover from system faults and minimize the impact on users, Oracle8i introduces fast-start fault recovery. It consists of the following components.
As a means of controlling the time required for instance and crash recovery, this feature allows the DBA to specify an upper limit on the number of I/O operations that Oracle will need to perform during instance recovery. A new dynamic parameter, FAST_START_IO_TARGET is introduced to control Fast-Start checkpointing.
Faster recovery is achieved at the expense of writing out additional (meaning: more than would have been written if this feature were disabled) buffers during normal processing. The fewer the number of dirty buffers in the buffer cache at the time of the failure, the faster the recovery times. This is because recovering data blocks by applying redo records between the most recent checkpoint and the end of the log constitutes the majority of the work and time during recovery. Statistics are collected and are available to the DBA through dynamic performance views to tune the system and make tradeoffs between runtime performance and database availability.
For additional information on this feature, see Oracle8i Tuning.
This feature enables dead transactions to be recovered, on demand, one block at a time. This improves the availability of the database for users accessing data that is locked by large dead transactions. In earlier releases, users are blocked until the entire dead transaction is recovered. With this feature, the user immediately recovers only the block under consideration and proceeds, leaving the rest of the dead transaction to be recovered in the background.
This Oracle8i feature is primarily targeted to solve the problem of recovering parallel transactions. Parallel transactions in release 8.0 exploited parallelism in the roll forward stage using parallel block recovery. But on a failure, the failed transactions were rolled back serially by the background process. This impacted the availability of the part of the database involved in the failed transactions.
Fast-start parallel recovery allows transactions to be recovered in parallel, resulting in significantly increased recovery throughput. Inter-transaction recovery is performed like parallel block recovery, where each slave rolls back a separate transaction. But if one of those transactions is extremely long, then intra-transaction recovery is invoked, where that transaction is divided up among the slaves.
Fast-start parallel recovery is started when SMON discovers that the amount of recovery work that is required is above a certain threshold. The threshold is the amount of work at which the time it takes to recover in parallel becomes less than the time it takes to recover serially.
This new enhancement provides a mechanism by which all I/O in the database can be stopped, enabling copies of the database to be made without I/O interference. Two new statements are introduced. The ALTER SYSTEM SUSPEND statement stops new lock and I/O activity from being initiated. The creation of backups or archived logs is not affected. The database remains suspended until an ALTER SYSTEM RESUME statement is issued.
This enhancement enables the Recovery Manager to correctly interpret tablespace names for recovery when the character set is other than the default. This is done by storing a character set ID in the control file. When the database is first opened, this character set ID is propagated from the data dictionary into the control file database information record. The information is then available from the control file, before database open, for any subsequent recovery attempts.
If the control file is lost, recovery is still possible. The DBA may specify the character set as an argument on the CREATE CONTROLFILE statement.
Changes to the Recovery Manager are presented below. Additionally, the Oracle8i Backup and Recovery Guide has been extensively rewritten. Most notably, it now contains an exhaustive reference for RMAN command syntax, and a chapter on recovery catalog views.
The use of a recovery catalog is now optional. If you choose not to use a recovery catalog, you can still use RMAN very effectively. RMAN obtains the information it needs from the control file of the target database. When using a recovery catalog, RMAN can perform a wider variety of automated backup and recovery functions. For this reason, Oracle recommends using a recovery catalog with RMAN whenever possible.
A related enhancement is discussed above in "Control File Character Set".
RMAN includes changes to support version 2.0 of the Oracle Media Management API, commonly referred to as System Backup to Tape API, or SBT. Support for the version 1.1 Media Management API is maintained.
In order to utilize different types of tertiary storage for Oracle database backups, RMAN requires that third party media management software be installed. This software interfaces to Oracle8i and RMAN using SBT. It is the responsibility of this vendor supplied software to load, label, and unload sequential media such as tape drives for the purpose of backing up and recovering data.
Through an enhancement called proxy copy, the media management vendor software is able to take over the entire data movement involved in a backup or restore. RMAN merely provides a list of files requiring backup or recovery to the media manager, which in turn makes all decisions regarding how and when to move the data. The PROXY option of the BACKUP command invokes this facility. The facility must be specifically supported by the media management software--not all media management software provides this support.
Some media management software allows backup media to be arranged into storage pools based on media type, retention period, or other criteria. The new POOL operand of the BACKUP command provides tight integration between such products and RMAN.
When a channel is allocated, RMAN will display in its log a text message identifying the media management product that will be used to take backups on that channel. When the media manager encounters an error, it will return a text error message explaining the error, which will be displayed in the RMAN log.
The new SEND command allows commands to be sent directly from an RMAN session to the media management software. Typically, this command is used to control runtime options of the media manager that can not otherwise be controlled using RMAN.
RMAN can detect when backups expire from the media management software catalog or on disk, and it can update its own catalog accordingly. Two new commands, CROSSCHECK BACKUP and DELETE EXPIRED BACKUP, allow for the synchronization of the recovery catalog with the media manager's catalog.
The new CHANGE...CROSSCHECK command can perform crosschecks on both backup sets and image copies.
The output of the LIST BACKUP command is significantly improved. It now prints the list of backups belonging to a backup set in a separate section of the report from the list of data files or archived logs included in the backup set.
A new command, REPORT NEED BACKUP REDUNDANCY, has been implemented. When fewer than a user-specified number of backups of a data file exist, this report alerts the user that a new backup is required.
The following new commands make it easier to create, upgrade, and drop the recovery catalog.
This command creates the recovery catalog. It replaces catrman.sql and associated scripts in the admin directory, which had to be run manually to create the recovery catalog schema.
When RMAN detects that the version of the recovery catalog is too old, it will issue an error message indicating that the recovery catalog requires upgrading. Previously it was necessary to run a SQL script to perform the upgrade. Now the upgrade is done with the UPGRADE CATALOG command. This command upgrades the recovery catalog from any prior version. No data is lost; the backups which were taken with the recovery catalog remain intact.
This command removes the recovery catalog schema.The backups themselves are not removed, so if there is a duplicated or replicated catalog available, or the information is available in the control file, the backups can still be used for restore.
These new commands allow for more types of recovery with a single RMAN session. It is no longer necessary to exit RMAN to shutdown, startup, mount, or open the database.
These commands have the same syntax as the corresponding SQL*Plus commands.
The following two forms of the ALTER DATABASE command are supported.
The DUPLICATE command allows creation of a replicated database using the backups of another database. Typically, a database is duplicated in order to create a test database that is separate from the production database.
When backing up on multiple nodes of a parallel server, it is possible that some disks have 'affinity' to certain nodes in the cluster such that access to those disks is faster from those nodes than from other nodes in the cluster. RMAN recognizes node affinity, if it exists, and will attempt to schedule data file backups on channels allocated at nodes that have affinity to those files. To use this feature, channels must be allocated at more than one node in a parallel server cluster.
The following are RMAN changes affecting backups.
Recovery Manager can now create up to four concurrent copies of each backup piece. This feature is particularly useful for archivelog backups. If a data file backup is bad, an earlier backup of the same data file can usually still be used for recovery, but if an archivelog backup is bad, then no recovery beyond the point in time when that log was created is possible. Because archivelogs are critical to recovery, customers often require that archivelogs be backed up twice before being deleted from disk.
RMAN no longer requires that backup piece names be explicitly specified using the FORMAT parameter. By default, RMAN chooses a unique name for each backup piece.
A backup piece will no longer be overwritten if an attempt is made to create a backup piece with the same name as an existing one. RMAN now issues an error.
It is now possible to perform TSPITR (Tablespace Point in Time Recovery) without a recovery catalog, however some restrictions apply.
Two new views are available to monitor the progress and performance of Recovery Manager backups. These are V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO. These views can be used to monitor the progress of individual files in a backup or restore, and to identify performance bottlenecks. A complete description of how to use these views to tune backups is in Oracle8i Tuning.
For Oracle8i provides the following the SQL*Loader enhancements:
For more informations, see Oracle8i Utilities.
Many of the changes for the Import and Export utilities in Oracle8i are in support of other features. However, there are a few enhancements in these utilities which stand on their own.
For more information on any of the above, see Oracle8i Utilities.
Oracle is committed to making the Oracle database server simpler to use and administer. Reducing the number and duration of administrative tasks reduces the total cost of ownership for a system. Fewer administrators are required for day-to-day operations, and they can spend their time on more challenging issues such as performance tuning and database design. For very low end systems in particular, Oracle is working toward making them as self-administering and self-tuning as possible.
The following Oracle8i components support Oracle's goal toward achieving operational simplicity.
The Oracle Software Packager and Oracle Universal Installer work together to provide a robust method for packaging and installing Oracle8i software, whether for simple installations or those with complex interdependencies.
The new Oracle Software Packager is a Java-based, object-oriented tool used for packaging products into components that can later be installed by the Oracle Universal Installer. Using wizards, it provides a visual development environment for generating installation packages in a format that can be interpreted by Oracle Universal Installer.
A component is the basic object to be installed by Oracle Universal Installer. The component wizard guides the install developer through the specification of the installation properties of the component, like component name, version number, operating systems and languages, the files or groups of files to be installed as part of the component, dependencies, variables, and the sequence of dialogs and actions that make up the installation plan. These properties are stored in a component definition file.
Dialogs represent user interface screens that are displayed to the installing user at run time and are used to assign values to variables. Action blocks are the state-change portions of the installation, and perform such tasks as copying files, setting environment variables, creating users, etc. The Oracle Software Packager is extensible and customizable.The install developer can take advantage of the packager's Java class libraries implementing predefined actions or dialogs, or request that the packager import custom Java classes.
Once the developer has defined the installation characteristics, Oracle Software Packager can verify the component definition and stage it.The verify wizard checks the component's integrity, while the staging wizard moves the component from the development area to a distribution medium, such as CD-ROM, Internet Server, or NFS system.
The Oracle Software Packager does not require access to a component to allow it to be defined as a dependent of the component being packaged. From simple installation definitions to very complex bundled installations, Oracle Software Packager enables packaging of Oracle software, as well as easy integration of ISV and value-added reseller (VAR) software into a common installation.
For more information, see the Oracle Software Packager User's Guide.
Oracle8i is installed using the new Java-based Oracle Universal Installer, presenting the same look and feel across all platforms. Oracle Universal Installer interprets the staging area prepared by Oracle Software Packager and performs the appropriate installation actions. Its features include:
The user is guided through the installation process by wizards and online help.
The Oracle Database Configuration and Data Migration Assistants have been enhanced to further simplify the creation, deletion, modification, and migration of Oracle databases.
For information on either of these products, refer to your operating system specific Oracle installation documentation. Some features may not be available on all operating systems.
The Oracle Database Configuration Assistant enables the creation, modification, or deletion of an Oracle database. In Oracle8i, it can be invoked as a standalone Java application from the Oracle Universal Installer, or as an applet from the Java-based Oracle Enterprise Manager.
A wizard interviews the user for information relevant to the use and environment of the database. At the end of this process a pretuned starter database, based on a seed database distributed on the installation CD-ROM, can be created immediately, or a SQL script is generated for creating the database at a later time. The actual database generation runs in quiet mode from parameters generated or selected based on the interview. The creation of remote databases is supported using the Oracle Enterprise Manager's intelligent agent.
The Database Configuration Assistant provides the ability to create an Oracle OFA (Optimal Flexible Architecture) compliant instance. The OFA-like file layout provides a standardized file layout and eases management and maintenance.
If the user selects to modify a database, installed cartridges that have not previously been configured for use with the database can be configured. Also, multi-threaded server support for the database can be enabled or disabled.
When deleting a database, all database files except the initialization parameter file are deleted.
Oracle Data Migration Assistant is a GUI tool that seamlessly transforms the data of an existing Oracle database to the current Oracle database release. It changes data file headers but leaves actual data unchanged. It does not copy data.
The Oracle Data Migration Assistant can migrate an Oracle7 database to Oracle8i. See your operating system specific Oracle documentation for information about the earliest release that the Oracle Data Migration Assistant can migrate on your operating system.
Version 2.0 of the Oracle Enterprise Manager delivers greater scalability and ease of use through the following enhancements.
The first tier, of Oracle Enterprise Manager's three-tier model, consists of a Java-based console and integrated applications which can be installed or run from a Web browser. Administrators can access the console from virtually anywhere, and the 100% Java framework provides heterogeneous platform support.
The middle-tier component is the Oracle Management Server (OMS). Its main function is to provide centralized intelligence and distributed control between clients and managed nodes, thereby processing and administering all system management tasks. As the number of system management tasks increases, the architecture scales through the addition of more OMSs. Failover and load balancing are also automated within the OMSs, providing much greater reliability in notification processing.
The third tier is comprised of managed services or targets, such as databases, nodes, applications or application servers. An intelligent agent functions as the executor of jobs and events sent by the OMS. The agent works independently from the Console, the OMS, or network connections. Once an Agent is assigned a task from the OMS, it will perform the work at the time scheduled, and report status back to the OMS. An example of one such task might be performing weekly database backups.
Oracle Enterprise Manager, Version 2.0, is a multi-user system. Multiple administrators with separate accounts and permissions can log into their own consoles. A shared repository allows them access to the same information from their separate consoles.
For more information, refer to the Oracle Enterprise Manager documentation set.
Numerous enhancements to Oracle Parallel Server (OPS) deliver major advances in performance, scalability, and manageability. Additionally, by extending commonly used single-instance diagnostic tools and functionality to be OPS-aware, DBAs, development, and support personnel are better able to diagnose, tune, and monitor systems. Summaries of these OPS enhancements are presented below.
For more information see Oracle8i Parallel Server Concepts and Administration.
In Oracle8i, new architecture dramatically decreases the need for workload partitioning and allows all applications to obtain the performance and scalability benefits from an OPS implementation. Changes include:
Oracle 8i improves the diagnosis of system performance problems and the administration of the OPS environment with improved tools.
Oradebug is a utility used by consulting and support personnel to diagnose and troubleshoot poorly behaving systems at run time. Previously, it was best suited for single-instance situations, but now that functionality is extended for the Oracle Parallel Server.
Enhancements to OPSM provide for greater ease in administration of the Oracle Parallel Server. A single generic interface is presented for administering parallel servers on any platform, helping administrators to easily manage the complexities of the Oracle Parallel Server product. Here are some of the features.
For more information about enhancements to OPSM, see the Oracle Parallel Server Management Users Guide and the Oracle8i Parallel Server Setup and Configuration Guide.
In Oracle8i, only a single installer session is required to install an OPS database. The installer collects node information from the user, distributes the required Oracle products to the specified nodes, and then invokes the OPS Assistant to set up the instances and create the database. When OPS Assistant is finished, the parallel server is up and available on all nodes, and the parallel server configuration information is saved so that OPSM can use it to manage the new parallel server.
For more information, see the Oracle8i Parallel Server Setup and Configuration Guide.
Instance affinity for jobs is the association of jobs to an instance. Through the new DBMS_JOB package, the user can indicate whether a particular instance, or any instance, can execute a user-submitted job in the Oracle Parallel Server environment.
This Oracle8i feature can be used to improve load balancing and limit block pinging. For instance, using Oracle Parallel Server and replication at the same time will result in block pinging problems on the deferred transactions queue if all instances in a clustered environment decide to propagate transactions from the deferred transaction queue. By limiting the activity against tables to only one instance within the parallel server cluster, pinging can be minimized.
There are recoverability and availability improvements which enhance the reliability of the Oracle Parallel Server. These are covered in more detail elsewhere in this chapter, but are mentioned briefly here.
The following new Net8 enhancements, described elsewhere, improve ease of use and scalability for the parallel server.
Oracle8i extends the functionality of advanced replication, focusing on mass-deployment applications. Heterogeneous services, introduced in release 8.0 and which implemented an extensibility framework for accessing non-Oracle systems, has also been further enhanced.
The Oracle8i features and enhancements described below comprise the overall effort to optimize replication performance and make snapshot environment distribution and security more effective. All are included in advanced replication.
Significant performance gains are realized by the internalization of PL/SQL replication packages and by optimizations to snapshot refresh.
Continuing the trend started with release 8.0, more replication code has been moved into the database engine. The PL/SQL generated packages used to apply replicated transactions at a remote site have been internalized. This allows replicated transactions to be more efficiently applied at remote sites and because packages are not generated, a site can be more quickly instantiated. Internal packages are also more secure because they are tamper proof.
Snapshot refresh has been optimized to support large refresh groups. There is improved support for subquery snapshots, and for null refresh (no changes to the master tables since the last refresh). A single refresh group can now contain 400 snapshots, and the number of roundtrips required to refresh snapshots in a refresh group has been reduced. (This feature was first added in release 8.0.5.)
In Oracle8i, Oracle adds new functionality to better support mass deployment and front office applications.
These facilitate the mass deployment of information to support such applications as field service and sales force automation. These templates represent a grouping together of snapshots and other database objects to be instantiated at a node. They allow a DBA to centrally package a snapshot environment for easy, custom, and secure distribution to one or multiple sites. The goal is to create the environment once, then deploy the snapshot deployment template as often as necessary. Template parameters allow data subsetting at a remote site without redefining the template, and a template may be defined as public or private. Public templates may be instantiated at any site, whereas private ones can be instantiated only at predefined, authorized sites. An Oracle Replication Manager deployment wizard guides the DBA through the selection of schema objects to add to the template, the selection of parameters, and defining authorizations.
Updatable snapshots can now be subsetted horizontally (selected rows) or vertically (selected columns). Previously, only horizontal subsetting was allowed. Vertical partitioning allows the deployment of the minimum amount of data needed by a remote site, thus reducing connection time. It also protects snapshot sites from changes to their associated masters. A column can be added to a master site without impacting the snapshot site, or a column can be deleted and not impact the snapshot site, if the snapshot site does not currently reference that column.
Snapshot deployment templates can be instantiated online or offline. Online instantiation allows a snapshot site to instantiate the template while connected to the target master site. The advantage here is that the data will be current. However, this is at the cost of requiring a live connection, possibly of long duration and having the potential of generating extreme network traffic that could degrade other network services.
Offline instantiation allows the DBA to package the deployment group templates and required data onto some type of storage media (tape, CD-ROM, etc.) for distribution to a snapshot site. Instead of connecting to the master site, instantiation can be done by pulling the template and data from the storage media. Users can fast refresh immediately after completing an offline instantiation; a full refresh is not required. Offline instantiation is an ideal solution for mass deployment situations where many disconnected laptops will be instantiating the target template.
While the scripts used to instantiate a snapshot site are generated at the master site and can control access to data, it is still necessary to connect to a receiver and proxy snapshot administrator to propagate replicated transactions and to refresh snapshots. Oracle8i enhancements to the replication security model eliminate certain security deficiencies regarding the granting of privileges to untrusted sites.
A number of improvements have been made to the Oracle Replication Manager; some have already been mentioned. A major and noticeable enhancement is that it has been rewritten to conform to the new Oracle Enterprise Manager (OEM) Java interface. Oracle Replication Manager can now be run from anywhere in the network, and it is not constrained to a Windows-based machine. There is also a replication class of events in OEM which, for example, can be used to monitor errors or delinquent snapshot refreshes.
The integration between the Oracle8i Enterprise Edition and Oracle Lite has been improved to provide better performance and increased functionality. All of the replication changes previously described are supported, and Oracle Lite users with laptops at remote sites will especially benefit from reduced connection time.
Heterogeneous services (HS) implements an extensibility framework for accessing non-Oracle systems. Introduced in Oracle8, HS integrates the core of Oracle's gateway technology directly into the Oracle database server by extending the Oracle SQL engine to optimize and rewrite SQL for non-Oracle datastores. This closer integration has improved performance. For instance, the Transparent Gateway for DB2 has provided a substantial performance improvement, particularly where large numbers of rows are retrieved.
Oracle8i enhances HS with the following changes. For more information, see Oracle8i Distributed Database Systems.
Heterogeneous service (HS) agents have been made multi-threaded. The architecture chosen for this implementation is similar to the Oracle MTS architecture. Specifically, there is a set of dispatcher threads to receive requests from Oracle database server processes and return results to them, and a pool of task threads to process the requests and compute results.
This enhancement will reduce the amount of system resources consumed when there are large numbers of user sessions concurrently accessing the same non-Oracle system. This more efficient use of system resources allows a greater number of concurrent user sessions.
A set of fixed views is now available to provide heterogeneous services status and to monitor information. The following views are added:
Identifies agent processes
Identifies agent process used by all instances of an Oracle Parallel Server configuration
Identifies HS connections
Identifies HS connections used by all instances of an Oracle parallel server
The views are present at all times in the Oracle database server and can be accessed directly through SQL queries. An OEM GUI applet also displays the information. A full description of each of these views is available in Oracle8i Reference.
Agent self-registration was introduced in a previous release. It reduces or eliminates the need for DBA intervention in configuring heterogeneous services. In Oracle8i, code has been rewritten to make the self-registration process more efficient.
New in Oracle8i is an agent-specific shared library (DLL) for HS object files, other than drivers, that is substituted when linking agent executables. While the benefits from this change are platform-specific, it can improve scalability by using a single agent library for all types of agents (extproc, hsalloci, hssqlpss, hsdepxa, and hsots). Also, memory requirements may be reduced because agent executables become quite small.
In distributed database systems where it may be necessary to join tables across systems, poor data access techniques can cause user data flow between database servers to be a major factor in overall performance. In order to determine the best access technique for an optimum execution plan, the optimizer must first be able to determine a sufficient number of alternate paths. The remote join enhancements in Oracle8i present more options, allowing for better execution plans to be generated with a corresponding performance increase.
Oracle8i security and networking enhancements support standards, provide stronger security, and lower the cost of ownership. The support of standards provides for more choice, and provides for greater ease of system integration between Oracle and non-Oracle components. When allowing remote access and extending networks to the Internet, you now have a variety of standards-based and proven methods for data encryption and authenticating users, databases, and Web servers.
The virtual private database feature secures data in the database by providing security at the row-level, making it easier to handle sophisticated security requirements. It also moves the definition of the security policy out of the application and into the database, thereby eliminating the potential to circumvent the security enforcement.
Centralized user management, through integrated security and directory services, will lower your cost of ownership. Fewer accounts means less administrative overhead, and single signon increases productivity by reducing the number of logins. It can also reduce lockouts resulting from a failure to remember all of the necessary passwords.
Oracle 8i introduces the virtual private database feature. With so many means of accessing the database available, both locally and remotely, and most notably through the Internet, server-enforced and granular access control becomes crucial. Security implemented in application code is insufficient, because to bypass the security one only needs to access the data from outside the application (through SQL*Plus, for example). The virtual private database feature addresses this problem by attaching the security policy directly to a table or view.
An important benefit of the virtual private database feature is that security is built only once, and not into every application that accesses the data. Then, no matter how a user gets to the data, the same security policy is enforced.
The virtual private database feature has two components: fine-grained access control and application context. Here is how they work together to implement a safe and consistent security policy across applications.
Fine-grained access control works using a dynamic predicate, or WHERE statement. This dynamic predicate allows the security rules to be acquired at SQL statement parse time when the base table or view is referenced in a select or DML statement.
Implementing fine-grained access control consists of creating a function to implement a security policy and associating the function with a table or view. The Oracle supplied DBMS_RLS package is used to associate security policy functions with the table or view. When a query or subquery which affects that table or view is executed, the server dynamically rewrites the query appending a WHERE condition generated by the function implementing the security policy.
For example, fine-grained access control can be used to enforce a security policy "customers can see only their own orders". An application might submit a SQL statement to select "all" orders. The security policy would append an additional predicate on the WHERE clause restricting the statement to retrieve only those orders submitted by that customer. This provides row-level, server enforced, access control, that needs to be built only once to protect your data no matter how the data is accessed.
For more information, see the Oracle8i Application Developer's Guide - Fundamentals and Oracle8i Supplied Packages Reference.
An application context functions as a secure data cache for storing information used to make access control decisions. It provides more flexibility and space than using CLIENT_INFO, and represents a new approach.
Application contexts are extensible. Each application can have its own context, with its own attributes. For example, an order entry context could have customer number and sales region attributes, while a human resources application might have employee number, position, and country code attributes. These attributes are used by fine-grain access control in enforcing a security policy.
An application context can be set and reset only by a designated package. The CREATE CONTEXT statement specifies the namespace of the context and the package to which it is bound. A secure and consistent API is provided to manage and access the application context.
For more information, refer to the Oracle8i Application Developer's Guide - Fundamentals and Oracle8i SQL Reference.
With the continuing growth of distributed systems involving numerous databases and applications comes an ever increasing challenge of user authentication and user management. To meet this challenge of enterprise user management, Oracle8i provides methods of encryption, authentication, and authorization, along with integrated security and directory services, packaging them as Oracle Advanced Security.
The Advanced Networking Option (ANO) has been renamed to Oracle Advanced Security to better describe its focus and new functionality. Oracle Advanced Security runs on top of Net8 to deliver security solutions to the Oracle Network and beyond through the integration of industry standards for encryption, authentication, and remote access. Oracle8i expands the choice of security solutions by offering SSL (Secure Sockets Layer) and RADIUS (Remote Authentication Dial-In User Service) protocol adapters.
Oracle8i also enhances enterprise user management by offering integrated security and directory services through Oracle Internet Directory (OiD). User information is stored and managed in a directory and is accessed by an LDAP (Lightweight Directory Access Protocol) version 3 compliant server.
For more information, see Oracle Advanced Security Administrator's Guide.
Oracle Advanced Security includes implementation of a SSL protocol adapter. SSL is an industry standard protocol for securing network connections and is widely used over the Internet. It secures Net8 connections and other protocols as well, including IIOP connections used with thin clients and Enterprise JavaBeans.
SSL uses digital certificates and a public key infrastructure (PKI) to provide the major pieces of security:
The importance of verifying the identity not only of users, but also of machines, becomes crucial when an organization opens its doors to the Internet.
Most organizations have chosen to protect their databases by keeping them inside of the network and placing a Web server outside of the network. Used in combination with a firewall or other security measures, the data can remain safe. The data server's ability to authenticate the Web server, therefore, is extremely important. You must verify that the Web server is what it claims to be, so that you can trust it to pass data--encrypted or unencrypted--to the outside world. SSL provides the critical authentication piece, along with the data privacy piece, of the security solution.
Oracle's implementation of SSL uses industry-standard X509 version 3 digital certificates for authentication. Briefly, the authentication works as follows:
SSL supports single signon in that the user only requires one password: that which opens the wallet. The certificate and private key are used to authenticate the user to multiple services. The complete package for this authorization requires components and functionality as described in "Integrated Security and Directory Services" below.
SSL does not provide authorizations (Oracle roles), that must be provided by the application. SSL provides only public key-based authentication; this is distinct from other authentication methods provided by Oracle Advanced Security, which include tokens, smart cards, and biometric devices.
The RADIUS (Remote Authentication Dial-In User Service) protocol is an industry standard for remote authentication and controlled access to networks. It is widely accepted because of its flexibility, its ability to handle many devices, and its ability to provide user authentications, authorization, and accounting between a network client and an authentication server.
RADIUS support in Oracle Advanced Security offers two major benefits.
Your choice of authentication mechanisms is extensively expanded, with little development effort required.
Enterprise user management and security presents many challenges. Users often have too many passwords, consequently they write them down or choose the same password for all accounts. They have multiple accounts that can be cumbersome and time-consuming for an organization to manage. Another challenge is that common application information is often fragmented across the enterprise, leading to data that is redundant, inconsistent, and expensive to manage.
Oracle has recognized these challenges and has continued to evolve solutions. The Advanced Networking Option was introduced in Oracle7, providing data encryption and supporting multiple authentication mechanisms, including biometrics, DCE (Distributed Computing Environment), and Kerberos. Oracle8 introduced Oracle Security Server (OSS), supporting single signon using X.509 version 1 certificates and proprietary authentication protocol and components. Oracle8i enhances the security architecture of Oracle8 by providing an open, standards-based approach for enterprise user management.
Oracle8i addresses the enterprise's need for strong security and centralized management of application information by offering integrated security and directory services; specifically, by storing and managing user information in a directory. Multiple Oracle applications can rely on a common centralized definition of a user to determine which applications, services, and data servers a user may access, and with what privileges. The benefits of this approach include:
Oracle8i's integrated security and directory services incorporates multiple components, described below.
Oracle Wallet Manager is a tool that is used to manage an Oracle wallet. An Oracle wallet contains a user's credentials used for authenticating the user to multiple services, such as data servers and application servers. Specific wallet contents include:
The Oracle Wallet Manager gives users (and administrators of data servers) complete control over the contents of their wallets. The contents of the wallet are encrypted with a key based on a user-specified password. Since the wallet is encrypted, it may be stored locally on a user's disk, or centrally in a directory accessible via LDAP.
The user needs only to remember one password, which opens the wallet, to access his or her credentials, which can then be used to authenticate the user to multiple services. And, those services need no longer store and manage local passwords for users.
Users may obtain certificates from any X509v3 certificate authority, such as VeriSign, Inc. This is an offline and email process, and may take from a few days to weeks. Having a certificate issued in this manner guarantees its authenticity.
Certificates can be loaded into Oracle wallets and Oracle Internet Directory using Oracle Wallet Manager. Publishing a wallet into a directory enables a user to access his or her credentials from anywhere in the organization. The wallet can be retrieved by the user as needed by sending a "certificate request" and password to the certificate authority, which authenticates the request using SSL.
The Oracle Internet Directory is an LDAPv3 compliant directory. It combines a native implementation of the Internet Engineering Task Force's (IETF) LDAP v3 standard with an Oracle8 back-end datastore. This design provides the Oracle LDAP Directory Server with a high degree of scalability, reliability, and compatibility with standard interfaces. It is specifically designed to meet the needs of managing user and system configuration data, including security attributes and privileges, in an Internet environment.
The management of Oracle wallets and their contents has already been discussed, but Oracle8i also extends the benefits of enterprise roles by storing them in and retrieving them from the Oracle Internet Directory. Oracle8 introduced enterprise roles, but they were stored in a proprietary repository.
Enterprise roles enable centralized authorization of users. For example, a user may be granted the enterprise role "HR clerk", which contains the global role "HR user" on the human resources database, and the "employee" global role on the corporate information database. If a user changes jobs, his enterprise role assignment can be changed, altering his privileges in multiple databases throughout the enterprise. Also, an administrator can add capabilities to enterprise roles (granted to multiple users) without having to update the authorizations of each user independently.
Oracle8i enables LDAP access for applications running within the data server. For example, an HR application could query a directory server to retrieve information about what organization a user belongs to, or request a user's certificate, and use the results of the query to limit or expand the data the user is allowed to see, or the functions the user is able to perform. Oracle8i provides a number of Java APIs to enable applications to access an LDAP directory form within the data server.
The Directory-enabled Oracle Security Manager is an administrative tool that provides single station administration of privileges throughout the enterprise. Security administrators can manage users and authorizations across multiple Oracle8i databases and Oracle Internet Directory from a single console. In the production release of Oracle's integrated directory and security services, this tool will be become Oracle Security Manager, which will be part of Oracle Enterprise Manager.
It is also now easier to set up, configure, and administer the Oracle network, using the new Net8 Easy Config and enhanced Net8 Assistant tools. These tools are not discussed in this book, but they are described in Net8 documentation.
For more information about Net8 enhancements, see the Net8 Administrator's Guide.
Up to this release, the client was configured with the system identifier (SID) of a database instance. This SID was then passed to the listener. The listener would then verify this information and permit or deny a connection. While a SID identified a database instance, it did not identify a database. This limitation did not permit a database to have more than one service or replication of data among databases. Because a database can serve multiple services, SID has been replaced with service naming. Service naming allows clients to access:
Service naming can include multiple services provided by a single database and services that span multiple instances. For example, a personnel system called HR and finance system called FINANCE could reside in the same physical database called SALES.US.ORACLE.WORLD. Administrators could define service names according to the name of the services being provided (and not the machine type, node name, or database name). The following service names might be created:
A service can also be implemented as multiple database instances.
To support services that include multiple instances, use the following new parameters in connect descriptors:
Allows clients to access a service as a whole. It may span instances or nodes.
Allows clients to access an instance of a database (the way SID did).
Further, in previous releases, an alias for a connect descriptor was service_name. Because SERVICE_NAME now is a parameter in the connect descriptor, service_name has been replaced with net_service_name.
Database instances register themselves with the listener when started. Prior to this release, information about the instance was required to be manually configured in the listener.ora file. Instance registration is comprised of:
This feature minimizes the system configuration since there is no need manually enter instance names in listener.ora files. Database instance registration enables dynamic load balancing and failover.
Database instance registration enables connection load balancing because of the registration that happens with remote listeners. Connection load balancing balances the number of active connections among various instances and dispatchers for the same service. This enables listeners to make their routing decisions based on how many connections each dispatcher has and on how loaded the nodes that the instances run.
The load of a dispatcher is determined by the number of connections to the dispatcher. The load of an instances takes into account the load of the node it is running.
Connection load balancing is only enabled for an MTS environment. It is not configurable by clients.
The Net8 native authentication adapter on NT has been improved in the following ways:
Backward compatibility with previous versions of Oracle is maintained, while integration with new security mechanisms in the future will be easier.
For more information about native authentication on Windows NT, refer to the Windows NT documentation "Getting Started" guides for Oracle8i and Net8.
Oracle8i also offers security to multi-tier architectures. In some systems, the middle tier is super-privileged to perform any action on behalf of any user, and the identity of the real client is not preserved through the middle tier. Middle tiers, especially Web servers or application servers, often sit on or outside a firewall, so limiting their access and auditing their actions is especially important.
Oracle8i provides the ability to preserve the real client identity through the middle tier and limit the users on whose behalf a middle tier can connect. The server can also audit actions taken by the middle tier on behalf of a particular user.
To support this feature, syntax changes have been made to the ALTER USER and AUDIT statement. For more information, see Oracle8i SQL Reference.
This section covers the Oracle8i new features and enhancements for the various program interfaces used to access data in the Oracle database. PL/SQL, the Pro*C/C++ and Pro*COBOL precompilers, and the Oracle Call Interface (OCI) are included. The new features supporting Windows NT integration are also discussed.
For more information on any of the PL/SQL features or enhancement described here, see the PL/SQL User's Guide and Reference. Additional references may be noted in the descriptions.
Autonomous PL/SQL blocks are PL/SQL blocks that have a transaction scope independent of the transaction scope of the calling PL/SQL block. They can perform operations, commit, and rollback independent of the transactions in the calling block, before returning to the calling block. Transactions within an autonomous PL/SQL block are referred to as autonomous transactions. They are not nested transactions in that they do not share resources with the calling transaction and committed changes are immediately visible to other transactions regardless of whether the calling PL/SQL block commits or rolls back. A PL/SQL stored procedure/function, local procedure/function, a package/type method, or a top-level anonymous block can be declared to be an autonomous block using the pragma specification:
The above specification can appear anywhere in the declaration section of a PL/SQL block, but at most once.
The Oracle8i autonomous transactions feature provides a means of constructing reusable application subcomponents that perform specialized operations.
The invoker-rights model, introduced in this release, allows programs to be executed with the privileges of the calling user. Prior releases used a definer-rights model wherein programs execute with the privileges of the creating user. The syntax of the CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, and CREATE TYPE statements has been altered to allow an invoker_rights_clause. If AUTHID CURRENT_USER is specified, the program becomes an invoker-rights program. If not specified, the default is AUTHID DEFINER and there is no change from past releases.
The invoker-rights model is another step toward the creation of reusable code--a single code base that can be used by multiple users to manage data stored in their schemas.
For more information, see Oracle8i Concepts.
For PL/SQL, Oracle8i provides functionality to send a collection of bind variables (bulk bind) to the SQL engine with a single SQL statement, rather than requiring the same statement to be sent multiple times, differently bound each time. This functionality is similar to that already provided with OCI and Pro*C/C++. It reduces communication overhead and can significantly improve performance.
Dynamic SQL adds power and flexibility to host languages by allowing an application to generate and submit SQL statements for execution at run time. Prior releases provided this capability through programmatic interfaces in the DBMS_SQL package. Oracle8i provides dynamic SQL processing support in PL/SQL that makes dynamic SQL processing extremely simple, efficient, and easy to use. It is seamlessly integrated with SQL, much like static SQL in PL/SQL, and its performance is comparable to that of static SQL.
In Oracle8i, PL/SQL supports three parameter passing modes: IN, IN OUT, and OUT. IN parameters are passed by reference; IN OUT parameters support copy-in and copy-out semantics; OUT parameters support copy-out semantics. Through a new syntax, using NOCOPY mode, Oracle8i allows all parameters to be passed efficiently by reference. This achieves a significant performance gain for any application, such as a data cartridge, that passes large data structures as IN OUT and OUT parameters.
Oracle 8i provides the UTL_REF package, containing procedures to support reference-based operations. Specifically, given a REF, there are procedures for selecting, locking, updating, and deleting an object.
Unlike SQL, the UTL_REF procedures enable the writing of generic type methods without knowing the object table name.
Oracle8i provides three new APIs, in the form of packages, that facilitate the tracing, debugging, or profiling (including code coverage) of PL/SQL applications.
For more informations about any of these packages, see Oracle8i Supplied Packages Reference.
This package provides a means for tracing the execution of PL/SQL programs on the server. It contains procedures to start and stop the tracing of calls to PL/SQL functions or procedures, and exceptions. Data is written to the database trace log file.
Third-party tool vendors can leverage this low-level API to provide debuggers to PL/SQL developers.
Debugging requires two database sessions. One session runs the target code, which invokes DBMS_DEBUG to cause the PL/SQL server to generate debug events. The other session, the supervising session or debugger, uses DBMS_DEBUG to communicate with and read events posted by the target session. Several debuggers are already available using this API, such as Oracle Procedure Builder.
This profiling API provides PL/SQL programs with services for collecting and persistently storing data, in database tables, that can help identify and isolate performance problems or provide code coverage information. It is possible to generate profiling data for all named library units in a single session. For this release, the data includes the total number of times each line of code is executed, the total amount of time spent executing the line, and minimum and maximum times spent on a particular execution of the line.
A simple sample suite of analysis and reporting tools is provided, and the use of database tables allows ad hoc querying. Third-party tool vendors can now provide more sophisticated analysis and reporting tools for profiling and code coverage.
Oracle8i relaxes the limit on the size of PL/SQL package bodies.
In earlier releases, users were expected to qualify functions and procedures with the RESTRICT_REFERENCES pragma, which has various options (WNDS, RNDS, WNPS, RNPS). This specifies the purity of a function or procedure if the user wishes to use the function for certain operations. This purity information, combined with additional computed information, is then used to determine whether it is safe to perform the following operations:
In Oracle8i, these rules have been relaxed to be more user-friendly.
More details about this, refer to the Oracle8i Application Developer's Guide - Fundamentals.
The Pro*C/C++ and Pro*COBOL compilers, which allow the embedding of SQL statements in source code, have been significantly enhanced for Oracle8i.
The following table lists the Oracle8i enhancements for the Pro*C/C++ Compiler.
The CALL embedded SQL statement invokes a stored procedure. It can be used instead of an embedded PL/SQL block in new applications.
A new embedded SQL interface allows the manipulation of LOBs (large objects).
A new embedded SQL interface to allows the manipulation of varray and nested table data.
ANSI Dynamic SQL
This is the implementation of ANSI dynamic SQL Method 4.
This precompiler option speeds up database access by "prefetching" values, thus cutting down the number of network round-trips.
External routines written in C can be called from PL/SQL blocks. The REGISTER CONNECT embedded SQL statement is used by the procedures.
The precompiler option, HEADER, specifies that precompiled header files are to be created and used to reduce the time and computer resources needed for developing large projects.
Calling Java from PL/SQL
Stored procedures written in Java can be called from an application.
DML Returning Clause
This clause is allowed in INSERT, DELETE, and UPDATE statements.
The universal ROWID datatype is supported. Index-organized tables use this concept.
SYSDBA/SYSOPER Privileges in CONNECT Statements
These privileges can now be set in the CONNECT statement.
CLOSE_ON_COMMIT Precompiler Option
The CLOSE_ON_COMMIT micro precompiler option allows a choice of whether or not to close all cursors when a COMMIT is executed and the macro option MODE=ANSI.
For more informations, see the Pro*C/C++ Precompiler Programmer's Guide.
The Pro*COBOL precompiler was largely rewritten for release 8.0, introducing many new features, but with an emphasis on greater compatibility with DB2. Oracle8i continues Oracle's effort to enhance the functionality of the Pro*COBOL precompiler, providing support for new Oracle8i database server features. The following table summarizes the Oracle8i enhancements.
The CALL embedded SQL statement invokes a stored procedure. It can be used instead of embedded PL/SQL block in new applications.
An embedded SQL statement interface allows LOBs (large objects) to be used in precompiler applications.
ANSI Dynamic SQL
This is the implementation of ANSI dynamic SQL Method 4.
This precompiler option speeds up database access by "prefetching" values, thus cutting down the number of network round-trips.
Calling Java from PL/SQL
Stored procedures written in Java can be called from a Pro*COBOL application.
DML Returning Clause
This clause, which can save round-trips to the database server, is now allowed in INSERT, DELETE, and UPDATE statements.
The support for universal ROWID datatype is presented. Index-organized tables use this concept.
User-Specified Runtime Contexts
COBOL subprograms can now be called by C programs. The context can be passed by a Pro*COBOL program to a C subprogram
SYSDBA/SYSOPER Privileges in CONNECT Statements
These privileges may now be set using the CONNECT statement.
Tables of Group Items
Tables of group items are now allowed as host variables in Pro*COBOL.
WHENEVER Do Call Branch
The WHENEVER directive has a DO CALL action: a subprogram is called.
Decimal-Point is Comma
The DECIMAL-POINT IS COMMA clause is supported. This permits commas to be used instead of decimal points in numeric literals.
The following divisions and their contents are now optional: IDENTIFICATION, ENVIRONMENT, DATA.
When set to NO, the NESTED precompiler option will prevent generation of the GLOBAL clause for non-nested programs.
For more informations, see the Pro*COBOL Precompiler Programmer's Guide.
The Java interfaces, JDBC and SQLJ, are discussed in the Java section of this document. See:
For information about these OCI enhancements, see the Oracle Call Interface Programmer's Guide.
Enhancements have been made to make the DML RETURNING clause more optimized for common usage patterns. Data transfer is minimized and the interface is simplified for these common cases. Specifically, OCI provides selective data transfer extensions to the use of the RETURNING clause which returns data only when it has been changed in the server. Also, there is additional OCI functionality for single row and array DML operations in which each iteration returns more than one row.
OCI now provides an error-batching mode for an array DML statement execution. In prior releases, OCI aborted if an error was signalled during an array DLM operation. Oracle8i can allow the DML to complete, but trap any erroneous rows. The erroneous rows are returned at one time after inserting/deleting all correct rows. The user can then make necessary corrections, and do another update operation. Consequently, the number of round-trips a user might have to perform has been reduced to a maximum of two.
OCI callbacks now provide a means of tracing OCI calls for debugging and performance measurements. Two types of callbacks can be registered, entry and exit, wherein additional pre or post processing can be done. They can also be used to substitute the body of the function with proprietary code to execute on a foreign data source. For licensed ISVs and internal Oracle groups, a dynamic callback registration mechanism is provided. This mechanism allows a core-compliant dynamically linked module to register callbacks at OCI environment initialization time. This is transparent to the OCI application and does not require any changes in a running application.
OCIDescribeAny was introduced in Oracle8 as a simple and uniform OCI interface to describe schema-level database objects: tables, views, sequences, synonyms, procedures, functions, packages, and types. It has been expanded to describe attributes of sub-schema objects like columns of a table or fields in a type, and to include more attributes of currently described database objects.
The publish/subscribe and system event notification capabilities of Advanced Queuing were discussed in "AQ-Based Publish/Subscribe". OCI provides a programmatic API through which an application or user can subscribe to be notified of a database or system event, and be notified asynchronously through a callback.
Oracle8i provides non-blocking capability with OCI. Essentially, it allows a user to specify or set a non-blocking polling mode for OCI call execution. This mode can either be specified for a whole application or at the individual call level. OCI non-blocking mode allows applications to process other events while an OCI call is in progress.
Microsoft Windows NT® is an increasingly popular environment for database development and deployment. Oracle8i contains several features to make it easier to develop Oracle-based applications with Microsoft products. The Oracle database server on Windows NT provides a highly available, scalable, and secure platform for application deployment. This section discusses the features supporting Windows NT integration offered in Oracle8i.
The Oracle Application Wizard (AppWizard) provides developers with a GUI tool with which to quickly and seamlessly create a Visual C++, Visual InterDev, or Visual Basic application that provides connectivity and data access to an Oracle database. A developer can use this tool to generate an Oracle database enabled application that compiles, links, and runs without writing a single line of code.
AppWizard is thoroughly integrated with the Visual Studio IDE, which is the most popular IDE for developing C++ and Visual Basic applications on the Windows NT/95 platform. It allows ISVs, VARs, and other users to easily build applications that leverage Oracle database technology in the Visual Studio IDE.
The wizard can be invoked whenever a developer decides to create a new project in Visual Studio. AppWizard guides the developer through a two-step process. First, the wizard prompts the user with questions about the database connection that is to be used: connection string, username, and password to the Oracle database. Then, the user is allowed to select the specific tables and columns from which the application will retrieve data.
Second, based on the developer's responses, AppWizard will generate a Visual Studio project and source code that provide the developer with a custom tailored application framework from which to start developing immediately. The generated code framework will consist of a mixture of Microsoft Foundation Classes (MFC) and Oracle Objects for OLE classes. The MFC code provides the basic GUI application code and the OO4O classes provide the connectivity and data access to Oracle databases.
The Oracle8i version of OO4O will enable developers who utilize COM/DCOM based development tools to have seamless access to all Oracle specific features that are presently inaccessible from other ODBC or OLE DB-based components such as ADO. Significant enhancements in this release include:
Extensive online help is available with Oracle Objects for OLE, including a Quick Tour for getting started.
Microsoft Transaction Server (MTS) is a middle-tier application server for managing distributed applications. It offers an ActiveX/DCOM based programming model to develop distributed applications and a runtime environment to deploy these applications. Oracle8i provides full, native integration with MTS. It allows a user to create distributed transactions coordinated by MTS with Oracle databases on any platform as participants.
Prior to Oracle8i, customers used Microsoft's XA solution for MTS to support transactions involving Oracle data. The transactions are coordinated by MTS but are mapped to the XA protocol. But Oracle8i now provides much better integration by supporting the OLE transaction interfaces published by Microsoft. This solution will perform much better and with less configuration.
This cartridge allows PL/SQL developers to programmatically manipulate COM/DCOM objects using the OLE automation interface (IDispatch). By allowing COM manipulation in PL/SQL stored procedures, functions, and triggers, developers have access to the thousands of pre-built and reusable COM objects that exist today. This feature enables developers to leverage COM objects that they build or buy from third-party vendors. Furthermore, it allows customers to extend the functionality of PL/SQL to suit their requirements.