Oracle8i
Release 3 (8.1.7.0.0)


Copyright (C) Oracle Corporation 1993, 1998, 1999, 2000

Editors: Richard Powell, Paul Lane, Bridget Burke, Venkat Subramanian

This software/documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited.

If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

RESTRICTED RIGHTS LEGEND:

Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988).

If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.

The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free.

Oracle is a registered trademark, and Net8, SQL*Plus, Oracle8i, Oracle Enterprise Manager, DBA Studio, Oracle Parallel Server, and PL/SQL are trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their respective owners

All trade names referenced are the service mark, trademark, or registered trademark of the respective manufacturer.



0.0 INTRODUCTION
      0.1 PURPOSE OF THIS README
      0.2 COVER LETTER AND LICENSING
      0.3 ORACLE8i, RELEASE 3 (8.1.7) PRODUCT DOCUMENTATION
1.0 COMPATIBILITY
      1.1 SERVER MANAGER DESUPPORT
      1.2 CONNECT INTERNAL DESUPPORT
      1.3 DATE FORMAT STRINGS: NEW BEHAVIOR
      1.4 PARTITION VIEWS
      1.5 SQL.BSQ
      1.6 SHUTDOWN CHANGES
      1.7 NUMERICAL COMPUTATIONS AND ORACLE7/8 COMPATIBILITY
2.0 ROWIDs AND ORACLE7 COMPATIBILITY
3.0 DESUPPORT NOTIFICATION FOR V6 COMPATIBILITY BEHAVIOR
4.0 PL/SQL
      4.1 INTER-VERSION RPC AND DEFAULT ARGUMENTS
5.0 UTILITIES
      5.1 SQL*LOADER
      5.2 EXPORT/IMPORT
6.0 SQL EXECUTION
      6.1 PLAN STABILITY
      6.2 FUNCTION-BASED INDEXES
7.0 MIGRATION
8.0 ORACLE CALL INTERFACE (OCI)
      8.1 BUILDING OCI APPLICATIONS AND/OR EXTERNAL PROCEDURES
9.0 XA
10.0 TRANSPARENT APPLICATION FAILOVER
11.0 ADVANCED QUEUING11.1 PROPAGATION JOBS
12.0 INITIALIZATION PARAMETERS
      12.1 RECOVERY_PARALLELISM
      12.2 DEFAULT BUFFER CACHE SIZE
13.0 MEDIA MANAGEMENT SOFTWARE
      13.1 USING LEGATO STORAGE MANAGER (LSM) ON UNIX
      13.2 USING A MEDIA MANAGER ON WINDOWS-NT
14.0 ORACLE PARALLEL SERVER
      14.1 ENVIRONMENT CONSIDERATIONS FOR INTERPROCESS COMMUNICATIONS (IPC)
      14.2 ADDING AND REPLACING NOTES IN OPS
15.0 DATABASE SECURITY
      15.1 PASSWORD MANAGEMENT
      15.2 ORACLE SECURITY SERVER
16.0 LOBs
      16.1 LOB DEMOS
17.0 STRUCTURED QUERY LANGUAGE (SQL)
      17.1 USING EVENT 10408 TO AVOID COMPATIBILITY PROBLEMS WITH NEW FUNCTION NAMES AND KEYWORDS
18.0 NATIONAL LANGUAGE SUPPORT
      18.1 LOBS
      18.2 NLS DEMO DIRECTORY
      18.3 Y2000 Date FORMAT ISSUES
      18.4 UNICODE 3.0 SUPPORT
      18.5 DATABASE CHARACTER SET MIGRATION TOOL
      18.6 SIMPLIFIED AND TRADITIONAL CHINESE CONVERSIONS
      18.7 HONG KONG SUPPLEMENTARY CHARACTER SET
      18.8 NEW EBCDIC CHARACTER SETS
      18.9 IMPROVED ALTER DATABASE CHARACTER SET SUPPORT
      18.10 AL24UTFFSS CHARACTER SET DESUPPORT
      18.11 NCHAR SUPPORT
      18.12 RESTRICTED ALTER DATABASE CHARACTER SET COMMAND SUPPORT (CLOB and NCLOB)
19.0 REPLICATION
      19.1 PROCEDURES
      19.2 SSL CONNECTIONS
      19.3 ORACLE 8i REL. 3 (8.1.7) DOCUMENTATION ERRATA
20.0 64-BIT ISSUES
21.0 TYPES
22.0 DATA DICTIONARY
      22.1 DATA DICTIONARY FUTURE VIEW CHANGES
23.0 MATERIALIZED VIEWS23.1 USING EVENT 30441 TO AVOID ERRORS
      23.2 FAST REFRESH OF MATERIALIZED VIEWS
      23.3 QUERY REWRITE
      23.4 NLS PARAMETERS
24.0 ORACLE TRACE COLLECTION SERVICES
      25.1 interMEDIA TEXT README, Release 8.1.7
      25.2 interMedia DATABASE OBJECT INSTALLATION
25.0 interMEDIA, SPATIAL SERIES, VISUAL INFORMATION RETRIEVAL
26.0 GENERIC ODBC CONNECTIVITY
27.0 TIME SERIES
28.0 JAVA
29.0 CONFIGURATION ASSISTANTS
      29.1 DATABASE CONFIGURATION ASSISTANT
      29.2 ORACLE DATA MIGRATION ASSISTANT
30.0 XML PARSER
31.0 8.1.6 FIXED BUGS
32.0 8.1.7 FIXED BUGS
33.0 8.1.7 OPEN BUGS
      33.1 MISCELLANEOUS BUGS
      33.2 KNOWN ODMA BUGS


0.0 INTRODUCTION

0.1 PURPOSE OF THIS README

This README file is relevant only to the delivered Oracle8i, Release 3 (8.1.7) and its integral parts, such as SQL, PL/SQL, the Oracle Call Interface (OCI), SQL*Loader, Import/Export utilities, and so on. There may also be separate README files for Net8 and precompilers.

For information on upgrading, downgrading, and migration, see Oracle8i Migration.

This README documents any differences between the server (and its integral parts) and its documented functionality, as well as known problems and workarounds.

Operating system releases, such as UNIX, Windows NT, DEC Open VMS, and so on, often provide an operating system specific README document. Additional README files may also exist for specific Oracle products such as SQL*Forms. This README file is provided in lieu of release notes, system bulletins, or similar publications.

0.2 COVER LETTER AND LICENSING

Please read the cover letter included with your Oracle8i Release 3 (8.1.7) package.

0.3 ORACLE8i, RELEASE 3 (8.1.7) PRODUCT DOCUMENTATION

The following list of books and part numbers describes the server documentation set. Some books are available only online.

ADDENDUM

A85455 - Oracle8i Documentation Addendum

chapters: interMedia, NLS, Oracle Parallel Server, Migration, Net8, PL/SQL Packages, Recovery Manager, Server Reference, Replication, Oracle Label Security Error Messages, Standby Database, Oracle Trace

MASTER INDEX & MASTER GLOSSARY

A85457 - Oracle8i Generic Documentation Master Index
Q73066 - Oracle8i Master Glossary [HTML only]

SERVER & SQL*PLUS

A76962 - Getting to Know Oracle8i
A76956 - Oracle8i Administrator's Guide
A76965 - Oracle8i Concepts
A76992 - Oracle8i Designing and Tuning for Performance
A76999 - Oracle8i Error Messages
A86632 - Oracle8i Migration
A76966 - Oracle8i National Language Support Guide
A76961 - Oracle8i Reference
A85397 - Oracle8i SQL Reference
A85456 - Oracle8i Supplied Java Packages Reference
A76936 - Oracle8i Supplied PL/SQL Packages Reference
A76955 - Oracle8i Utilities
A77218 - Legato Storage Manager Administrator's Guide
A76993 - Oracle8i Backup and Recovery Guide
A76990 - Oracle8i Recovery Manager User's Guide and Reference
A76995 - Oracle8i Standby Database Concepts and Administration
A76994 - Oracle8i Data Warehousing Guide
A76960 - Oracle8i Distributed Database Systems
A76959 - Oracle8i Replication
A76958 - Oracle8i Replication Management API Reference
A82950 - SQL*Plus User's Guide and Reference
A82951 - SQL*Plus Quick Reference
A85448 - SQL*Plus Accessibility Guide for Windows

DIRECTORY, NETWORKING, AND SECURITY

A76933 - Net8 Administrator's Guide
A85430 - Oracle Advanced Security Administrator's Guide
A86101 - Oracle Internet Directory Administrator's Guide
A86082 - Oracle Internet Directory Application Developer's Guide

PARALLEL SERVER

A76972 - Oracle8i Parallel Server Documentation Online Roadmap [HTML only]
A76970 - Oracle8i Parallel Server Administration, Deployment, and Performance
A76934 - Oracle8i Parallel Server Setup and Configuration Guide
A76968 - Oracle8i Parallel Server Concepts

APPLICATION DEVELOPMENT

A76939 - Oracle8i Application Developer's Guide - Fundamentals
A76938 - Oracle8i Application Developer's Guide - Advanced Queuing
A76976 - Oracle8i Application Developer's Guide - Object-Relational Features
A76940 - Oracle8i Application Developer's Guide - Large Objects (LOBs)
A77004 - Oracle8i Application Developer's Guide - Large Objects (LOBs) using PL/SQL [PDF only]
A77003 - Oracle8i Application Developer's Guide - Large Objects (LOBs) using OCI [PDF only]
A77001 - Oracle8i Application Developer's Guide - Large Objects (LOBs) using Pro*C [PDF only]
A77000 - Oracle8i Application Developer's Guide - Large Objects (LOBs) using Pro*COBOL [PDF only]
A77005 - Oracle8i Application Developer's Guide - Large Objects (LOBs) using VB (OO4O) [PDF only]
A77002 - Oracle8i Application Developer's Guide - Large Objects (LOBs) using Java [PDF only]
A86030 - Oracle8i Application Developer's Guide - XML
A83730 - Oracle8i XML Reference
A76937 - Oracle8i Data Cartridge Developer's Guide
A85456 - Oracle8i Supplied Java Packages Reference
A76936 - Oracle8i Supplied PL/SQL Packages Reference
A76975 - Oracle Call Interface Programmer's Guide
A77069 - PL/SQL User's Guide and Reference
A76942 - Pro*C/C++ Precompiler Programmer's Guide
A76951 - Pro*COBOL Precompiler Programmer's Guide
A42525 - Programmer's Guide to the Oracle Precompilers
A42523 - Pro*Fortran Supplement to the Oracle Precompilers Guide
A58231 - SQL*Module for Ada Programmer's Guide

interMEDIA

A77061 - Oracle8i interMedia Text Migration
A77063 - Oracle8i interMedia Text Reference
A85336 - Oracle8i interMedia Audio, Image, and Video User's Guide and Reference
A85374 - Oracle8i interMedia Audio, Image, and Video Java Client User's Guide and Reference
A85334 - Oracle8i interMedia Locator User's Guide and Reference
A85337 - Oracle Spatial User's Guide and Reference
A67294 - Oracle8i Time Series User's Guide
A85335 - Oracle8i Visual Information Retrieval User's Guide and Reference
A85333 - Oracle8i Visual Information Retrieval Java Client User's Guide and Reference

JAVA

A83725 - Oracle8i Enterprise JavaBeans Developer's Guide and Reference
A83722 - Oracle8i CORBA Developer's Guide and Reference
A83728 - Oracle8i Java Developer's Guide
A81358 - Oracle8i Java Stored Procedures Developer's Guide
A83724 - Oracle8i JDBC Developer's Guide and Reference
A81357 - Oracle8i JPublisher User's Guide
A83723 - Oracle8i SQLJ Developer's Guide and Reference
A83727 - Oracle8i Java Tools Reference
A83726 - Oracle JavaServer Pages Developer's Guide and Reference
A83720 - Oracle8i Oracle Servlet Engine User's Guide

WEBDB [Release 2.2]

A77054 - Oracle WebDB Release Notes
A77053 - Oracle WebDB Installation Guide [PDF only]
A77075 - Oracle WebDB Tutorial [PDF only]
A77057 - Oracle WebDB Creating and Managing Components - Task Help [PDF only]
A77055 - Oracle WebDB Creating and Managing Components - Field-Level Help [PDF only]
A77056 - Oracle WebDB Creating and Managing Sites - Task Help [PDF only]
A77058 - Oracle WebDB Creating and Managing Sites - Field-Level Help [PDF only]

ORACLE ENTERPRISE MANAGER

A85248 - Oracle Enterprise Manager Administrator's Guide
A85250 - Oracle Enterprise Manager Concepts Guide
A85247 - Oracle Enterprise Manager Configuration Guide
A85245 - Oracle Enterprise Manager Messages Manual
A85251 - Oracle Intelligent Agent User's Guide
A85249 - Oracle SNMP Support Reference Guide

Oracle Integration Server

A83729 - Oracle Integration Server Overview
A75325 - Oracle Applications Interconnect User's Guide [Release 3.1.3]
A65435 - Oracle Message Broker Administration Guide [Release 2.0.1]
A85436 - Oracle Message Broker Release Notes [Release 2.0.1]
A85440 - Oracle Workflow Guide [Release 2.5.2]
A85441 - Oracle Workflow Installation Supplement [Release 2.5.2]
A85248 - Oracle Enterprise Manager Administrator's Guide
A76938 - Oracle8i Application Developer's Guide - Advanced Queuing
A83722 - Oracle8i CORBA Developer's Guide and Reference
A85456 - Oracle8i Supplied Java Packages Reference
A76936 - Oracle8i Supplied PL/SQL Packages Reference
A76960 - Oracle8i Distributed Database Systems
A76959 - Oracle8i Replication


1.0 COMPATIBILITY

This section contains recommendations on upgrading to this release, and on preparing for future changes (for example, to comply with future SQL ANSI standards). If you follow these recommendations, upgrading to newer releases of Oracle will be simplified.

1.1 SERVER MANAGER DESUPPORT

Oracle Corporation intends to replace Server Manager with SQL*Plus in a future release. The 8.1 version of SQL*Plus has been enhanced to be functionally equivalent to Server Manager. Please see Oracle8i Migration for more information.

1.2 CONNECT INTERNAL DESUPPORT

CONNECT INTERNAL is currently supported for backward compatibility only. Oracle Corporation intends to desupport it in a future release of Oracle. If you have not done so already, you should plan to migrate your applications to use other connection syntax. See the Oracle8i Administrator's Guide for further details.

1.3 DATE FORMAT STRINGS: NEW BEHAVIOR

In Oracle7, a space or punctuation character in the format string caused the corresponding character in the date string to be discarded. This caused incorrect dates to be entered into the database since alphanumeric characters were thrown out. In Oracle8i, an error occurs if an alphanumeric character is found in the date string when a punctuation character or space is found in the format string, with one exception.

Example:

    TO_CHAR(TO_DATE('0297', 'MM/YY'), 'MM/YY')
Oracle7 result: 02/97
Oracle8 result: ORA-1861

Exception:

    TO_CHAR(TO_DATE('1996-05-19 12:37:48.196000', 'YYYY-MM-DD HH24:MI:SS."00000"')
Oracle8 result: 1996-05-19 12:37:48

This exception will be removed in future releases.

Also, Oracle8 will allow matching of certain dates which produced an error in Oracle7. This matching will only occur when it can be unambiguously determined what the correct date is. However, this matching will not occur if the format model modifiers FX or FXFM are used.

The new matching rules are:

Example:

    TO_CHAR(TO_DATE('May 25, 1905', 'MM/DD/RRRR HH:MI:SS'), 'MM/DD/RRRR')
Oracle8 result: 05/25/1905

Please read the subsection "String-to-Date Conversion Rules" in the section "Format Models" of Chapter 2 in the Oracle8i SQL Reference for complete details.

1.4 PARTITION VIEWS

Oracle Corporation recommends that you use partitioned tables (available beginning in Oracle8) rather than partition views. In general, partition views should not be used and are supported for compatibility only. Oracle Corporation intends to desupport them in a future release.

1.4.1 Guidelines for Creating and Maintaining Partitioned Views

To use partition views, the PARTITION_VIEW_ENABLED parameter must be set to TRUE. DDL commands must be issued separately for each underlying table. For example, to add an index to a partition view, you must add indexes to all underlying tables. To analyze a partition view, you must analyze all underlying tables. However, you can submit operations on each partition in parallel.

Administrative operations must be performed as operations on the underlying tables of the partition view, not on the partition view itself.

For example, a split operation consists of either one or two CREATE TABLE AS SELECT operations (one if the split is 'in place'), followed by a redefining of the partition view's view text.

Every partition has its own index, so any index lookup must be done in all indexes for partitions that are not skipped.

A partition view cannot be the target of a DML statement.

Partition views do not support concatenated partitioning keys.

SQL*Loader does not support partition views.

1.5 SQL.BSQ

Oracle recommends not changing sql.bsq before installation because some modifications may jeopardize customer supportability.

1.6 SHUTDOWN CHANGES

Starting in Oracle 8.0.4, the shutdown mechanism has changed. Clients that are connected to ORACLE during a SHUTDOWN ABORT/IMMEDIATE/TRANSACTIONAL now receive one of the following errors on any subsequent SQL operations:

    ORA-03113: end-of-file on communication channel 
    ORA-12571: TNS:packet writer failure  

instead of:

    ORA-01092: ORACLE instance terminated. Disconnection forced 

1.7 NUMERICAL COMPUTATIONS AND ORACLE7/8 COMPATIBILITY

Oracle8 releases starting with 8.0.4 perform numerical computations with better accuracy than Oracle7 and Oracle version 8.0.3. Multiplication and division have their internal results rounded, not truncated as was the case in the past, to the maximum precision supported by the NUMBER type. Conversions of NUMBER to the native C FLOAT and DOUBLE datatypes are rounded to the precision of those types on the platform on which Oracle is executing. The result of these improvements is that some numerical computations will now produce more accurate through slightly different results than they did under all releases of Oracle7 and version 8.0.3 (the first Oracle8 release).


2.0 ROWIDs AND ORACLE7 COMPATIBILITY

The encoding scheme for Oracle8 rowids has been expanded. See Oracle8i Migration and Oracle8i SQL Reference for more information.


3.0 DESUPPORT NOTIFICATION FOR V6 COMPATIBILITY BEHAVIOR

With Oracle7, Oracle offered a Version 6 [V6] Compatibility flag that allowed application developers developing Oracle7 applications to emulate Oracle6 behavior. With the release of Oracle 8.0.3, users were cautioned that the Version 6 compatibility flag was being desupported effective immediately in all of the Oracle8 products, including release 8 of PL/SQL, all the Oracle precompilers, the Oracle8 Oracle Call Interface, SQL*Module, and SQL*Plus. The desupport of the V6 compatibility flag is consistent with Oracle's policy of supporting backward compatibility and behavior from one version release upgrade to another, for example, from Oracle6 to Oracle7 but not for more than one version release upgrade.

Users who do not absolutely need to maintain V6 behavior are encouraged to upgrade their Oracle7 clients to Oracle8. If you absolutely must continue to emulate V6 behavior for specific applications, you must maintain one ORACLE_HOME with an Oracle7 client for those applications. You can create a separate $ORACLE_HOME with an Oracle8 client for those applications for which you do not need V6 behavior.

Specifically, the V6 Compatibility flag emulated the following aspects of Oracle6 behavior with Oracle7:

All of these behaviors were desupported with the desupport of the V6 Compatibility Flag with Oracle8.


4.0 PL/SQL

4.1 INTER-VERSION RPC AND DEFAULT ARGUMENTS

Calls from one server to functions and procedures on a second server running a different version of Oracle may have problems when using defaulted argument values. Customer-visible symptoms may vary depending upon the source code involved. PLS-801[1411] and PLS-801[1407] are quite likely, but other symptoms, including silently wrong results, are possible. The suggested workaround is to provide an explicit value for each argument on any call through a database link if it is possible that the sending and receiving systems are running different versions of Oracle.


5.0 UTILITIES

5.1 SQL*LOADER

5.1.1 BUFFERS Keyword

The BUFFERS keyword is no longer supported for input file processing.

5.1.2 Specifying Command-Line File-Processing Options

If you specify a data file on the command line and also specify data files in the control file with INFILE, the data specified on the command line will be processed first, and the first data file specified in the control file will be ignored. All other files specified in the control file will be processed.

5.1.3 Connecting to a Database As User SYS

Starting in version 8.1.7, if you attempt to connect to a database as user SYS, you also need to specify AS SYSDBA in the connect string. For example:

     sqlldr \'SYS/password AS SYSDBA\' foo.ctl

This example shows the entire connect string enclosed in single quotation marks and backslashes. This is because the string, AS SYSDBA, contains a blank, a situation for which most operating systems require that the entire connect string be placed in quotation marks or marked as a literal by some method. Some operating systems also require that quotation marks on the command line be preceded by an escape character. In this example, backslashes are used as the escape character. If the backslashes were not present, the command-line parser that SQL*Loader uses would not understand the quotation marks and would remove them before calling SQL*Loader.

See your operating system-specific Oracle documentation for more information about special and reserved characters on your system.

5.1.4 New SIGNED and UNSIGNED Keywords for INTEGER and SMALLINT

The information given on page 5-59 in the Oracle8i Utilities manual for version 8.1.6 regarding the INTEGER and SMALLINT datatypes incorrectly identifies them as unsigned. By default, the INTEGER and SMALLINT control file datatypes are treated as signed quantities. Two new keywords, SIGNED and UNSIGNED, allow you to treat INTEGER and SMALLINT as either signed or unsigned quantities.

The UNSIGNED keyword, as shown in the following example, results in the INTEGER and SMALLINT control file datatypes being treated as unsigned quantities.

  (column1 position(1) INTEGER UNSIGNED,
   column2 INTEGER UNSIGNED,
   column3 SMALLINT UNSIGNED,
   ...
   )

Use of the SIGNED Keyword results in the default behavior.

5.1.5 Change in Behavior of SQL*Loader Input Routines

Previous versions of SQL*Loader contained a bug that could cause it to incorrectly identify record boundaries. As a result, incorrect results might be produced when loading a stream-record-format datafile that uses a character set that is not byte-unique.

A character set is byte-unique if every byte encoding is unique. Single byte character sets are always byte-unique. Varying width multibyte character sets are not byte-unique if any single byte character encoding ever appears in any byte position in any multibyte character. Examples of multibyte character sets that are not byte-unique are utf8 and ja16sjis.

For version 8.1.7, a change has been made to SQL*Loader behavior that fixes this problem. A side effect of this fix is that SQL*Loader may run slower for non-byte-unique character sets. In the common case where the record terminator is a single byte 0x0a ("\n" in ASCII), SQL*Loader will not run slower.

5.1.6 New PRESERVE Keyword with CONTINUEIF

This topic attempts to clarify material presented in the Utilities manual for version 8.1.6 (beginning on page 5-36) regarding how logical records are assembled from physical records. This topic also describes the new PRESERVE keyword for use with CONTINUEIF THIS and CONTINUEIF NEXT.

If the PRESERVE keyword is not used, the continuation field is removed from all physical records when the logical record is assembled. That is, data values are allowed to span the records with no extra characters (continuation characters) in the middle.

If the PRESERVE keyword is used, the continuation field is kept in all physical records when the logical record is assembled.

The following examples show the use of CONTINUEIF THIS and CONTINUEIF NEXT, with and without the PRESERVE keyword.

Example 1-1 CONTINUEIF THIS without the PRESERVE Keyword

Assume that you have physical records 14 characters long and that a period represents a space:

    %%aaaaaaaa....
    %%bbbbbbbb....
    ..cccccccc....
    %%dddddddddd..
    %%eeeeeeeeee..
    ..ffffffffff..

In this example, the CONTINUEIF THIS clause does not use the PRESERVE keyword:

    CONTINUEIF THIS (1:2) = '%%'

Therefore, the logical records are assembled as follows:

    aaaaaaaa....bbbbbbbb....cccccccc....
    dddddddddd..eeeeeeeeee..ffffffffff..

Note that columns 1 and 2 (for example, %% in physical record 1) are removed from the physical records when the logical records are assembled.

Example 1-2 CONTINUEIF THIS with the PRESERVE Keyword

Assume that you have the same physical records as in Example 1-1.

In this example, the CONTINUEIF THIS clause uses the PRESERVE keyword:

    CONTINUEIF THIS PRESERVE (1:2) = '%%'

Therefore, the logical records are assembled as follows:

    %%aaaaaaaa....%%bbbbbbbb......cccccccc....
    %%dddddddddd..%%eeeeeeeeee....ffffffffff..

Note that columns 1 and 2 are not removed from the physical records when the logical records are assembled.

Example 1-3 CONTINUEIF NEXT without the PRESERVE Keyword

Assume that you have physical records 14 characters long and that a period represents a space:

    ..aaaaaaaa....
    %%bbbbbbbb....
    %%cccccccc....
    ..dddddddddd..
    %%eeeeeeeeee..
    %%ffffffffff..

In this example, the CONTINUEIF NEXT clause does not use the PRESERVE keyword:

    CONTINUEIF NEXT (1:2) = '%%'

Therefore, the logical records are assembled as follows (the same results as for Example 1-1).

    aaaaaaaa....bbbbbbbb....cccccccc....
    dddddddddd..eeeeeeeeee..ffffffffff..
Example 1-4 CONTINUEIF NEXT with the PRESERVE Keyword

Assume that you have the same physical records as in Example 1-3.

In this example, the CONTINUEIF NEXT clause uses the PRESERVE keyword:

    CONTINUEIF NEXT PRESERVE (1:2) = '%%'

Therefore, the logical records are assembled as follows:

    ..aaaaaaaa....%%bbbbbbbb....%%cccccccc....
    ..dddddddddd..%%eeeeeeeeee..%%ffffffffff..

5.1.7 SQL*Loader Documentation Errata

This section contains additions, deletions, and corrections to the SQL*Loader chapters as they appeared in the Utilities manual for Oracle8i version 8.1.6.

5.1.7.1 Format of BAD and DISCARD Files

The Utilities manual for version 8.1.6 incorrectly states, in several places, that the BAD file and DISCARD file have the same format as the input datafile.

SQL*Loader does not always write records to the BAD or DISCARD files using the same record format of the input files. Exactly what gets written is specific to the system where SQL*Loader is running. For example, for the different record formats supported on Sun Solaris, SQL*Loader behaves as follows:

5.1.7.2 Default Filename for the Bad File

The Utilities manual for version 8.1.6 contains the following incorrect statement on page 6-3 regarding the BAD keyword used to generate a bad file:

"If a filename is not specified, the name of the control file is used by default with the .BAD extension."

The correct information is that the name of the datafile (not the control file) is used by default, with an extension of .BAD.

5.1.7.3 Loading Date Fields That Contain Only Whitespace

The text on page 5-66 regarding the DATE field and on page 5-82 regarding loading all-blank fields contains outdated information. As of version 8.1.7, DATE fields that contain only whitespace are loaded as NULL fields and, therefore, no longer cause an error.

5.1.7.4 Behavior When Both Position and Delimiters Are Specified for a Field

The Oracle8i Utilities manual for version 8.1.6 contains the following incorrect paragraph on page 5-84.

"If a predetermined size is specified for a delimited field, and the delimiter is not found within the boundaries indicated by the size specification, then an error is generated. For example, if you specify:

    loc POSITION(19:31) CHAR TERMINATED BY "," 

and no comma is found between positions 19 and 31 of the input record, then the record is rejected. If a comma is found, then it delimits the field."

The correct information is that if POSITION and terminators or enclosure strings are specified for the same field, only POSITION has any effect. The terminator or enclosure strings are ignored.

5.1.7.5 Understanding the Use of WHEN, NULLIF, and DEFAULTIF Clauses

This topic attempts to clarify how SQL*Loader uses the WHEN, NULLIF, and DEFAULTIF clauses. This information supersedes information given in the Oracle8i Utilities manual for version 8.1.6 in the sections "Setting a Column to Null or Zero" on page 5-80 and "Choosing Which Records to Load" on page 5-41.

The following information applies to scalar fields. For nonscalar fields (column objects, LOBs, and collections), the WHEN, NULLIF, and DEFAULTIF clauses are processed differently because nonscalar fields are more complex. The results of a WHEN, NULLIF, or DEFAULTIF clause can be different depending on whether the clause specifies a field name or a position.

If the WHEN, NULLIF, or DEFAULTIF clause specifies a field name, SQL*Loader compares the clause to the evaluated value of the field. The evaluated value takes trimmed whitespace into consideration. See the information about trimming blanks and tabs in the Oracle8i Utilities manual for a discussion of whitespace trimming.

If the WHEN, NULLIF, or DEFAULTIF clause specifies a position, SQL*Loader compares the clause to the original logical record in the datafile. No whitespace trimming is done on the logical record in that case.

Different results are more likely if the field has whitespace that is trimmed or if the WHEN, NULLIF, or DEFAULTIF clause contains blanks or tabs or uses the BLANKS keyword. If you require the same results for a field specified by name and for the same field specified by position, use the PRESERVE BLANKS keyword. The PRESERVE BLANKS keyword instructs SQL*Loader not to trim whitespace when it evaluates the values of the fields.

The results of a WHEN, NULLIF, or DEFAULTIF clause are also affected by the order in which SQL*Loader operates, as described in the following steps. SQL*Loader performs these steps in order, but it does not always perform all of them. Once a field is set, any remaining steps in the process are ignored. For example, if the field is set in step 5,

  1. SQL*Loader does not move on to step 6.

  2. SQL*Loader evaluates the value of each field for the input record and trims any whitespace that should be trimmed (according to existing guidelines for trimming blanks and tabs).

    SQL*Loader evaluates any WHEN clauses for the table for the row.
  3. If the record satisfies the WHEN clauses for the table or there are no WHEN clauses specified, SQL*Loader checks each field for a NULLIF clause.

  4. If a NULLIF clause exists, SQL*Loader evaluates it.

  5. If the NULLIF clause is satisfied, SQL*Loader sets the field to null.

  6. If the NULLIF clause is not satisfied or if there is no NULLIF clause, SQL*Loader checks the length of the field from field evaluation. If the field has a length of 0 from field evaluation (for example, it was a null field, or whitespace trimming resulted in a null field), SQL*Loader sets the field to null. In this case, any DEFAULTIF clause specified for the field is not evaluated.

  7. If any specified NULLIF clause is false or there is no NULLIF clause, and if the field does not have a length of 0 from field evaluation, SQL*Loader checks the field for a DEFAULTIF clause.

  8. If a DEFAULTIF clause exists, SQL*Loader evaluates it.

  9. If the DEFAULTIF clause is satisfied, then the field is set to 0 if the field in the datafile is a numeric field. It is set to NULL if the field is not a numeric field. The following fields are numeric fields and will be set to 0 if they satisfy the DEFAULTIF clause:

    If the DEFAULTIF clause is not satisfied or if there is no DEFAULTIF clause, SQL*Loader sets the field with the evaluated value from step 1.

The order in which SQL*Loader operates could cause results that you do not expect. For example, the DEFAULTIF clause may look like it is setting a numeric field to NULL rather than to 0.

The following examples clarify the results for different situations. In the examples, a blank or space is indicated with a period (.). Assume that col1 and col2 are VARCHAR2(5) columns in the database.

Example 1-5 DEFAULTIF Clause Is Not Evaluated

The control file specifies:

    (col1 position (1:5),
     col2 position (6:8) char) integer external defaultif col1 = 'aname')

The datafile contains:

    aname...

Col1 for the row evaluates to 'aname'. Col2 evaluates to NULL with a length of 0 (it is "..." but the trailing blanks are trimmed for a positional field).

When SQL*Loader determines the final loaded value for col2, it finds no WHEN clause and no NULLIF clause. It then checks the length of the field, which is 0 from field evaluation. Therefore, SQL*Loader sets the final value for col2 to NULL. The DEFAULTIF clause is not evaluated, and the row is loaded as 'aname' for col1 and NULL for col2.

Example 1-6 DEFAULTIF Clause Is Evaluated

The control file specifies:

    .
    .
    .
    PRESERVE BLANKS
    .
    .
    .
    (col1 position (1:5),
     col2 position (6:8) integer external defaultif col1 = 'aname'

The datafile contains:

    aname...

Col1 for the row again evaluates to 'aname'. Col2 evaluates to '...' because trailing blanks are not trimmed when PRESERVE BLANKS is specified.

When SQL*Loader determines the final loaded value for col2, it finds no WHEN clause and no NULLIF clause. It then checks the length of the field from field evaluation, which is 3, not 0.

Then SQL*Loader evaluates the DEFAULTIF clause, which evaluates to TRUE because col1 is 'aname', which is the same as 'aname'.

Because col2 is a numeric field, SQL*Loader sets the final value for col2 to '0'. The row is loaded as 'aname' for col1 and as '0' for col2.

Example 1-7 DEFAULTIF Clause Specifies a Position

The control file specifies:

    (col1 position (1:5),
     col2 position (6:8) integer external defaultif (1:5) = BLANKS)

The datafile contains:

    .....123

Col1 for the row evaluates to NULL with a length of 0 (it is '.....', but the trailing blanks are trimmed). Col2 evaluates to '123'.

When SQL*Loader sets the final loaded value for col2, it finds no WHEN clause and no NULLIF clause. It then checks the length of the field from field evaluation, which is 3, not 0.

Then SQL*Loader evaluates the DEFAULTIF clause. It compares (1:5) which is '.....' to BLANKS, which evaluates to TRUE. Therefore, because col2 is a numeric field (integer external is numeric), SQL*Loader sets the final value for col2 to '0'. The row is loaded as NULL for col1 and '0' for col2.

Example 1-8 DEFAULTIF Clause Specifies a Field Name

The control file specifies:

    (col1 position (1:5),
     col2 position (6:8) integer external defaultif col1 = BLANKS)

The datafile contains:

    .....123

Col1 for the row evaluates to NULL with a length of 0 (it is '.....', but the trailing blanks are trimmed). Col2 evaluates to '123'.

When SQL*Loader determines the final value for col2, it finds no WHEN clause and no NULLIF clause. It then checks the length of the field from field evaluation, which is 3, not 0.

Then SQL*Loader evaluates the DEFAULTIF clause. As part of the evaluation, it checks to see that col1 is NULL from field evaluation. It is NULL, so the DEFAULTIF clause evaluates to FALSE. Therefore, SQL*Loader sets the final value for col2 to '123', its original value from field evaluation. The row is loaded as NULL for col1 and '123' for col2.

5.2 EXPORT/IMPORT

5.2.1 Triggers

A full Export no longer exports triggers owned by schema SYS. Users who have defined SYS triggers must manually re-create them either before or after the full Import to the target database. It is suggested that they be re-created after the Import (just as user triggers are re-created last) in case they define actions that would slow down or otherwise impede the progress of Import.

This change was made to accommodate JAVAVM, which creates triggers in schema SYS as part of the Java "definition" of the database. Such triggers are expected to exist in the target Java database prior to Import, and Imports for user SYSTEM do not in general have sufficient privileges to re-create the triggers in the SYS schema.

5.2.2 Features Not Yet Supported

The Export and Import utilities do not provide transportable tablespace support for:

5.2.3 Incremental, Cumulative, and Complete Features No Longer Supported

Incremental, cumulative, and complete Exports are obsolete features. They are no longer documented, nor is their use supported by Oracle Corporation. For database backups, you should use the Oracle Backup and Recovery Manager. See the Oracle8i Backup and Recovery Guide for more information.

5.2.4 Connecting to a Database As User SYS

Starting in version 8.1.7, if you attempt to connect to a database as user SYS, you also need to specify AS SYSDBA in the connect string. For example:

    exp \'SYS/password AS SYSDBA\'

This example shows the entire connect string enclosed in single quotation marks and backslashes. This is because the string, AS SYSDBA, contains a blank, a situation for which most operating systems require that the entire connect string be placed in quotation marks or marked as a literal by some method. Some operating systems also require that quotation marks on the command line be preceded by an escape character. In this example, backslashes are used as the escape character. If the backslashes were not present, the command-line parser that Export and Import use would not understand the quotation marks and would remove them before calling Export or Import.

See your operating system-specific Oracle documentation for more information about special and reserved characters on your system.

5.2.5 Processing Restrictions

The following restrictions apply when you process data with the Export and Import utilities:

5.2.6 Export/Import Documentation Errata

This section contains additions, deletions, and corrections to the Export and Import chapters as they appeared in the Oracle8i Utilities manual for Oracle8i version 8.1.6.

5.2.6.1 Preserving Case Sensitivity for Table Names

Statements on pages 1-26 and 2-28 of the Oracle8i Utilities manual for version 8.1.6 indicate that case sensitivity for table names can be preserved by enclosing the name in quotation marks.

It should be further noted that some operating systems require that quotation marks on the command line be preceded by an escape character. The following are examples of how case sensitivity can be preserved in the different Export and Import modes.


6.0 SQL EXECUTION

6.1 PLAN STABILITY

6.1.1 Default System Tablespace

The default system tablespace can become exhausted if the CREATE_STORED_OUTLINES parameter is enabled and the running application has an abundance of literal SQL statements. If this happens, use the OUTLN_PKG.DROP_UNUSED procedure to remove those literal SQL outlines.

6.1.2 OR-Expansion

For execution plans that involve OR-expansion, you should avoid using stored outlines if possible. This recommendation is due to the nature of stored outlines, which use hints to influence the execution plan, and to the nature of OR-expansion, which is represented internally through a set of OR chains, each of which represents a distinct join order. Hints are only useful for influencing a single join order, as there is no way to target a specific OR chain. Therefore an outline's hints are applied to the first OR chain represented internally. The net effect is that these hints simply get propagated across the remaining OR chains by the optimizer, often leading to suboptimal execution plans that differ from the originally saved plans.

Workaround:

Stored outlines that involve OR-expansion can be identified by querying the USER_OUTLINE_HINTS view for hint text containing USE_CONCAT. Issue the following query:

    SELECT NAME, HINT FROM USER_OUTLINE_HINTS WHERE HINT LIKE 'USE_CONCAT%';

Any outline containing this hint should either be dropped using the DROP OUTLINE command or moved to an unused category with the following command:

    ALTER OUTLINE <outline-name> CHANGE CATEGORY TO <unused-category-name>;

6.2 FUNCTION-BASED INDEXES

6.2.1 Turning Off Descending Indexes

Before release 8.1, index columns were allowed to be marked DESC during index creation, for example:

    CREATE INDEX (a1, a2 ASC, a3 DESC);

However, DESC was always ignored. As of Release 8.1 such index columns are actually in descending order. As of version 8.1.6, you can revert to the old behavior (ignoring DESC during CREATE INDEX) by issuing:

    ALTER SYSTEM SET  "_ignore_desc_in_index" = true;

or by setting _ignore_desc_in_index to TRUE in the initialization parameter file. For any existing indexes whose columns are already descending (query the DESCEND column of DBA_IND_COLUMNS to check), you must drop and re-create such columns if you do not want them to be descending.

On some platforms, the parameter "_ignore_desc_in_index" is not in the initial version of 8.1.6, and an event (10612) is there instead. Setting the event to a nonzero level turns off DESC. The event will be replaced by the underscore parameter on all platforms in all releases except the initial one. Contact Oracle Support for details of setting events on an instance.


7.0 MIGRATION

See the Oracle 8i Migration guide (Release 8.1.7) on the 8.1.7 documentation CD for migration issues.


8.0 ORACLE CALL INTERFACE (OCI)

8.1 BUILDING OCI APPLICATIONS AND/OR EXTERNAL PROCEDURES

The $ORACLE_HOME/rdbms/demo directory contains not only demos but a Makefile that must be used as a template on how to build your own OCI applications and/or external procedure. Development of new makefiles to build an OCI application and/or an external procedure should consist of the customization of the Makefile provided by adding your own macros to the link line. However, Oracle requires that you keep the macros provided in the demo Makefile, as it will result in easier maintenance of your own Makefiles.


9.0 XA

When building a TP-monitor XA application, ensure that the TP-monitors libraries (that define the symbols ax_reg and ax_unreg) are placed in the link line before Oracle's client shared library. If your platform does not support shared libraries or if your linker is not sensitive to ordering of libraries in the link line, use Oracle's non-shared client library. These link restrictions are applicable only when using XA's dynamic registration (Oracle XA switch xaoswd).

Oracle8 does not support 7.1.6 XA calls (although it does support 7.3 XA calls). Thus, 7.1.6 XA calls need to relink the tuxedo applications with Oracle8 XA libraries.


10.0 TRANSPARENT APPLICATION FAILOVER

Select Failover is implemented by transparently reexecuting the SELECT statement and then bringing the cursor up to the same point as it was before the failure. Thus, if the original query took an hour, failing over the query will also take about an hour. Also, Select Failover only occurs after Session Failover has occurred. Thus, if a client is doing a parallel query and a slave dies, the query will not be transparently restarted.


11.0 ADVANCED QUEUING

11.1 PROPAGATION JOBS

Propagation jobs are owned by SYS, but the propagation occurs in the security context of the queue table owner. Previously propagation jobs were owned by the user scheduling propagation, and propagation occurred in the security context of the user setting up the propagation schedule. The queue table owner must be granted EXECUTE privileges on the DBMS_AQADM package. Otherwise, the Oracle snapshot processes will not propagate and generate trace files with the error identifier SYS.DBMS_AQADM not defined. Private database links owned by the queue table owner can be used for propagation. The user name specified in the connection string must have EXECUTE access on the DBMS_AQ and DBMS_AQADM packages on the remote database.


12.0 INITIALIZATION PARAMETERS

12.1 RECOVERY_PARALLELISM

The book Oracle8i Designing and Tuning for Performance (A76992-01) incorrectly states that the RECOVERY_PARALLELISM initialization parameter applies to media recovery. This is incorrect. The RECOVERY_PARALLELISM initialization parameter specifies the number of concurrent recovery processes for instance or crash recovery only. Media recovery is not affected by this parameter.

12.2 DEFAULT BUFFER CACHE SIZE

The default buffer cache size in version 8.0.x is 50 buffers. In 8.1.x, the default buffer cache size is calculated to be as many buffers as will fit in 48MB.

For example, if the database block size is 2K (the default value for DB_BLOCK_SIZE), the default value of DB_BLOCK_BUFFERS is 24576.

Oracle recommends you explicitly set DB_BLOCK_BUFFERS to the required size.


13.0 MEDIA MANAGEMENT SOFTWARE

13.1 USING LEGATO STORAGE MANAGER (LSM) ON UNIX

For details about LSM, see READMELSM.doc, which is the separate README for the Legato Storage Manager.

13.2 USING A MEDIA MANAGER ON WINDOWS-NT

Oracle on Windows-NT accesses the media management library via a DLL. The media manager's install process must install the correct DLL for its interface library. It is not necessary to relink the Oracle executable.


14.0 ORACLE PARALLEL SERVER

(See chapter in the 8.1.7 Oracle8i Documentation Addendum on this topic.)

14.1 ENVIRONMENT CONSIDERATIONS FOR INTERPROCESS COMMUNICATIONS (IPC)

The Oracle server requires the shared library libskgxp.so to reside in $ORACLE_HOME/lib directory where Oracle was installed. Moving an Oracle binary from one ORACLE_HOME to another will result in dynamic link time failures.

14.2 ADDING AND REPLACING NOTES IN OPS

Refer to the Oracle Website, http://www.oracle.com/database/options/parallel, for procedures on adding and replacing nodes in Oracle Parallel Server clusters. These procedures are valid for Oracle releases 8.1.x on both UNIX and Microsoft Windows NT platforms.


15.0 DATABASE SECURITY

15.1 PASSWORD MANAGEMENT

Some of the Password Management features are not supported by Server Manager line mode (svrmgrl).

15.2 ORACLE SECURITY SERVER

Version 8.1.7 does not include support for Oracle Security Server. The feature set supplied by Oracle Security Server, with the exception of X.509 certificate generation, has been replaced by the enterprise user management feature of Oracle Advanced Security.


16.0 LOBs

16.1 LOB DEMOS

Current LOB demos work for non-multibyte database character sets only.


17.0 STRUCTURED QUERY LANGUAGE (SQL)

17.1 USING EVENT 10408 TO AVOID COMPATIBILITY PROBLEMS WITH NEW FUNCTION NAMES AND KEYWORDS

Oracle 8.1.6 introduced a number of new function names and keywords. If your applications encounter naming compatibility problems as a result of these new terms, you can set event 10408 to block out the new function names and keywords.

Event 10408 is set in the initialization parameter file and is disabled by default. Enable it as follows:

    event = "10408 trace name context forever, level 1"

When event 10408 is enabled, it blocks the keywords OVER and CASE, and the built-in SQL functions FIRST_VALUE, LAST_VALUE, LAG, LEAD, RANK, DENSE_RANK, CUME_DIST, NTILE, RATIO_TO_REPORT, ROW_NUMBER, COVAR_POP, COVAR_SAMP, CORR, REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY, VAR_POP, STDDEV_POP, VAR_SAMP, STDDEV_SAMP, and PERCENT_RANK.


18.0 NATIONAL LANGUAGE SUPPORT

(See chapter in the 8.1.7 Oracle8i Documentation Addendum on this topic.)

18.1 LOBS

Current LOB demos work for non-multibyte database character sets only.

18.2 NLS DEMO DIRECTORY

The location of NLS demo files has changed from:

    $ORACLE_HOME/nlsrtl/demo

to

    $ORACLE_HOME/ocommon/nls/demo

The demo directory contains the following sample files for character set customization as documented in Appendix B of the Oracle8i National Language Support Guide:

    $ORACLE_HOME/ocommon/nls/demo/lx0boot.nlt
    $ORACLE_HOME/ocommon/nls/demo/lx22711.nlt

18.3 Y2000 Date FORMAT ISSUES

The default date format for all territories was changed in 8.1.6 from "YY" to "RR" and from "YYYY" to "RRRR". This update will provide better support for Y2K issues. It affects only date conversions from character strings into the DATE datatype.

For detailed information on the behavior of the new default settings, please refer to the section "Date Format Models" in the Oracle8i SQL Reference.

If you wish to revert to the pre-8.1.6 behavior, you can set your date format back to "YY" or "YYYY" by specifying NLS_LANG and NLS_DATE_FORMAT in the client environment.

18.4 UNICODE 3.0 SUPPORT

Unicode 3.0 is now supported in the Oracle character sets UTF8 and UTFE. It is synchronized with ISO/IEC 10646-1 second edition, and includes an additional 10,307 code points, updates on the general category and casing features. Other Unicode 3.0 features will be provided in future releases of the Database.

18.5 DATABASE CHARACTER SET MIGRATION TOOL

A new NLS utility called the Character Set Scanner (csscan) is shipped with Oracle 8.1.7. This utility is created at install time similar to the Import and Export tools under the $ORACLE_HOME/bin directory. This tool allows the users to evaluate the impact of migrating an existing database to a new character set. The tool scans the data in the database

including the data dictionary and provides an analysis of the current data, highlighting data which will be converted when migrated to the new database character set.

Please refer to the Character Set Scanner documentation in the addendum for more information.

18.6 SIMPLIFIED AND TRADITIONAL CHINESE CONVERSIONS

This feature offers support for Simplified and Traditional Chinese conversions, by enabling you to view Simplified Chinese data on Traditional Chinese clients and Traditional Chinese data on Simplified Chinese clients regardless of the type of Chinese data (Traditional or Simplified) being stored in the database. This is performed by mapping individual Chinese characters to their counter parts in Traditional and Simplified forms. However, transformation mappings are not possible/available for all Chinese characters, hence replacement characters may still exist for some cases.

In prior releases, without this mapping, Simplified and Traditional forms of Chinese characters that have separate encoding in Unicode will result in a mapping to the '?' character. With this new conversion, loss of information is minimized.

Please note that the conversions are done on aper-character basis, context may be lost during the character transformations. For example, one Simplified Chinese character may correspond to two or more Traditional Chinese characters. Normally only one of these is the correct one depending on the context. Oracle provides one to one mapping from one Simplified Chinese character to one Traditional Chinese character and vice versa only , so in some cases the meaning of the words could be nonsensical.

18.7 HONG KONG SUPPLEMENTARY CHARACTER SET

A new database character set ZHT16HKSCS in 8.1.7 supports the Hong Kong Supplementary Character set (HKSCS), ZHT16HKSCS is compatible with Microsoft's HKSCS implementation on Traditional Chinese Windows. For more information on the HKSCS standard, please refer to http://www.digital21.gov.hk/eng/hkscs/index.html

18.8 NEW EBCDIC CHARACTER SETS

The 8.1.7 release introduces 4 new EBCDIC character sets. Russian Cyrillic - CL8EBCDIC1025R and Greek - EL8EBCDIC875R, they are the replacements for CL8EBCDIC1025S and EL8EBCDIC875S character sets with the correction of two character assignments. In general, the new replacement character sets should be used. The existing character sets CL8EBCDIC1025S and EL8EBCDIC875S are supported for compatibility only. The remaining two new character sets are Siemens West European-WE8BS2000E, which is based on the ISO8859P15 codepoint definition with EURO support added and Siemens East European -EE8BS2000.

18.9 IMPROVED ALTER DATABASE CHARACTER SET SUPPORT

In prior releases, the ALTER DATABASE CHARACTER SET command supports the upgrade of the character set US7ASCII to a strict ASCII superset only. In 8.1.7, this has been expanded to cover all subset and superset character sets. For a listing of subset and superset character sets, please refer to the Reference Material section of the document addendum.

18.10 AL24UTFFSS CHARACTER SET DESUPPORT

Oracle Corporation intends to remove support for the Character Set AL24UTFFSS as the database character set in the next major release. AL24UTFFSS was introduced with Oracle7 as the Unicode character set supporting UTF-8 encoding scheme based on the Unicode standard 1.1, which is now obsolete. In Oracle8 and Oracle8i, we have the database Character set UTF8, which includes the Unicode enhancements based on the latest Unicode standard 3.0.

Oracle Corporation recommends customers use UTF8 as the Unicode character set for Oracle8 and Oracle8i. The migration path for AL24UTFFSS is to upgrade to UTF8. As with all migrations to a new database character set, Oracle recommends customers use the Character Set Scanner (csscan) for data analysis before attempting to migrate your existing database character set to UTF8.

18.11 NCHAR SUPPORT

The Oracle8i Server introduced in release 8.0 a NATIONAL CHARACTER (NCHAR) datatype that allows for a second, alternate character set in addition to the original database character set. NCHAR supports a number of special, fixed-width Asian character sets that were introduced to provide for higher performance processing of Asian character data. Examples of fixed-width character sets are:

Starting with the next major release of the Oracle8i Server, the NCHAR, NVARCHAR, and NCLOB data types will be limited to the Unicode character set encoding only. Any other Oracle8i Server character sets that are currently available under the NCHAR datatype, including Asian character sets (e.g., JA16SJISFIXED), will no longer be supported. The migration path for existing NCHAR, NVARCHAR, and NCLOB columns will be via export and import.

18.12 RESTRICTED ALTER DATABASE CHARACTER SET COMMAND SUPPORT (CLOB and NCLOB)

In Oracle 8.1.5, 8.1.6, and 8.1.7 , CLOB and NCLOB datatypes are always stored as UCS-2 (2-byte fixed-width Unicode) for multibyte database character sets. For single byte database character sets, CLOBs and NCLOBs are stored as the database character set and the national character set respectively. If the database or national character set is migrated from single byte to multiple byte via the ALTER DATABASE CHARACTER SET or ALTER DATABASE NATIONAL CHARACTER SET command, the CLOB or NCLOB columns will remain in the original database character set encoding, hence introducing data inconsistency in the CLOB columns.

The migration path for CLOB and NCLOB columns will be first export and drop the tables containing CLOB and NCLOB columns, then ALTER DATABASE CHARACTER SET and/or ALTER DATABASE NATIONAL CHARACTER SET and import.


19.0 REPLICATION

(See chapter in the 8.1.7 Oracle8i Documentation Addendum on this topic.)

19.1 PROCEDURES

Restriction: Do not use the DBMS_SNAPSHOT.REFRESH_ALL_MVIEWS and DBMS_SNAPSHOT.REFRESH_DEPENDENT procedures to refresh snapshots used in a replication environment.

Workaround: Use the DBMS_REFRESH.REFRESH or the DBMS_SNAPSHOT.REFRESH procedures to refresh your snapshots in a replication environment.

19.2 SSL CONNECTIONS

SSL connections with Java RepAPI are not supported in version 8.1.7.

19.3 ORACLE 8i REL. 3 (8.1.7) DOCUMENTATION ERRATA

The section on page 10-4 in the version 8.1.7 Oracle8i Documentation Addendum is incorrect. This section is titled "Reduced Quiesce in Single Master Environments with No Updatable Snapshots".

The following section completely replaces the section in the addendum:

Reduced Quiesce In Single Master Environments Not Using Minimum Communication

In a single master environment, if all of the updatable snapshots do not use minimum communication in the detection of conflicts, then quiesce is no longer required for the operations listed in the following table. This table also lists the procedure in the DBMS_REPCAT package that corresponds to each operation. If at least one updatable snapshot uses minimum communication to detect conflicts, make sure you:

  1. Propagate all of the deferred transactions at the databases containing updatable snapshots using minimum communication before you perform the administration operations to the master group..

  2. Do not allow any database manipulation language (DML) changes on the updatable snapshots that use minimum communication until you have finished the administration operation on the master site and regenerated replication support for the snapshot.

See Also: "Minimum Communication" on page 5-29 of the version 8.1.6 Oracle8i Replication book (A76959-01)

Use the respective DBMS_REPCAT procedure to do the following:


20.0 64-BIT ISSUES

Oracle now provides support for 64-bit systems. This support eliminates the 2GB addressing limitation of 32-bit releases. Therefore, SGAs (system global areas) and PGAs (program global areas) are limited only by the physical memory on the computer system. 64-bit addressing can improve performance by allowing very large buffer caches to be configured, thereby reducing I/Os. Further, by eliminating the limit on the maximum size of the shared pool, 64-bit addressing can allow more users to be logged into Oracle.

Check with your Oracle sales representative to determine availability of release CDs for your platform for 64-bit systems.


21.0 TYPES

Type specifications and their bodies can be kept in shared memory, so that they will not be aged out with the normal LRU mechanism. You must have EXECUTE privilege on a type to be able to keep it pinned in shared memory. An attempt to keep a type TY whose body does not exist will result in only keeping the type specification TY. Once the type body for TY is created, you must keep the type again if you wish to keep the type body for TY also pinned in shared memory. The following example demonstrates how a type TY in u1's schema can be kept pinned in shared memory by user u2 (u2 is assumed to have EXECUTE privilege on u1.TY):

begin 
  sys.dbms_shared_pool.keep('u1.TY', 'T'); -- the flag can be either 'T' or 't' 
end; 


22.0 DATA DICTIONARY

22.1 DATA DICTIONARY FUTURE VIEW CHANGES

The following V$ tables may change in a future release of Oracle:


23.0 MATERIALIZED VIEWS

23.1 USING EVENT 30441 TO AVOID ERRORS

By default, fast refresh in a data warehousing environment fails if both direct-path load and DML insert operations are performed. You can avoid this error by enabling event 30441, which supports fast refresh of the direct-path load portion while ignoring the DML operations. The fast refresh is based only on the direct-loaded rows.

Event 30441 is a session event that is disabled by default. To enable it, issue the following statement:

ALTER SESSION SET EVENTS '30441 trace name context forever, level 1';

Disable the event as follows:

ALTER SESSION SET EVENTS = '30441 trace name context off';

23.2 FAST REFRESH OF MATERIALIZED VIEWS

If you specify REFRESH FAST for a single-table aggregate materialized view, you must have created a materialized view log for the underlying table, or the command will fail. The workaround is to specify REFRESH FORCE, which will still perform a fast (incremental) refresh whenever possible.

23.3 QUERY REWRITE

If the CURSOR_SHARING parameter is set to FORCE, some rewrites that were text match based in 8.1.5 may no longer be possible from 8.1.6. This is because literals in the query are replaced by bind variables, so the SQL text of the query no longer matches that of the materialized view. To avoid this behavior, set CURSOR_SHARING to EXACT (which is the default from 8.1.6). Doing so will prevent literal replacement when using query rewrite.

23.4 NLS PARAMETERS

Users should not change the default NLS parameters when using materialized views that contain either of the following:

  1. Equi-joins where one side of the join is character data. The result of this equi-join depends on collation and this can change on a session basis, giving an incorrect result in the case of query rewrite or an inconsistent materialized view after a refresh operation.

  2. Expressions that generate internal conversion to character data in the select list of a materialized view, or inside an aggregate of a materialized aggregate view. This restriction does not apply to expressions that involve only numeric data, for example, a+b where a and b are numeric fields.


24.0 ORACLE TRACE COLLECTION SERVICES

(See chapter in the 8.1.7 Oracle8i Documentation Addendum on this topic.)

Beginning with version 8.1.6, Oracle Trace collection services provide a new optional circular data collection mode that allows Oracle Trace data to be collected over an extended period of time, but with only the most recent data retained, up to a user-specified file size limit.

To use the circular mode from the Oracle Trace Command Line Interface (CLI), specify a negative value for the CLI "start collection" max file size parameter (-2 GB limit); max size each of (up to 2) files is its magnitude.

Please see the Oracle Technology Network (http://technet.oracle.com/) for on-line access to the latest "Oracle Trace Developer's Guide", which includes more detail on the Oracle Trace CLI features and their use.


25.0 interMEDIA, SPATIAL SERIES, VISUAL INFORMATION RETRIEVAL

(See chapter in the 8.1.7 Oracle8i Documentation Addendum on this topic.)

25.1 interMEDIA TEXT README, Release 8.1.7

The interMedia Text new features for this release are described in Chapter 1 of the Oracle8i Documentation Addendum, release 8.1.7. This readme describes the interMedia Text new features and enhancements not included in the Addendum. It also describes how to upgrade your system manually from to from 8.1.6 to 8.1.

25.2 interMedia DATABASE OBJECT INSTALLATION

The interMedia database object installation verification script located in $ORACLE_HOME/ord/im/admin/imchk.sql will always report an INVALID package status for the interMedia component . The DBA can correct this problem by making the following code changes to that file:

  1. Move the initialization of the expected_status variable to before the first call to Cartridge.Check_Component_Status().

  2. Change 'ORDIMErrorCodes' to 'ORDIMERRORCODES' in the first call to Cartridge.Check_Component_Status().

  3. Rerun the imchk.sql script to verify installation of interMedia objects in the target database.

Example: OLD

-- check common error handling package
  Cartridge.Check_Component_Status(
        'ORDIM', 'ORDSYS',
        'ORDIMErrorCodes', 'PACKAGE','Internal Package',
        expected_status, actual_status);
-- check source first
  expected_status:='VALID';

Example: NEW

  expected_status:='VALID';
-- check common error handling package
  Cartridge.Check_Component_Status(
        'ORDIM', 'ORDSYS',
        'ORDIMERRORCODES', 'PACKAGE','Internal Package',
        expected_status, actual_status);
-- check source first


26.0 GENERIC ODBC CONNECTIVITY

The ODBC agent cannot load libraries if they are located in directories that are not listed in the LD_LIBRARY_PATH environment variable. The environment variable LD_LIBRARY_PATH should be set before the Net8 listener that spawns the gateway is started.

For example:

If your ODBC driver manager is located in /opt/odbc/lib, you should include this directory in your LD_LIBRARY_PATH before starting the Net8 listener that would spawn off the agent.

In csh:

$ lsnrctl stop
$ setenv LD_LIBRARY_PATH /opt/odbc/lib:${LD_LIBRARY_PATH}
$ lsnrctl start

In bourne shell:

$ lsnrctl stop
$ LD_LIBRARY_PATH=/opt/odbc/lib:${LD_LIBRARY_PATH}
$ export LD_LIBRARY_PATH
$ lsnrctl start


27.0 TIME SERIES

The functions in the Time Series option have migrated to SQL in version 8.1.7, obviating the need for a Time Series option.

The classes of functions that were implemented in SQL as part of the base 8.1.7 server version include the following:

These functions will be callable from SQL, and will be implemented in a relational framework (no PL/SQL or Oracle Objects). See Oracle8i SQL Reference (A76989-01) for a description of these SQL functions.

The benefits of this approach include:

The technical details of the migration from the Time Series option to SQL are discussed in the following white papers (available on http://www.oracle.com/database/options):


28.0 JAVA

Beginning with 8.1.6, a fully functional Java virtual machine (VM) is part of the RDBMS. Individual README files are included for:

Please refer to the individual README files for detailed information of known problems and configuration issues for these Java products.


29.0 CONFIGURATION ASSISTANTS

The Assistants should be run as a user who is a member of the DBA group.

29.1 DATABASE CONFIGURATION ASSISTANT

The Configuration Assistant must have read/write access to the directory where the database datafiles will be created.

The Database Configuration Assistant does not support single instance database creation on raw devices. However, Oracle Parallel Server databases can be created on raw devices. (Related bug: 891248.)

Workaround: Do not specify any RAW device location in the tool. Use the 'Save as script' option, change datafile location (to raw devices), and use the REUSE option in datafiles SQL.

When creating or configuring the database using the Database Configuration Assistant (DBCA), make sure you logged in as the same user who owns the ORACLE_HOME. (Related bugs: 993099, 998153, 1050166, 1053512, 1101672.)

Workaround: Log in as ORACLE_HOME owner account to create or configure databases.

29.2 ORACLE DATA MIGRATION ASSISTANT

(See 33.2 KNOWN ODMA BUGS at the end of this readme.)

29.2.1 8.1.6 Instance Exists On The Node

If an 8.1.7 instance exists on the node, it will be listed as an upgrade candidate. However, this database cannot be upgraded because it is already in the current release. (BUG 820711)

Workaround: None known.


30.0 XML PARSER

XML Parser for PL/SQL & Class Generator for Java are not being loaded into the 8.1.7 database while upgrading from 8.1.5/8.1.6. In order to use XML Parser for PL/SQL, you need to load $ORACLE_HOME/lib/xmlplsql.jar and run $ORACLE_HOME/xdk/plsql/parser/bin/load.sql. To use Class Generator for Java, you need to load $ORACLE_HOME/lib/classgen.jar.


31.0 8.1.6 FIXED BUGS

(SEE README_rdbms816fixed.htm ON CD)


32.0 8.1.7 FIXED BUGS

(SEE README_rdbms817fixed.htm ON CD)


33.0 8.1.7 OPEN BUGS

33.1 MISCELLANEOUS BUGS

BUG:692232

Description: When autonomous transactions are used within distributed transactions and there is a deadlock situation, then the distributed transaction does not timeout.

Workaround: Do not use autonomous transactions with distributed transactions.

BUG:820224

Description: During CREATE TABLE AS SELECT using parallel query, Oracle sometimes hangs waiting for an exclusive lock.

Workaround: Disable parallel query for the statement.

BUG:764456

Description: User cannot issue DDLs in the ODCIStatsCollect() function while ANALYZing a partitioned table or a domain index built on a partitioned table.

Workaround: If an association has been defined with the column type, and the ODCIStatsCollect() function in that association type issues a DDL the workaround for the user (in order to ANALYZE the partitioned table successfully) is to define a new association with the column where the ODCIStatsCollect() function is empty. That will ensure that the ANALYZE will be able to successfully collect the standard statistics though no user defined statistics will be collected.

BUG:679751

PL/SQL functions returning VARCHAR cannot be used to create a function-based index directly. The size of the PL/SQL function returning VARCHAR is assumed to be 4000 characters. The maximum key length of an index is dependent on block size and is around 2000 characters for the default block size. The workaround is to explicitly specify the SUBSTR() function when creating the index and in queries.

BUG:556122

Oracle does not support an outer join predicate that includes an attribute reference via a scoped REF column or attribute. A workaround is to specify the table with the scoped REF column as a degenerate derived table in the FROM clause. For example,

SELECT ...
    FROM customers c, (SELECT * FROM orders) o
    WHERE ...

Another valid workaround is to compare the REFs instead of the customer numbers -- it is logically equivalent since the REF uniquely identifies the customer (similar to the customer number)

SELECT ... 
    FROM customers c, orders o 
    WHERE ref(c) = o.cust(+) 
          and o.orderdate is null

33.2 KNOWN ODMA BUGS

Bug:1352192

Trace files occur when some PL/SQL format changes happen while migrating from 8.1.6 to 8.1.7. There is a trigger in JServer which gets executed at start-up and this causes a PL/SQL block to get executed in 8.1.7 before it is upgraded to the new PL/SQL format. A similar trace can also be generated even if Jserver hasn't been installed and a user trigger exists for startup.

Fix/ workaround:

ODMA 8.1.7 will bring up a dialog to let the user know that JServer is detected in the database and the user needs to manually upgrade it. A similar message is given for Time Series, VIR and interMedia.