0152-0154

Previous Table of Contents Next

Page 152

NOTE

Note that in Listing 7.7, the join never actually occurred. Standard star query optimizations may have produced the Cartesian product of the rows selected from dimensions and joined this result to the fact table. This join approach will almost always be considerably slower than bitmap indexes, consuming more memory in the process.

You enable star transformation for an instance by setting the STAR_TRANSFORMATION_ENABLED initialization parameter to true. Keep in mind that bitmap indexes must be available to perform the transformation. This approach is particularly well-optimized for sparse fact tables that can be constrained optionally by a large number of dimensions. For dense fact tables with a few required dimensional constraints, the more traditional approach using an aggregate index and standard star-optimization techniques may be more appropriate.

Administration and Security Enhancements

Oracle8 introduces a number of administration and security enhancements, many of which apply to specific server options or object-oriented features. This section focuses on the major improvements related to the core server, including server-managed recovery, improved password management, and data dictionary protection.

Server-Managed Recovery

Server-managed recovery is implemented as a command-line utility similar to SQL*DBA that accesses the new backup and recovery PL/SQL package. The new Oracle8 Recovery Manager, which can be used only with Oracle8 servers, enables you to perform the following tasks :

• Create and manage scripts used to automate frequently used backup operations
• Generate a report of all backup and recovery activity
• Create recovery catalogs, which can be used to automate recovery procedures
• Perform backup and restore operations in parallel
• Configure and schedule backup operations for unattended execution

Although recovery catalogs are not required, there are several advantages to using them. Recovery catalogs, which are maintained in the database rather than in the file system, contain additional information that is not in the control files, including information to support point-in-time recovery and stored command scripts. More important, you can use a catalog to recover a database even if the control files have been damaged or are missing. Obviously, to take advantage of this feature, you should store recovery catalogs in an external database on another machine. You can register and refresh the recovery catalog destination database through the Recovery Manager interface.

Page 153

The Recovery Manager supports full and incremental backups using backup sets, as well as image copying of specific files or groups of files. You can schedule individual backup operations or the execution of stored backup sets through the Recovery Manager. All activity is logged, and you can generate a report to view the results of operations in a readable format.

NOTE

A full discussion of the Recovery Manager's capabilities is far beyond the scope of this chapter. You can use literally hundreds of commands to configure and perform backup, restore, and recovery operations. In addition to the added capabilities, the real benefit of this new utility is that it is simply the administrative interface. The actual tasks are performed by separate server-based processes, which can be run in parallel, completely unattended.

Improved Password Management

The Oracle profile has been enhanced in Version 8 to support better security and password management. You now can use the profile to perform these tasks:

• Lock an account after a specified number of failed password attempts
• Specify the number of days that an account will be locked after exceeding the maximum allowable failed logon attempts
• Specify the number of days before a password expires
• Specify the number of days that must pass (or the number of password changes that must occur) before an expired password can be reused
• Specify a required complexity as defined through a custom PL/SQL function
• Specify the number of days that an expired password can continue to be used

The following example demonstrates the use of the parameters that enforce these restrictions:

 CREATE PROFILE remote_user LIMIT    FAILED_LOGIN_ATTEMPTS         3     PASSWORD_LIFETIME          DEFAULT     PASSWORD_REUSE_MAX      UNLIMITED     PASSWORD_REUSE_TIME                 365     ACCOUNT_LOCK_TIME                 30     PASSWORD_GRACE_TIME                30     PASSWORD_VERIFY_FUNCTION  DEFAULT; 

Note that Oracle supplies defaults for these values, including a verification function.

Data Dictionary Protection

Data dictionary protection is another Oracle8 security enhancement. The ANY system privileges (SELECT ANY TABLE, for example) no longer include the SYS schema. Also, the SYS user ID now is functionally equivalent to internal. Users must belong to the DBA operating system

Page 154

group to connect to SYS, and the password provided must match the password for SYS in the password file. These new restrictions are designed to prevent unauthorized access from the network.

Oracle8 OCI

The Oracle Call Interface is at the core of all Oracle development tools, and the Oracle8 version of the OCI is almost entirely new. Backward compatibility is maintained for many of the Version 7 functions, but applications written to the Oracle7 OCI will be unable to take advantage of the new features of Oracle8.

Version 8 of the OCI is a much larger and more flexible library than Version 7. The changes and enhancements can be classified into the following categories:

• New handles (replacing the host data area and cursor data area from previous versions)
• New internal datatypes and constants
• New external variables and constants
• New functions for C datatype mappings and functions to operate on C datatypes
• New relational functions and replacements for relational functions provided in Version 7
• New support for object types, REFs, locators, and object-relational functions
• New functions for object caching and memory management
• New functions for obtaining type information on object types at runtime

As mentioned previously, backward compatibility is provided for the vast majority of relational functions. However, new versions of these functions are supplied in Version 8. Consider the following Version 7 function prototypes to parse and execute a SQL statement or PL/SQL block:

 sword  oparse(struct cda_def *cursor, text *sqlstm, sb4 sqllen, sword defflg, ub4 lngflg); sword  oexec(struct cda_def *cursor); 

Version 8 of the OCI provides these replacements:

 sword OCIStmtPrepare(OCIStmt *stmtp, OCIError *errhp, CONST text *stmt, ub4 stmt_len, ub4 language, ub4 mode); sword OCIStmtExecute (OCISvcCtx *svchp, OCIStmt *stmtp, OCIError *errhp, ub4 iters, ub4 rowoff, CONST OCISnapshot snap_in, OCISnapshot *snap_out, ub4 mode); 

These prototypes are provided only to illustrate the point that OCI8 is a completely new API rather than an extension of OCI7. It is highly recommended that OCI7 applications be fully migrated to the new API unless Oracle7 databases must be supported.

Previous Table of Contents Next