Oracle7 Release 7.3

Oracle Finally Puts Its Money Where Its Mouth Is With Oracle7 Release 7.3 and the Universal Server.

With a marketing hype rivaled only by Microsoft's Windows 95 launch, the Oracle Universal Server, including Oracle7 Release 7.3, was forced onto the Oracle user community and the general IT public at large at the Oracle Developers' Conference in February 1996. The aim of the Oracle Universal Server is to combine the functionality of the world's best-selling client/server relational DBMS with complete Web, text management, messaging, and multimedia information servers. The multimedia services include ful ly integrated relational, spatial, text, audio, and video information to any user with a standard Web browser accessing a Web-enabled database. The Oracle Universal Server is targeted to give Oracle users a comprehensive platform designed to handle all o f these needs: network- centric computing, mission-critical online transaction processing (OLTP), online analytical processing (OLAP), and data warehousing applications. In this review I investigate what the Oracle Universal Server has to offer.

Relational Data Model

You define standard declarative referential, key, and column integrity constraints as part of the CREATE TABLE and ALTER TABLE statements, using an ANSI SQL-92-like syntax. For referential constraints, Oracle supports the RESTRICT (NO ACTION is the corre ct ANSI SQL-92 syntax) and CASCADE options for delete operations on the primary table, but it supports only the RESTRICT option for updates on the primary table. Oracle also supports the MATCH NONE, MATCH FULL, and MATCH PARTIAL options for composite and nullable foreign keys, which few other DBMSs do at this stage.

Alternatively, you can code your own integrity constraints using Oracle's powerful triggers. Oracle's pre-operation and pre-row triggers are an efficient way to check whether an operation will violate a referential-integrity constraint before the operati on is actually performed.

Database Objects

• The Oracle ConText option lets Oracle handle large units of unstructured text the same way it handles the standard, structured data types. The ConText option provides SQL-like interface to access the textual data, which makes it possible to ap ply the ANSI SQL-92 textual data type functions to large volumes of unstructured text. This makes it easy to extend existing applications to perform intelligent text searches and text reductions. The ConText option also provides facilities for text retri eval, classification, and management.
• The Oracle Video Server lets you store, manage, and display rich multimedia data to clients over a network. This includes full-motion, full-screen video as well as high-fidelity audio. This option includes not only the Oracle Video Server but also the Oracle Video Client and Oracle Media Net, which can deliver video streams at varied bit rates in a distributed environment.
• The Oracle Spatial Data Option manages spatial (geographic) information as an integrated function of the database. The spatial data is then stored, manipulated, and accessed in the same way as the standard, structured data types.

In Oracle, each table can be stored in a default form in a table space, as a cluster, or as a hashed cluster. In a cluster, related tables are stored together in a pre-joined form. When the cluster has a hashed structure, it performs exact-match queries well. Oracle indexes, by default, have B-tree structures when stored in the standard table spaces or when used to index clusters. When an index is used to index a hash cluster, it obviously has a hash structure. Oracle7 Release 7.3 also has bitmapped ind exes, which are better than conventional indexes for columns with a relatively small number of possible values, and are often used in OLAP-type applications.

Oracle lets you define multiple database triggers per table. The triggers can be defined to fire per operation or per affected row, before or after the operation. The triggers are coded in PL/SQL, a powerful 4GL-like language that includes a rich set of functions, user-defined data types (such as temporary tables), and cursors to process single or multiple rows at a time. The triggers for the same event fire in any order, typically in creation order. If you need to enforce a firing order, the code can b e combined in one trigger. An Oracle database trigger can have a when clause, which is used to eliminate unnecessary trigger firing. Database triggers are not stored in a compiled form in Oracle 7.2. However, in Oracle 7.3, database triggers are stored i n a compiled form, similar to that of stored procedures. This makes the implementation of Oracle triggers much more efficient. An Oracle trigger cannot query or perform a manipulation operation on a "mutating" table, where the trigger may see an inconsis tent view of the table, because there is a transaction pending on the table (the firing transaction, in this case).

An Oracle stored procedure or function is a named set of PL/SQL statements that is stored in the database as a database object. You can execute a procedure interactively using an Oracle tool, such as SQL*Plus, or call it explicitly in the code of a datab ase application, such as an Oracle Forms or embedded SQL application, or in the code of another procedure or trigger. When you create a stored procedure, Oracle compiles the procedure, stores the compiled code in memory, and stores the stored procedure i n the database. When you invoke a stored procedure, Oracle verifies the procedure's validity and then executes the procedure. If the procedure is valid and currently in memory, the PL/SQL engine simply executes the procedure's code. If the procedure is v alid and not currently in memory, the PL/SQL engine loads the compiled procedure from disk into memory and then executes it.

Queries

The SQL language used in Oracle is compliant with the ANSI SQL-92 Entry Level standard. However, like all of the other DBMSs, it includes many extensions to the language to provide additional commands and additional functionality.

Oracle supports cursors using the ANSI SQL-92 standard syntax. In addition to fetching one row at a time, you can fetch an entire array of data at a time. However, you can move forward only through the cursor.

Oracle supports left and right outer joins using the well-known Oracle outer join syntax. Left and right outer joins are specified by (+) symbols in the join condition of the where clause of the SELECT statement, not in the from clause, as specified in t he ANSI SQL-92 syntax.

Database Administration

• Database Manager starts and shuts down the database, customizes initialization parameters, and creates database aliases.
• User Manager creates user accounts and roles, grants privileges to users, and manages user passwords.
• Object Manager manages database objects such as tables, indexes, synonyms, and views, and it grants object privileges to users.
• Session Manager views and disconnects user sessions on the database.
• Database Expander expands the database to make room for additional data.
• Backup and Recovery Managers back up and recover your data.
• Password Manager changes the database system password from the database.

• Navigator is an object browser used to manage a tree structure of all of the nodes, databases, listeners, users, roles, and profiles.
• Map Window is a GUI view of the key objects; it manages and monitors subsets of the objects in the system. The subsets can be grouped by any criteria.
• Job Scheduling System schedules, runs, and automates repetitive database tasks, even on remote sites.
• Event Management System is a monitor for database and system events, with a filtering mechanism to set thresholds and an interface with the Job Scheduling System to activate corrective tasks.

For highly secure systems, Oracle7 has the necessary features to enforce the required security policies. Oracle7 enforces data confidentiality through the following mechanisms:

• User Identification and Authentication establishes and verifies a user's identity before permitting a connection to the database.
• Discretionary Access Control (DAC) restricts users' ability to perform specific operations and access specified objects, based on privileges assigned to them. Authorized users can re-grant their rights to other users according to their discret ion. Privileges can be granted per object, to a role, or across a system. A role is a user-defined group of privileges that can be granted to users or to other roles - it is a powerful mechanism for managing large numbers of users and privileges. Users c an set 70 different types of security privileges.
• Mandatory Access Control (MAC) restricts users' access to data based on its sensitivity and the user's clearance. To enforce MAC, the system must store and maintain security labels for each object and a clearance for each user.
• Accountability includes all of the actions on the system that are recorded and that can be tied to specific users. These actions are recorded in an audit trail that can be analyzed to detect security threats. Oracle7 provides auditing options per user, per database operation, per object accessed, and per system privilege.
• Database Encryption stores the database in an encoded form that can only be deciphered with the correct software and deciphering key.

Connectivity and Distribution

Replication

The Oracle7 Advanced Replication Option is also easy to manage through the GUI-based Oracle Replication Manager. In addition to managing data-level changes, it can also be used to manage schema-level changes. You can define groups of objects, such as tab les, views, and triggers, and manage their replication as a single unit by dragging and dropping it onto the icons of the other databases. The Oracle7 Advanced Replication Option provides two methods of conflict handling, namely primary site ownership an d dynamic ownership. With primary site ownership, the replicated data may only be updated at the site which "owns" it while other sites subscribe to the replicated data. With dynamic ownership, the "right" to update the replicated data may move around, b ut it may only be at one site at a time.

Internet Support

Among the Leaders