Skip Headers
 
Oracle9i Database README
Release 2 (9.2)

Oracle9i Database README Release 2 (9.2)
Copyright © 2002, Oracle Corporation. All rights reserved.

Primary Editor: Jenn Polk

Contributing Editors: Cathy Baird, Mark Bauer, Ruth Baylis, Bridget Burke, Ted Burroughs, Phil Locke, Diana Lorentz, Kevin MacDowell, Carol Sexton, and Randy Urbano

The Programs (which include both the software and documentation) contain proprietary information of Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent and other intellectual and industrial property laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained 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. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.

RESTRICTED RIGHTS LEGEND:

If the Programs are delivered to the US Government or anyone licensing or using the Programs on behalf of the US Government, the following notice is applicable:

 
RESTRICTED RIGHTS NOTICE

Programs delivered subject to the DOD FAR Supplement are commercial computer software and use, duplication and disclosure of the Programs including documentation, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject to the Federal Acquisition Regulations are 'restricted computer software' and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR 52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be licensee's responsibility to take all appropriate fail-safe, back up, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the Programs.

 
TRADEMARKS

Oracle is a registered trademark, and SQL*Plus, Oracle9i, Pro*C, Pro*C/C++, Pro*COBOL, Pro*FORTRAN 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.
Documentation Accessibility:

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle Corporation is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program Web site at

http://www.oracle.com/accessibility/
Accessibility of Code Examples in Documentation:

JAWS, a Windows screen reader, may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, JAWS may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation:

This documentation may contain links to Web sites of other companies or organizations that Oracle Corporation does not own or control. Oracle Corporation neither evaluates nor makes any representations regarding the accessibility of these Web sites.

-----------------------------------------------

CONTENTS

1 INTRODUCTION
2 DATABASE SECURITY
3 REAL APPLICATION CLUSTERS
4 COMPATIBILITY
5 ORACLE9i SAMPLE SCHEMAS
6 RESTRICTIONS ON PL/SQL LANGUAGE
7 UTILITIES
8 SQL EXECUTION
9 TRANSPARENT APPLICATION FAILOVER
10 MEDIA MANAGEMENT SOFTWARE
11 LOBs
12 STRUCTURED QUERY LANGUAGE (SQL)
13 REPLICATION
14 STREAMS
15 TYPES
16 DATA DICTIONARY
17 DATA MINING
18 ORACLE CHANGE DATA CAPTURE
19 interMEDIA, SPATIAL, VISUAL INFORMATION RETRIEVAL
20 TIME SERIES
21 CONFIGURATION ASSISTANTS
22 ORACLE NET SERVICES
23 XML DEVELOPER'S KITS (XDK)
24 ORACLE XML DATABASE
25 LOGMINER
26 ORACLE9i DATA GUARD
27 ORACLE TRACE
28 OLAP
29 ORACLE INTERNET DIRECTORY
30 OPEN BUGS

1 INTRODUCTION

1.1 Purpose of this README

This README file is relevant only to the delivered Oracle9i release 2 (9.2) product and its integral parts, such as SQL, PL/SQL, the Oracle Call Interface (OCI), SQL*Loader, Import/Export utilities, and so on. There is also a separate README file for Oracle Net Services (known in previous releases as Net8) and Oracle Ultra Search, Oracle Internet Directory, and Oracle Advanced Security. There may also be separate README files for precompilers and other Oracle products.

For information about upgrading, downgrading, and migration, see Oracle9i Database Migration.

This README documents any differences between the server (and its integral parts) and its documented functionality, as well as known problems and workarounds. For additions and corrections to the server documentation set, please refer to the Oracle9i Documentation Addendum, which is available on the product CD. A list of open known bugs is also available on the product CD.

Operating system releases, such as UNIX, Windows NT, OpenVMS, 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 system bulletins or similar publications.

1.2 Cover Letter and Licensing

Please read the cover letter included with your Oracle9i release 2 (9.2) package.

1.3 Oracle9i Database (9.2) PRODUCT DOCUMENTATION

Files on Line, which shipped with the previous release of the database, will be included with Oracle9iAS release 9.0.2.

Oracle9i Database (9.2) product documentation is available on the documentation CD and on the Oracle Technology Network (OTN). Updates to the documentation on the CD may be available only on OTN.

The following list of books describes the Oracle9i Database documentation set available on the Documentation CD.

Oracle9i SERVER AND SQL*PLUS DOCUMENTATION
Administration, Concepts, and Reference

A96531-01 Oracle9i Database New Features

A96521-01 Oracle9i Database Administrator's Guide

A96524-01 Oracle9i Database Concepts

A96525-01 Oracle9i Database Error Messages

A96530-01 Oracle9i Database Migration

A96533-01 Oracle9i Database Performance Tuning Guide and Reference

A96532-01 Oracle9i Database Performance Planning

A96536-01 Oracle9i Database Reference

A96652-01 Oracle9i Database Utilities

A96529-01 Oracle9i Database Globalization Support Guide

A96539-01 Oracle9i Sample Schemas

A96540-01 Oracle9i SQL Reference

A96544-01 Oracle9i Heterogeneous Connectivity Administrator's Guide

A97290-01 Oracle9i Database Generic Documentation Master Glossary

A96625-01 Oracle9i Database Generic Documentation Master Index

Backup and Recovery

A96519-01 Oracle9i Backup and Recovery Concepts

A96566-01 Oracle9i Recovery Manager User's Guide

A96565-01 Oracle9i Recovery Manager Reference

A96564-01 Oracle9i Recovery Manager Quick Reference

A96572-01 Oracle9i User-Managed Backup and Recovery Guide

A97202-01 Oracle9i Backup and Recovery Documentation Online Roadmap

Data Warehousing

A96520-01 Oracle9i Data Warehousing Guide

Data Mining

A95959-01 Oracle9i Data Mining Administrator's Guide

A95961-01 Oracle9i Data Mining Concepts

Streams and Replication

A96571-01 Oracle9i Streams

A96567-01 Oracle9i Advanced Replication

A96568-01 Oracle9i Replication Management API Reference

SQL*Plus

A90842-01 SQL*Plus User's Guide and Reference

A90843-01 SQL*Plus Quick Reference

Data Guard

A96629-01 Oracle9i Data Guard Broker

A96653-01 Oracle9i Data Guard Concepts and Administration

Oracle9i DIRECTORY, NETWORKING, AND SECURITY DOCUMENTATION
Networking and Security

A96580-01 Oracle9i Net Services Administrator's Guide

A96581-01 Oracle9i Net Services Reference Guide

A96573-01 Oracle Advanced Security Administrator's Guide

A96578-01 Oracle Label Security Administrator's Guide

A96582-01 Oracle9i Security Overview

Oracle Internet Directory

A96574-01 Oracle Internet Directory Administrator's Guide

A96577-01 Oracle Internet Directory Application Developer's Guide

A96579-01 Oracle9i Directory Service Integration and Deployment Guide

Oracle9i Real Application Clusters

A96599-01 Oracle9i Real Application Clusters Documentation Online Roadmap

A96596-01 Oracle9i Real Application Clusters Administration

A96600-01 Oracle9i Real Application Clusters Setup and Configuration

A96597-01 Oracle9i Real Application Clusters Concepts

A96598-01 Oracle9i Real Application Clusters Deployment and Performance

A96601-01 Oracle9i Real Application Clusters Real Application Clusters Guard I - Concepts and Administration

Oracle9i SERVER APPLICATION DEVELOPMENT DOCUMENTATION
Application Development

A96590-01 Oracle9i Application Developer's Guide - Fundamentals

A96587-01 Oracle9i Application Developer's Guide - Advanced Queuing

A96591-01 Oracle9i Application Developer's Guide - Large Objects (LOBs)

A96594-01 Oracle9i Application Developer's Guide - Object-Relational Features

A96628-01 Oracle9i Application Developer's Guide - Workspace Manager

A96621-01 Oracle9i XML Developer's Kits Guide - XDK

A96620-01 Oracle9i XML Database Developer's Guide - Oracle XML Database

A88895-01 Oracle9i Case Studies - XML Applications

A96595-01 Oracle9i Data Cartridge Developer's Guide

Language and Interface

A96584-01 Oracle Call Interface Programmer's Guide

A96583-01 Oracle C++ Call Interface Programmer's Guide

A96624-01 PL/SQL User's Guide and Reference

A96108-01 Pro*C/C++ Precompiler Programmer's Guide Vol-I

A97263-01 Pro*C/C++ Precompiler Programmer's Guide Vol-II

A97269-01 Pro*C/C++ Precompiler Programmer's Guide (whole set)

A96109-01 Pro*COBOL Precompiler Programmer's Guide

A42523-01 Pro*FORTRAN Supplement to the Oracle Precompilers Guide

A42525-01 Programmer's Guide to the Oracle Precompilers

A58231-01 SQL*Module for Ada Programmer's Guide

Application Reference

A96616-01 Oracle9i XML API Reference - XDK and Oracle XML Database

A96621-01 Oracle9i XML Developer's Kits Guide - XDK

A96609-01 Oracle9i Supplied Java Packages Reference

A96612-01 Oracle9i Supplied PL/SQL Packages and Types Reference

interMedia, Oracle Spatial, and Oracle Syndication Server

A96120-01 Oracle interMedia Annotator User's Guide

A96121-01 Oracle interMedia Java Classes User's Guide and Reference

A88786-01 Oracle interMedia User's Guide and Reference

A96630-01 Oracle Spatial User's Guide and Reference

A88787-01 Oracle Syndication Server User's and Administrator's Guide

Oracle Text Documentation

A96517-01 Oracle Text Application Developer's Guide

A96518-01 Oracle Text Reference

ADDITIONAL DOCUMENTATION
Oracle Enterprise Manager

A96670-01 Oracle Enterprise Manager Administrators Guide

A96673-01 Oracle Enterprise Manager Configuration Guide

A96674-01 Oracle Enterprise Manager Concepts Guide

A96675-01 Oracle Enterprise Manager Event Test Reference Manual

A96671-01 Oracle Enterprise Manager Messages Manual

A86647-01 Database Tuning with the Oracle Tuning Pack

A96679-01 Getting Started with Oracle Change Management Pack

A88720-01 Getting Started with Oracle Management Pack for Oracle Applications

A88748-01 Getting Started with the Oracle Diagnostics Pack

A96680-01 Getting Started with the Oracle Standard Management Pack

Java

A96656-01 Oracle9i Java Developers Guide

A96659-01 Oracle9i Java Stored Procedures Developers Guide

A96654-01 Oracle9i JDBC Developers Guide and Reference

A96658-01 Oracle9i JPublisher Users Guide

A96655-01 Oracle9i SQLJ Developers Guide and Reference

A96657-01 Oracle9i Support for JavaServer Pages Reference

OLAP

A95297-01 Oracle9i OLAP Developer's Guide to the OLAP API

A95298-01 Oracle9i OLAP Developer's Guide to the OLAP DML

A95295-01 Oracle9i OLAP User's Guide

Workflow

A96644-01 Oracle Workflow Client Installation Notes (release 2.6.2)

A95265-03 Oracle Workflow Guide (release 2.6.2)

A96643-01 Oracle Workflow Server Installation Notes (release 2.6.2)

Other Documentation

A88783-01 Oracle Dynamic Services User's and Administrator's Guide

A96676-01 Oracle Intelligent Agent Users Guide

A96672-01 Oracle SNMP Support Reference Guide

A96690-01 Oracle Universal Installer Concepts Guide

A97204-01 Oracle Ultra Search Online Documentation

2 DATABASE SECURITY

2.1 The Security Checklist

In order to fully maximize the security features offered by Oracle9i, Release 2 (9.2), it is imperative that Oracle9i itself is well protected. To provide guidance for configuring Oracle9i in a secure manner, Oracle provides a security checklist. Oracle recommends that you read this checklist and configure your database accordingly. The security checklist can be found at the following URL:

http://otn.oracle.com/deploy/security/oracle9i/pdf/9iR2_checklist.pdf

Note that Chapter 23, "Establishing Security Policies", of the Oracle9i Database Administrator's Guide, Release 2, also contains a copy of the security checklist. However, the checklist has been updated since the close date for publication of the book. Please refer to the listed URL for the most recent version of the security checklist.

2.2 Database User Accounts Locked

In Oracle9i, Release 2 (9.2), all default database user accounts except SYS, SYSTEM, SCOTT, and DBSNMP are locked upon database creation using the Database Configuration Assistant (DBCA).

At the end of the database creation process, the DBCA displays a page requiring that you change the default passwords for user accounts SYS and SYSTEM. This page prompts you to enter and confirm new passwords for the SYS and SYSTEM user accounts. It also provides access to a password management page that enables you to selectively unlock and specify new passwords for other Oracle9i default user accounts.

If you create a database using the DBCA in silent mode, and pass the -passwordDialog true parameter, then Oracle also presents a dialog requiring that you change the default passwords for user accounts SYS and SYSTEM.

You can optionally activate locked accounts manually at a later time by using, for example, an ALTER USER statement.

2.3 Data Dictionary Protection

Data dictionary protection is now enabled by default. Specifically, the O7_DICTIONARY_ACCESSIBILITY initalization parameter is set to FALSE on installation. As a result, regular users who are not database administrators with ANY privileges (for example, SELECT ANY TABLE) can no longer use the ANY privilege upon data dictionary objects; however, the user can access non-SYS schema objects using the ANY privilege.

Users making a database administrator user type connection (for example, CONNECT / AS SYSBDA) can exercise privilege on data dictionary objects, since SYSDBA has all privileges.

A new system privilege, SELECT ANY DICTIONARY, provides users with SELECT access to any object in the SYS schema without giving them DBA privileges.

For example,

Oracle Corporation recommends that the dictionary protection feature remain enabled as it is a more secure configuration. If O7_DICTIONARY_ACCESSIBILITY is set to TRUE, then regular users with the ANY privilege can use these privileges -- perhaps maliciously -- to alter data dictionary objects.

2.4 Fine Grained Access Control

Due to stringent access control enforced by Fine Grained Access Control, queries that are subjected to any policy predicate may encounter ORA-28133 when attempting to execute a DDL statement against the VPD-enabled object. The workaround is to temporarily disable VPD or use EXEMPT ACCESS POLICY privilege to complete the DDL changes. Please also report this error as a bug. The exceptions to this are DBMS_STATS and ALTER TABLE .. ADD COLUMN with DEFAULT value.

2.5 Resource Profiles

When session resource limits are exceeded, the session will be automatically closed.

3 REAL APPLICATION CLUSTERS

3.1 Tablespace Size Recommendations

The tablespace size recommendations shown in Table 2-1 of Oracle9i Real Application Clusters Setup and Configuration have increased for four of the tablespaces as follows:

SYSTEM -- 420MB
TEMP -- 120MB
UNDOTBS -- 320MB
ODM -- 280MB

3.2 Real Application Clusters Deployment and Performance Monitoring

Only use oradebug commands as documented in Oracle9i Real Application Clusters Deployment and Performance with the supervision of Oracle Support.

3.3 Incorrect Reference to XML DB Tablespace

The tablespace for the Oracle XML DB feature is incorrectly referred to as the XML tablespace, and its datafile file name contains the string xml instead of xdb.

Workaround

In Oracle9i Real Application Clusters Setup and Configuration Release 2 (9.2), change the references to XML tablespace to XDB tablespace and change xml to xdb in the datafile names. These appear in Tables 2-1, 2-2, 2-3, and 2-4, and in Example 2-1. For example, the datafile name db_name_raw_xml_50m should be db_name_raw_xdb_50m and the datafile symbolic link name db_name_xml should be db_name_xdb.

3.4 Real Application Clusters Deinstallation

To perform deinstallation in the Real Application Clusters environment, please do not choose the deinstall products option from the Welcome page in OUI.

Please follow the following steps to perform Real Application Clusters deinstallation.

  1. 1. Choose the nodes that your oracle home has been installed on in the node selection page in OUI.
  2. 2. Click on Installed Products button in the File Locations page in OUI.
  3. 3. Choose the Oracle home you want to deinstall and click the Remove button.

  4.  

     

4 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.

4.1 Nesting of Quotation Marks in Initialization Parameter Values

Starting with Oracle9i release 9.2, the nesting of quotation marks using the backslash (\) escape character is no longer supported. This may affect how Oracle interprets parameter values in your initialization parameter file or server parameter file.

For example, assume you have the following initialization parameter in your parameter file:

CONTROL_FILES = "ctlfile\'1.ora"

In releases prior to release 9.2, the filename would be interpreted as ctlfile'1.ora. Starting with release 9.2, the filename in the previous example would be interpreted as ctlfile\'1.ora.

Oracle Corporation highly recommends modifying your parameter files to remove such references. Refer to the Oracle9i Database Reference for other methods of nesting quotation marks in initialization parameter values.

4.2 CONNECT INTERNAL Desupport

CONNECT INTERNAL has been desupported in this release. Users making DBA-privileged connections to the database must use one of the following syntaxes:

CONNECT / AS SYSDBA  or CONNECT <username/password> AS SYSDBA

Users who have relied on CONNECT INTERNAL in connect strings must replace CONNECT INTERNAL with one of the above commands. See the Oracle9i Database Administrator's Guide for further details.

4.3 SQL.BSQ

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

4.4 LU6.2 Protocol

LU6.2 protocol is not supported for Oracle9i. Migrate or upgrade to TCP/IP based protocols.

4.5 Interoperability with the 8.1.7 JDBC Thin Driver

There is a known problem with connecting from a client using the 8.1.7.0 or 8.1.7.1 JDBC Thin driver to the 9.0.1 Oracle database. The problem causes the Oracle server to return ORA-600 errors during operation. A JDBC patch is available for download from:

http://metalink.oracle.com/metalink/plsql/m12_documents.showNOT?p_id=147731.1

The patch must be applied to the 8.1.7.0 and 8.1.7.1 JDBC drivers to ensure proper operation.

4.6 JDBC Updates

For up-to-date information about database URLs and connection cache timeouts in the JDBC Developer's Guide and Reference, please see

http://otn.oracle.com/tech/java/sqlj_jdbc/content.html

4.7 OJMS Queues and Upgrade

If an upgrade is performed to RDBMS 9.2, from an earlier database version in which OJMS queues or topics already exist, then the OJMS type bodies that contain PL/SQL member functions may become invalid and unusable. The OJMS PL/SQL types and the AQ queues and topics that were created using these types however remain usable and no data is lost or corrupted. The OJMS Java client library functionality is also not affected. This problem is documented in Bug 2365565 and a patch can be downloaded from Metalink.

4.8 Configuring ODM for Upgraded Database Requires XML DB

When upgrading an Oracle9i release 9.0.1 database that does not have ODM installed, DBCA will not permit configuring ODM in the upgraded database if XML DB is not already configured. The ODM option is grayed out and, therefore, cannot be selected.

Workaround

ODM requires that the XML database option be either configured or be selected to be configured. If you select the Oracle XML DB feature and then select ODM, you will be able to configure ODM.

5 ORACLE9i SAMPLE SCHEMAS

The seed database now contains a set of sample schemas (in addition to the traditional SCOTT schema), which are installed by default when you install Oracle. These sample schemas are used in many of the examples that appear in the documentation and training materials for Oracle9i. These new schemas are documented in the book Oracle9i Sample Schemas, which is on the documentation CD.

6 RESTRICTIONS ON PL/SQL LANGUAGE

6.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 if one of the servers is a 8.0.X release. Symptoms may vary depending upon the source code involved. The errors 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.

6.2 DATETIME

For functions implemented in PL/SQL, the following checks are not completely enforced for argument or return types: the datetime and interval day to seconds fractional seconds precision check, and the interval leading field precision check. This affects the precision part of the datetime/interval data when SQL calls a PL/SQL function.

6.3 External Procedures: Loading DLLs through PL/SQL Libraries

In Oracle9i release 2, EXTPROC, by default, will only load DLLs that are in the $ORACLE_HOME/bin or $ORACLE_HOME/lib directory. Also, only local sessions, that is, Oracle client process that are running on the same machine, are allowed to connect to EXTPROC.

To load DLLs from other directories, the environment variable EXTPROC_DLLS should be set. The value for this environment variable is a colon (:) separated list of DLL names qualified with the complete path.

For example:

EXTPROC_DLLS=/private1/home/scott/dll/myDll.so:/private1/home/scott/dll/newDll.so

The preferred method to set this environment variable is through the ENVS parameter in the listener.ora file. Please refer to the Oracle Net manual and the Oracle9i Application Developer's Guide - Fundamentals for more information on the EXTPROC feature.

7 UTILITIES

7.1 SQL Loader

7.1.1 BUFFERS Keyword

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

7.1.2 Size of CHAR or RAW Fields in Direct Path Loads When No Length is Specified

For direct path loads in previous versions of SQL*Loader, the size of a CHAR or RAW field specified with no length in the data file was assumed to be the same size as the column in the table. As of Oracle9i, direct path loads operate in the same way as conventional path loads and assume a length of 255.

7.1.3 SQL*Loader and Multi-Level Collections

SQL*Loader does not support multi-level collections in Oracle9i. One possible workaround is to load each table individually as a single-table load, and supply your own correlation information; for example, supply the SIDs. If you do this, you must be careful about rows in the parent table without corresponding child rows in the child nested tables, and orphan rows in the child nested tables without parent rows. Currently, this method works only for direct path loads; it does not work in conventional path.

7.1.4 SQL*Loader Handling of Byte-Order Mark and SKIP Parameter

If the character set of the datafile is a unicode character set (UTF-16 or UTF-8) and it contains a byte-order mark (BOM) in the first few bytes of the file, then use of the command line parameter SKIP is not supported. If SKIP is specified, the byte-order mark will not be read and interpreted as a byte-order mark.

7.1.5 Using SQL*Loader or OCI for Data Loading

If you use SQL*Loader (with conventional path) or OCI to load data into varrays or primary-key-based-refs (pkrefs) and the data being loaded is in a different character set from the database character set, then unexpected problems may occur. The problems include:

To avoid these problems, set the client character set (using the environment variable NLS_LANG) to the database character set before loading the data.

Another general problem when converting data to a different character set is data loss because the output database character set is not a superset of the input character set. To avoid data loss problems, be sure to follow the guidelines in the Oracle9i Globalization Support Guide.

7.2 External Table Restrictions

7.2.1 External Table Directory Objects

A user must have privileges explicitly granted rather than through roles in order to access an external table directory object. Otherwise, the user may or may not have access to the directory object.

7.2.2 Whitespace in TERMINATED BY String

If the access parameters for an external table specify TERMINATED BY with a string that only contains whitespace (blanks, tabs, and so on), then also specifying ENCLOSED BY for the same field will cause all rows to be rejected. An example of this is shown below.

ACCESS PARAMETERS (FIELDS  
(DEPTNO CHAR(10)  TERMINATED BY "  " ENCLOSED BY "(" AND ")"),  
DNAME CHAR(30))

The problem is the code that parses the fields will skip all whitespace after the last enclosing delimiter is found before it looks for a terminator string. After the whitespace is skipped, the code then looks for the terminator, but since all whitespace has been skipped, the delimiter is not found. There is no single workaround to this problem, though omitting the TERMINATED BY clause may work in some cases.

7.3 Oracle Locale Builder Utility

When using the Locale Builder utility to create new locale definitions, it is important for the users to review the semantics of their changes according to SQL specifications. If the generated locale object contains custom formats, for example, a custom date/time format, which does not conform to SQL standards, the users may experience runtime errors when trying to activate their new locale definitions. Therefore, it is advisable that the users take extra care in creating custom formats and validating their changes.

When adding classification data for customized multibyte character sets, users should base the definition on an 8-bit or multibyte character set, since classification verification for multibyte character sets does not apply to 7-bit character sets.

7.4 Miscellaneous Utilities Issues

7.4.1 Restriction for the DBMS_METADATA Package and the Oracle Shared Server Environment

The new DBMS_METADATA package (See Oracle9i Supplied PL/SQL Packages and Types Reference) generates database object definitions as either XML or SQL DDL. In an OSS environment, it must disable session migration and connection pooling. This results in any shared server process servicing a session that runs the package to effectively become a default, dedicated server for the life of the session. Make sure sufficient shared servers are configured when the package is used and that the number of servers is not artificially limited by too small a value for the MAX_SHARED_SERVERS initialization parameter.

7.4.2 New Waits Parameter Supported in TKPROF Utility

A new parameter called Waits is now supported in the TKPROF Utility. With this fix a new parameter, wait information is available for tkprof. The parameter has a default value of yes. When set to yes, any wait events captured in the trace file will result in a summary that is produced per cursor involved, along with a overall summary for recursive and non-recursive calls for each wait event encountered. The summary will include count, max and sum aggregates for each wait event. To disable the feature, specify waits=no.

7.4.3 Change in DBNEWID Behavior

The changing of the database name does not require a resetlogs, therefore all previous backups are still useable. However, all datafiles require the header to be updated so all datafiles must first be placed into read-write mode. In addition, the following text is displayed if the database name has been changed successfully.

Database name changed to PROD12.
Modify parameter file and generate a new password file before restarting. 
Succesfully changed database name.
DBNEWID - Completed succesfully.

8 SQL EXECUTION

8.1 Plan Stability and 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, because 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>;

8.2 EXPLAIN PLAN and V$SQL_PLAN

In Oracle9i, there are circumstances where EXPLAIN PLAN cannot be used to predict the execution plan for a query with certainty. The optimizer can peek at the value of a user bind variable at the first invocation of the query and use that value in generating an execution plan. That plan may be different from the plan that would be produced by EXPLAIN PLAN. The peeking feature is enabled if the parameter optimizer_features_enable is set to Oracle9i release 9.0.0 or higher. There can also be differences between EXPLAIN PLAN and the plan the optimizer would generate for actually running the query if the parameter cursor_sharing is set to similar. To obtain the actual execution plan for a cursor, use v$sql_plan.

8.3 Multi Join Key Pre-Fetching

This feature enables parallel reads during a table lookup nested loop join. When activated, the table lookup node is pushed up above the nested loop join node in the EXPLAIN PLAN output. This allows blocks containing rows matching multiple join key values to be prefetched in parallel with a single vector read.

This change to the EXPLAIN PLAN output is not considered a different execution plan. It does not affect the join order, join method, access method, or parallelization scheme.

8.4 GROUP BY Clause

The GROUP BY clause does not define a sort order. If you have not specified an ORDER BY clause in your SELECT statement, and have been relying on the row order returned by the DBMS, you may see a different result order in Oracle9i release 2. Refer to the GROUP BY clause in the Oracle9i SQL Reference for more information.

9 TRANSPARENT APPLICATION FAILOVER

Select Failover is implemented by transparently re-executing 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 fails, the query will not be transparently restarted.

10 MEDIA MANAGEMENT SOFTWARE

In this release, Oracle has bundled a new version of the Legato backup and recovery software for Oracle databases. This is a single version of Legato NetWorker and the NetWorker Module for Oracle client, which has been designed to operate on the same server as your Oracle database. After installing this version of Legato NetWorker, you will receive a license notice each time you back up your database to tape using the Oracle Recovery Manager (RMAN) interface. To eliminate this license notice, you can enter an authorization code, which can be obtained directly from Legato without charge. Instructions for obtaining this code are in the displayed license notice.

Oracle no longer distributes Legato documentation. Legato NetWorker documentation can be obtained directly from Legato and can be found at the following Web site:

http://www.legato.com/lssv/

This site will also contain any product updates for this NetWorker version.

If you have previously installed and used Legato Storage Manager (LSM) on your Oracle database server, you can uninstall it and install this new version of Legato NetWorker. Any backups made by LSM are still usable by the new Legato NetWorker software.

11 LOBs

11.1 SQL Functions on LOBs

For single byte and multibyte character set data, including varying-width character sets, the following functions have known issues:

NVL

COUNT

DECODE

NVL and COUNT: bug1475475 ORA-00932 when using NVL() or COUNT() on CLOB.

DECODE: bug 1473512 - ORA-0932 in DECODE (NCHAR,NCLOB,NCHAR,NCHAR) in PL/SQL

11.2 Passing Temp LOB Parameters Between Different Languages

This note applies to you if you:

When a temporary LOB (either standalone or embedded inside an object) is passed as an IN parameter to a procedure (or function) implemented in Java or C, and the LOB is written into the procedure, the caller of the procedure will not see the modifications made inside the procedure. If the caller needs to see the modifications, the LOB parameter must be declared as an IN OUT parameter.

Prior to Oracle9i release 2, even if the LOB parameter was declared as IN instead of IN OUT, changes made to temporary LOBs inside the procedure were visible outside the procedure. However, this is no longer true in Oracle9i release 2.

Since there are existing applications that may have come to rely on being able to write into IN temporary LOBs, this behavior is being maintained in Oracle9i release 2 for the most common case, which is, writes into top level temporary LOBs (not embedded inside object types) for invocations from PL/SQL to both Java and C. For all the other cases, the parameter must be declared as IN OUT if the intent is to modify the temporary LOB.

This behavior for temporary LOBs passed in from PLSQL to Java and C will be desupported in Oracle10i, and it will then become mandatory to declare these LOB parameters as IN OUT. It is recommended that such parameters be changed to IN OUT prior to Oracle10i.

To change a parameter to IN OUT:

  1. Modify the parameter declaration to be IN OUT.
  2. Modify the Java/C function parameter type to match the IN OUT parameter type rather than the IN parameter type. For Java implementations, this is normally done by declaring the parameter to be a single element array of the corresponding Java type and modifying the Java code to get the LOB or object value from the array rather than using the parameter directly. For C implementations, this is done by changing the LOB or object (of a non-final type only) parameter to a double pointer rather than a single pointer and modifying the C code appropriately. Please see Oracle9i documentation for details.

  3.  

     

An example of changing the parameter type for a Java implementation is given below.

Rem ---------------------------------------------------
Rem A procedure with a clob declared as an IN parameter
Rem and its java implementation
Rem ---------------------------------------------------

create procedure foo (x clob) 
  is language java 
  name 'fooclass.jfoo(oracle.sql.CLOB)';
/

import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class fooclass 
{
  public static void jfoo(CLOB x) throws SQLException
  {
    try 
    {
      Writer w = x.getCharacterOutputStream();
      w.write("hello world");
      w.close();
    }
    catch (Exception e)
    {
    }
  }
}

Rem ------------------------------------------------------
Rem The modified procedure with the clob declared as an 
Rem IN OUT parameter and its modified java implementation.
Rem Note that the parameter type has been changed to 
Rem IN OUT and the java parameter has been changed to an
Rem array.
Rem ------------------------------------------------------

create procedure foo (x IN OUT clob) 
  is language java 
  name 'fooclass.jfoo(oracle.sql.CLOB[])';
/

import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class fooclass 
{
  public static void jfoo(CLOB x[]) throws SQLException
  {
    try 
    {
      Writer w = x[0].getCharacterOutputStream();
      w.write("hello world");
      w.close();
    }
    catch (Exception e)
    {
    }
  }
}

12 STRUCTURED QUERY LANGUAGE (SQL)

12.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 following 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.

12.2 SQL access to Analytic Workspaces

SQL access to Analytic Workspaces in 9.2.0.1 is achieved by using table functions, that is, through calls to the olap_table function. Attempting to use this table function in a multi-user environment in shared server mode causes the client sessions to crash (end of communication channel error). The workaround is to run all SQL access to Analytic Workspaces in dedicated process mode.

13 REPLICATION

13.1 Procedures

Restriction: Do not use the DBMS_MVIEW.REFRESH_ALL_MVIEWS and DBMS_MVIEW.REFRESH_DEPENDENT procedures to refresh materialized views used in a replication environment.

Workaround: Use the DBMS_REFRESH.REFRESH or the DBMS_MVIEW.REFRESH procedures to refresh your materialized views in a replication environment.

13.2 Interoperability

Oracle9i master sites can only interact with Oracle7 release 7.3.4 or higher master sites. Oracle9i materialized view sites can only interact with Oracle7 release 7.3.4 or higher master sites. Oracle9i master sites can only interact with Oracle7 release 7.3.4 or higher materialized view sites.

13.3 Replication Installation

The Advanced Replication feature is now automatically installed and upgraded in every Oracle9i database. There is no need to explicitly run the catrep.sql script as it is now called from catproc.sql.

13.4 Globalization Support (NLS) and Replication

In a replication environment involving Oracle9i and previous releases of the database at sites utilizing NCHAR and NVARCHAR2 datatypes, an Oracle patch must be installed at the earlier release (pre-9i) site. Contact Oracle Support to obtain the appropriate NLS patch as recommended in ALERT 140014.1 Oracle8/8i to Oracle9i using New AL16UTF16 available in MetaLink:

http://metalink.oracle.com

13.5 Virtual Private Database (VPD) and Replication

For multimaster replication, there must be no VPD restrictions on the replication propagator and receiver schemas.

For materialized views, the defining query for the materialized view may not be modified by VPD. VPD must return a NULL policy for the schema that performs both the create and refresh of the materialized view. Creating a remote materialized view with a non-NULL VPD policy will not generate an error but may yield incorrect results.

13.6 Type Evolution Not Supported

Type evolution is not supported in a replication environment. If a column of a replicated table or a replicated object table is based on a user-defined type, then you cannot alter the user-defined type.

14 STREAMS

14.1 Streams Queue Table Sizing

In normal operation, DML and DDL events captured with Oracle Streams are placed in an optimized queue buffer. In situations where a downstream replication site is unavailable or unable to process incoming events quickly enough, the buffer can spill over into the Streams queue table. The queue table must be sized appropriately to handle these overflow events.

14.2 Database Configuration for Streams Capture

The optional capture process of Oracle Streams requires that the SHARED_POOL_SIZE initialization parameter be set to a minimum value of 100 MB and that the database be running in ARCHIVELOG mode.

14.3 Capture "Invalid Dictionary Error"

The first capture process created on a database builds the Streams dictionary. If this initial dictionary build is interrupted, an error indicating that the dictionary is invalid may occur. The typical cause of an interrupted dictionary build is a shutdown abort operation. All other shutdown modes will wait for the dictionary build to complete. If you encounter this error, drop and recreate the capture process.

14.4 JMS Types and XMLTypes

JMS Types and XMLType access to the Streams queue table is not enabled by default to minimize the impact of Bug 2248652 that causes export of the Streams queue table to fail. Users can enable this access by calling the DBMS_AQADM.ENABLE_JMS_TYPES(queue_table) procedure where the VARCHAR2 parameter queue_table is the name of the queue table. This procedure should be invoked after the call to DBMS_STREAMS_ADM.SET_UP_QUEUE. Sites dependent on the Export utility as their backup strategy should avoid enabling this access.

14.5 Streams Wizard in Oracle Enterprise Manager

When using the Oracle Streams Wizard, via OMS or in standalone mode, to configure replication, the generated scripts are incorrect and may produce errors such as "ORA-26687 Instantiation SCN not set" or "ORA-1403 no data found" (bugs 2301915, 2318295 and 2326341). Please contact Oracle Support for an Enterprise Manager and Oracle Management Server patch that will produce the correct scripts. Use the bug numbers listed here as a patch reference.

15 TYPES

15.1 Inheritance

The following situation is known to have issues in this release:

Concurrent session issue, that is, executing a DDL in one session and concurrently using DDL or DML in another session, has known issues. We recommend that you issue DDLs in one session only and not do anything in other sessions until the DDLs are complete.

15.2 Restrictions with the Any types (SYS.AnyData, SYS.AnyType and SYS.AnyDataSet) in Oracle9i release 2

Any data based on user-defined SQL object types that are part of inheritance hierarchies, or which have been evolved, are not supported in persistent table columns. They are supported for transient usage for parameter passing and so on. However, attempts to store such values persistently will return an error.

15.3 User-defined Constructors and Upgrade/Downgrade

Oracle9i release 2 cannot be downgraded to a previous release if user-defined type(s) exist that contain user-defined constructors, or which embed a user-defined type (at an arbitrary level of nesting) that contains user-defined constructors, and if such a type is used in a column of a table. If such a type exists, but it is not used in a column of a table, then downgrade is permitted. However, upon downgrade, the constructors are automatically replaced with static functions. If downgrade was not permitted due to the presence of offending types in tables, then the user may choose to either drop the constructor function from the type, or drop the column from the table.

16 DATA DICTIONARY

16.1 Data Dictionary Future View Changes

The following V$ views may change as follows in a future release of Oracle:

17 DATA MINING

17.1 Need for Compatible Character Sets

When using ODM in a shared JVM environment, such as when integrated with a servlet application, all connections made to an ODM server (also known as a Data Mining Server) must be based on databases with compatible character sets. Otherwise, string length tests conducted in the JVM may not recognize these differences, allowing data to pass to the database, which could result in server-side failures.

17.2 Cannot Import Naive Bayes PMML Models

Importing Naive-Bayes PMML models fails when TargetValueCount element's count attribute contains a real number. The count attribute's value for the TargetValueCount element in a Naive-Bayes model most commonly contains an integer value; however, the PMML specification allows for real numbers. If the document to be imported contains real numbers for this attribute, the import will fail.

Workaround

Convert the values of the count attribute to integers before performing the PMML model import MATERIALIZED VIEWS.

17.3 Feature Availability

The creation and refresh features of materialized views are supported in both the Standard and Enterprise Editions. However, Query Rewrite and Summary Advisor are available in the Enterprise edition only.

17.4 EVENT 30441 No Longer Needed

Oracle9i ignores event 30441. because Oracle9i supports fast refresh of materialized views with joins and aggregates after SQL DML

17.5 NLS Parameters

When using certain materialized views as described below, you must ensure that your NLS parameters are the same as when you created the materialized view. Materialized views that fall under this restriction contain the following constructs:

  1. Expressions that may return different values, depending on NLS parameter settings.
  2. Equijoins where one side of the join is character data. The result of this equijoin 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.
  3. 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.

  4.  

     

17.6 Summary Advisor Privileges and ORA-30446

When processing a workload, the Summary Advisor attempts to validate each statement in order to identify table and column references. If the current database user does not have SELECT privilege on a particular table, the Advisor bypasses the statement referencing the table. This may cause many statements to be excluded from analysis. If the Advisor excludes all statements in a workload, the workload is invalid and the Advisor returns the following message:

ORA-30446, valid workload queries not found

To avoid missing critical workload queries, the current database user must have SELECT privilege on the tables that are targeted for materialized view analysis. Moreover, the SELECT privilege cannot be obtained via a role.

18 ORACLE CHANGE DATA CAPTURE

18.1 Change Data Capture: Database Configuration Assistant Considerations.

If using the Database Configuration Assistant to create a database that uses one of the pre-defined templates, you can choose any of the following templates:

Data Warehouse and the Transaction Processing database templates support the Oracle Change Data Capture feature.

You may choose the New Database option to build a custom database. If so, you must select the database feature "Oracle JVM" from the Additional database configurations dialog box. Oracle JVM is already selected by default: do not deselect it. Oracle Change Data Capture requires the Oracle JVM feature.

19 interMEDIA, SPATIAL, VISUAL INFORMATION RETRIEVAL

19.1 Oracle interMedia

See the README.txt file that is installed in the <ORACLE_HOME>/ord/im/admin directory on Solaris systems or <ORACLE_HOME>\ord\im\admin directory on Windows NT systems for information on new features, problems, fixes and differences between the software and the supplied documentation for Oracle interMedia.

19.2 Oracle Visual Information Retrieval

In this release, Oracle is no longer providing the Visual Information Retrieval option. Instead, we are providing a compatible API to the ORDVir object for current users of Visual Information Retrieval. Please refer to the README.txt file that is installed in the <ORACLE_HOME>/ord/vir/admin directory on Solaris systems or <ORACLE_HOME>\ord\vir\admin directory on Windows NT systems for more information.

This ORDVir-compatible API may not ship with the next version of Oracle.

19.3 Visual Information Retrieval Java Classes Do Not Work with SQLJ runtime12.zip

Applications that use Visual Information Retrieval Java Classes (contained in ordvir.zip or ordvir817.zip) cannot be compiled with the JDK 1.2 specific version of the SQLJ runtime library (contained in runtime12.zip). The javac compiler displays the following error if you try to compile an application using the OrdVir class when runtime12.zip is included in the CLASSPATH variable:

error: The method java.util.Dictionary getTypeMap() declared in nested class oracle.ord.im.OrdVirBase. _Ctx cannot override the method of the same signature declared in interface sqlj.runtime.ConnectionContext. They must have the same return type.

Specify the classes contained in the following libraries in the CLASSPATH variable to compile an application that uses Visual Information Retrieval Java Classes:

Unix:

ORACLE_HOME>/ord/jlib/ordvir.zip or ordvir817.zip
ORACLE_HOME>/ord/jlib/ordim11.zip or ordim817.zip

-- and --

ORACLE_HOME>/jdbc/lib/classes111.zip
ORACLE_HOME>/sqlj/lib/runtime.zip or runtime11.zip

-- or --

ORACLE_HOME>/jdbc/lib/classes12.zip
ORACLE_HOME>/sqlj/lib/runtime.zip

Windows NT:

ORACLE_HOME>\ord\jlib\ordvir.zip or ordvir817.zip
ORACLE_HOME>\ord\jlib\ordim11.zip or ordim817.zip

-- and --

ORACLE_HOME>\jdbc\lib\classes111.zip
ORACLE_HOME>\sqlj\lib\runtime.zip or runtime11.zip

-- or --

ORACLE_HOME>\jdbc\lib\classes12.zip
ORACLE_HOME>\sqlj\lib\runtime.zip

19.4 interMedia CBR indexing not available in Standard Edition

In Oracle9i release 9.0.1, interMedia introduced image content based retrieval (CBR) to allow applications to query the database for images that are similar to a sample image based on color, texture and structure. While this feature is available in both Standard Edition and Enterprise Edition, indexing of image content based retrieval with the new ORDImageIndex requires bit-mapped index support only available in Enterprise Edition. Attempting to use this feature with Standard Edition will result in the following error message:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine 
ORA-00439: feature not enabled: Bit-mapped indexes 
ORA-06512: at "ORDSYS.ORDIMGIDXMETHODS", line 159 

The Oracle9i interMedia User's Guide and Reference release 1 (9.0.1) neglects to mention that indexing for image content based retrieval is available only with Enterprise Edition.

20 TIME SERIES

The functions in the Time Series option migrated to SQL in Oracle8i release 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 Oracle8i release 8.1.7 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 Oracle9i SQL Reference 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 at:

 http://technet.oracle.com/products/oracle8i/

21 CONFIGURATION ASSISTANTS

The Configuration Assistants should be run by a user who is a member of the DBA group and is owner of the ORACLE_HOME.

21.1 Oracle Data Migration Assistant

21.1.1 ODMA Name Change

The name of Oracle Data Migration Assistant (ODMA) has changed to Database Upgrade Assistant (DBUA). DBUA upgrades single instance Oracle 7.3.4, 8.0.6, 8.1.7 and 9.0.1 database releases to 9.2.0. DBUA supports Real Application Clusters database upgrade: it upgrades 8.1.7 Oracle Parallel Server (OPS) database release and 9.0.1 Real Application Clusters (RAC) database release to 9.2.0.

22 ORACLE NET SERVICES

The Oracle Net readme file is located at:

$ORACLE_HOME/network/doc/README_OracleNet.htm.

23 XML DEVELOPER'S KITS (XDK)

23.1 XSU issues

The following area has a known issue:

Inheritance is not supported in the XML Schema generated by the XSU

24 ORACLE XML DATABASE

24.1 Oracle XML Database Servlet Support Limitations

In addition to the limitations mentioned in the documentation, the following classes/methods are not supported:

24.2 Use Pseudo Column SYS_NC_ROWINFO$ in Triggers

Use the pseudo column SYS_NC_ROWINFO$ within triggers to refer to the XMLType row value of a XMLType table or view. See also bug 2220374 regarding how to improve this expression's usability.

24.3 Support for XMLType in Materialized Views.

XMLType is not supported in materialized views.

24.4 Support for JNDI and Java Beans

JNDI and Java Beans are not supported in Oracle9i release 2 Oracle XML DB.

24.5 Globalization Issues: Multibyte Encodings

Multibyte encodings, including UTF-8, are not supported on this release.

24.5.1 Character Encoding in Entities

XML entities inserted using the Oracle XML Database FTP, HTTP, or WebDAV Protocol Server must be encoded using an equivalent encoding to or a subset of the database character set. For example, a document with the encoding ISO-8859-1, windows-1252, or US-ASCII can be stored in a server of WE8MSWIN1252, but the encoding windows-1252 cannot be stored in a server of WE8ISO8859P1.

Bug 2275973: HASPATH and INPATH in the Oracle XML Database do not work with 8-bit data When you use registerSchema from a BFILE, the XML schema is accepted in ASCII format only.

24.6 Import Support Under FROMUSER/TOUSER and XDB objects

The Import utility's TOUSER functionality cannot be used in Oracle9i release 2 when the FROMUSER schema has references to XDB tables, views, types, or schemas.

25 LOGMINER

In general, when a LogMiner session is started using a dictionary from the redo logs and with DDL tracking turned on, all SQL_REDO and SQL_UNDO fields in V$LOGMNR_CONTENTS are fully translated. There are no strings in either of these fields of the form ...HEXTORAW...

There is an exception to this that can occur when you start LogMiner and all of the following conditions are present:

Specifically, if a DDL event occurs beyond the end of the dictionary in the redo log stream, but before the user-specified startSCN, then that DDL event will not automatically be applied to LogMiner's internal dictionary.

The workaround is to never specify a startSCN that is greater than the endSCN of the dictionary in the redo log stream.

The following example illustrates this problem.

  1. Assume that you have the following SCNs:
  2. SCN 100 - CREATE TABLE FOO (COL1 NUMBER, COL2 VARCHAR2(14));
    SCN 200 - start of dictionary extracted to redo log files
    SCN 1000 - end of dictionary extracted to redo log files
    SCN 2000 - ALTER TABLE FOO DROP (C1);
    SCN 2050 - INSERT INTO FOO VALUES('2a, before');
  3. Also assume that all needed logfiles have been added using the DBMS_LOGMNR.ADD_LOGFILE procedure and that LogMiner has been started, as follows:
  4. execute dbms_logmnr.start_logmnr(
    startSCN => 2050,
    Options =>
    dbms_logmnr.DDL_DICT_TRACKING +
    dbms_logmnr.DICT_FROM_REDO_LOGS);
    
  5. Run the following query:
  6. SELECT SQL_REDO FROM V$LOGMNR_CONTENTS;

    The results of the query show that the SQL_REDO for the following statement is not completely translated:

    "INSERT INTO FOO VALUES('2a, before')"

    Therefore, the value in the SQL_REDO field may appear as follows:

        insert into "SCOTT"."FOO"("COL 1")
               values (HEXTORAW('32612c206265666f7265'));
    
  7. For the INSERT statement to have been returned fully translated, you could have started LogMiner in either one of the following ways:
  8. execute dbms_logmnr.start_logmnr(startSCN => 0,
                                     Options =>
    dbms_logmnr.DDL_DICT_TRACKING +
    dbms_logmnr.DICT_FROM_REDO_LOGS);

    Or

    execute dbms_logmnr.start_logmnr(startSCN => 1000,
                                     Options =>
    dbms_logmnr.DDL_DICT_TRACKING +
    dbms_logmnr.DICT_FROM_REDO_LOGS);

If it is necessary to only view SQL_REDO starting at SCN 2050, you can use an AND operator to add a predicate to any query made against V$LOGMNR_CONTENTS. For example:

    SELECT SQL_REDO
    FROM V$LOGMNR_CONTENTS
    WHERE SEG_OWNER = 'SCOTT' AND SCN >= 2050;

25.1 Behavior When Starting LogMiner Without COMMITTED_DATA_ONLY Flag

If DBMS_LOGMNR.START_LOGMNR is called without specifying the COMMITTED_DATA_ONLY flag in the OPTIONS parameter, then the following columns of the V$LOGMNR_CONTENTS view will be empty where the operation column value is DDL:

25.2 Known Issues With the Behavior of V$LOGMNR_CONTENTS Fixed View

The following is a known issue with the behavior of the V$LOGMNR_CONTENTS view:

When rollback redo operations are translated in absence of minimal supplemental logging there may be some invalid delete operations returned by V$LOGMNR_CONTENTS view.

25.3 Known Issue With Supplemental Logging

If a DML UPDATE statement uses the BUFFER hint, then supplemental logging is disabled for the update operation.

25.4 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

The ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement is not properly handling the Real Application Cluster environment. For environments with multiple instances the following workarounds are available:

25.5 Datatype Support

LogMiner does not support redos generated by compressed heap segments in addition to other documented unsupported data types.

26 ORACLE9i DATA GUARD

The following sections contain release notes about Oracle9i Data Guard:

26.1 Physical and Logical Standby Databases

26.1.1 Real Application Clusters and Data Guard During a Network Outage

This note concerns Data Guard configurations running in maximum protection mode with physical standby databases.

If you configured Oracle9i Data Guard to support a primary database in a Real Application Clusters environment and the primary database is running in maximum protection mode, a network outage between the primary database and all of its physical standby databases will disable the primary database until the network connection is restored. The maximum protection mode dictates that if the last participating physical standby database becomes unavailable, processing halts on the primary database.

If you expect the network to be down for an extended period of time, consider changing the primary database to run in either the maximum availability or the maximum performance mode until network connectivity is restored. If you change the primary database to maximum availability mode, it is possible for there to be a lag between the primary and standby databases, but you gain the ability to use the primary database until the network problem is resolved.

If you choose to change the primary database to the maximum availability mode, it is important to use the following procedures to prevent damage to your data.

Perform the following steps if the network goes down and you want to change the protection mode for the Real Application Clusters configuration.

  1. Shut down the physical standby database.
  2. Follow the instructions in Oracle9i Data Guard Concepts and Administration to change the mode from the maximum protection mode to either maximum availability or maximum performance mode.
  3. Open the Real Application Clusters primary database for general access.

  4.  

     

Later, when the network comes back up, perform the following steps to revert to the maximum protection mode:

  1. Shut down the Real Application Clusters primary database and then mount it without opening it for general access.
  2. Mount the physical standby database.
  3. Change mode on the Real Application Clusters primary database from its current (maximum availability or maximum performance) mode to the maximum protection mode.
  4. Open the Real Application Clusters primary database.

  5.  

     

26.1.2 Select from Read-Only Database Over Database Link

Using a database link to connect to a read-only database to select data from a view fails returning the errors ORA-4052, ORA-604, ORA-372, ORA-1110, and ORA-2063. You cannot use a database link to select data from a view using a cursor loop. The workaround is to avoid using the database link to query views using a cursor loop. 

This problem only occurs with a view. It does not occur if you use a SELECT statement directly from the view if you use a cursor loop to select from a table. 

This problem corresponds with Bug 2033385 and Bug 2181830. 

26.1.3 Corrections to Instructions for Creating a Logical Standby Database

To instantiate a logical standby database following the steps in Chapter 4, Section 4.1 "Preparing to Create a Logical Standby Database," you must manually build the dictionary for logical standby database.

For a Cold Backup

In Section 4.2, step 2 replace numbers 6 and 7 on page 4-11, as follows:

6. Open the primary database and build the LogMiner dictionary:

SQL> ALTER DATABASE OPEN;

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT NAME FROM V$ARCHIVED_LOG

  2> WHERE DICTIONARY_BEGIN='YES' AND STANDBY_DEST='NO';

7. Use an operating system copy utility to copy the following files to the logical standby site:

For a Hot Backup

In Section 4.2, step 2 replace number 6 (page 4-12), as follows:

6. Put the primary database in a quiesced state to obtain a starting point for building the standby database.

For Both Cold and Hot Backups

In addition, for both Cold and Hot Backup methods, replace Section 4.2, step 13 on Page 4-16 with the following:

You must register the first redo log which contains the dictionary build so that the log apply services can begin to apply data to the logical standby database. Do this by executing the ALTER DATABASE REGISTER LOGICAL LOGFILE statement on the logical standby database. When you execute this statement, specify the filename and location of the archived redo log identified during either the cold or hot backup procedure (in step 6) that you copied to the logical standby site. For example:

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE "/Oracle/remote_arc/db1/arc0.arc';

26.1.4 Creating and Opening a Logical Standby Database

Section 4.2, Step 10 on page 4-15 of Oracle9i Data Guard Concepts and Administration incorrectly states that you should use the RESETLOGS parameter when you open the logical standby database. You do not need to include the RESETLOGS parameter when you open the database. The correct code example for Step 10 is as follows:

Step 10 Open the logical standby database.

Enter the following statement to start and open the database to user access:

SQL> STARTUP PFILE=init$Log1.ora EXCLUSIVE MOUNT;       
SQL> ALTER DATABASE OPEN;

26.1.5 CREATE TABLE AS SELECT FROM CLUSTER_TABLE Not Supported by Logical Standby Databases

Do not issue the CREATE TABLE AS SELECT DDL statement on a primary database that has logical standby databases. Performing the CREATE TABLE with a clustered column results in the creation of an empty table on the logical standby database. If this happens, you will not see any other indications that the associated DML statement was errant. However, if you subsequently try to update a row in the empty table (that does not exist), the NO DATA FOUND error is returned. When this happens:

  1. Run the DBMS_LOGSTDBY.INSTANTIATE_TABLE PL/SQL procedure for the table being created
  2. Restart SQL apply operations

  3.  

     

26.1.6 ALTER TABLE SET COLUMN UNUSED Not Supported by Logical Standby Databases

Do not issue the ALTER TABLE SET COLUMN UNUSED statement on a primary database that has logical standby databases. Using the ALTER TABLE statements to set a column unused may fail and return the "ORA-26689: column datatype mismatch in LCR" error. After this error occurs, use the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure for the table listed in the DBA_LOGSTDBY_EVENTS view and then restart SQL apply operations.

While the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure will refresh the logical standby database's copy of the table,all subsequent updates to the table will fail.

To work around this problem:

  1. Drop the column on the primary database.
  2. On the logical standby database, run the DBMS_LOGSTDBY.INSTANTIATE_TABLE PL/SQL procedure to re-create the table
  3. Restart the SQL apply operations on the logical standby database

  4.  

     

26.1.7 Supporting OUTLINES Transactions On Logical Standby Databases

Issuing the CREATE OUTLINE, ALTER OUTLINE, or DROP OUTLINE DDL statements on a primary database that has logical standby databases may result in an "ORA-00600 internal error code" message being returned. (Query the DBA_LOGSTDBY_EVENTS view to see the actual error message.)

If an OUTLINE-related DDL statement stops SQL apply operations to the logical standby database, you can work around the problem by:

  1. Querying the DBA_LOGSTDBY_EVENTS view to obtain the transaction ID
  2. Providing the transaction ID information to the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure
  3. Restarting SQL apply operations

  4.  

     

To prevent SQL apply operations from stopping during OUTLINE-related DDL work on logical standby databases, Oracle Corporation recommends executing the following.

EXECUTE:

DBMS_LOGSTDBY.SKIP('CREATE OUTLINE','%','%');EXECUTE

DBMS_LOGSTDBY.SKIP('ALTER OUTLINE','%','%');EXECUTE

DBMS_LOGSTDBY.SKIP('DROP OUTLINE','%','%');EXECUTE

DBMS_LOGSTDBY.SKIP('DML','OUTLN','%');EXECUTE

DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','OUTLN','%');EXECUTE

26.1.8 CREATING NESTED TABLES on Logical Standby Databases

For this release of Oracle9i Data Guard, logical standby databases do not support creating nested tables. Attempting to create a nested table on the logical standby database will result in the "ORA-02320: failure in creating storage table for nested table column" error being returned. If the DDL statement causes SQL apply operations to stop on the logical standby database, provide the transaction ID information (obtained by querying the DBA_LOGSTDBY_EVENTS view) to the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure and restart SQL apply operations. Although you can create the table on the logical standby database, rows will not be maintained for it.

26.1.9 DDL Statements that Use DBLINKS

On logical standby databases, SQL statements such as the CREATE TABLE tablename AS SELECT * FROM bar@dblink, issued may fail. Avoid issuing this type of statement, because there are many errors that can arise from this type of operation.

When the statement is executed on the logical standby database, it will access the database link at that time and it's not possible to know if the information on the logical standby database is the same as it was at the time the statement was executed on the primary database. For example, additional columns may have been added or dropped that would make it impossible to apply the rows that follow. Assuming the network was set up so that the initial creation succeeded, you might see the following error: "ORA-26689: column datatype mismatch in LCR" for a table containing nested table columns. Also, the "ORA-02019: connection description for remote database not found" error may be returned if the database link or the TNS service was undefined on the logical standby database.

When this happens, use the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure for the table being created and then restart SQL apply operations.

26.1.10 Logical Standby Databases On the Same Node as the Primary Database

If a logical standby database is located on the same computer system as the primary database, it's likely that both Oracle instances will have access to the same directory structure. There are Oracle commands that reuse datafiles. If Oracle commands that reuse datafiles are applied on the primary database, the commands may also be applied on the logical standby database. If that happens while the primary database is shut down, it's possible for the logical standby database to claim the file as part of its database and possibly cause damage to the primary database.

For this reason only, Oracle Corporation recommends using the following settings when running the primary and logical standby databases on the same computer system.

EXECUTE DBMS_LOGSTDBY.SKIP('ALTER TABLESPACE');

26.1.11 NCLOB Data Type Unsupported by Logical Standby Databases

For this release, tables with columns of type NCLOB cannot be maintained on logical standby databases. However, these columns will not be listed in the DBA_LOGSTDBY_VIEW view as unsupported for logical standby databases and the tables will not be skipped automatically by SQL apply operations on the logical standby database.

Thus, if a table on the primary database has a column of type NCLOB, execute the following PL/SQL statement on the logical standby database to skip the table:

DBMS_LOGSTDBY.SKIP('DML','myschema','mytabwithnclob');

26.1.12 Connecting Through Oracle Enterprise Manager

When you use Oracle Enterprise Manager to connect to a remote logical standby database, the O7_DICTIONARY_ACCESSIBILITY initialization parameter must be set to TRUE. This is required so that non SYSDBA users can connect to a logical standby database.

26.1.13 ALTER TABLE ADD COLUMN Statements May Fail

Do not issue an ALTER TABLE statement on a primary database that has a logical standby database to add a column. This statement may fail and return the "ORA-26689: column datatype mismatch in LCR" error.

Although the DBA_LOGSTDBY_EVENTS view does not provide enough information to indicate whether this error was caused by an ADD COLUMN request, you may want to skip the transaction if you believe this is the case. To skip the transaction, perform the following steps:

  1. Query the DBA_LOGSTDBY_EVENTS view to obtain the transaction ID (in the XIDUSN, XIDSLT, XIDSQN columns) information.
  2. Run the DBMS_LOGSTDBY.SKIP_TRANSACTION PL/SQL procedure, providing the transaction ID information as input.

  3. Alternatively, you can use the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure to instantiate the table listed in the DBA_LOGSTDBY_EVENTS view.

  4. Restart SQL apply operations on the logical standby database.

  5.  

     

26.1.14 Invalid Dictionary Error

The "Invalid Dictionary Error" message may be returned when a new logical standby database (or a database that has just been moved into the logical standby role as a result of a switchover operation) is interrupted while it is in the initialization phase. You cannot recover from this error. The only solution is to re-create the logical standby database from the primary database.

If you query the V$LOGSTDBY view and it shows the "ORA-16115 loading LogMiner dictionary data" message for one of the processes indicated in the process ID (PID) column, do not attempt to perform a SHUTDOWN ABORT operation. Logical standby databases can recover from an ABORT operation if other activity is underway.

26.1.15 DBA_LOGSTDBY_PROGRESS View on Real Application Clusters

Attempts to query the DBA_LOGSTDBY_PROGRESS.APPLIED_TIME column when running in an Oracle Real Application Clusters environment will return the "ORA-01427 single-row subquery returns more than one row" error. In Oracle Real Application Clusters environments, you should explicitly specify your column selections when selecting from the DBA_LOGSTDBY_PROGRESS view and avoid selecting the APPLIED_TIME column.

26.1.16 OPEN_CURSORS Initialization Parameter

Oracle Corporation recommends setting the OPEN_CURSORS initialization parameter to 600 or greater on a logical standby database. When the OPEN_CURSORS initialization parameter is set to less than 600, it may result in the SQL apply operations to the logical standby database failing, returning the "ORA-01000 maximum open cursors exceeded" error message.

26.1.17 Registering a Logical Logfile Returns ORA-01289

If the primary database from which a new logical standby database is created was previously a standby database, the "ORA-01289 cannot add duplicate logfile string" error message may be returned when you register the first logfile. To work around this problem, issue the following SQL statements:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL SCN 1;

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 'yourlogfile';

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

26.1.18 Granting Privileges to SYS Returns ORA-01749

If SQL apply operations fail on a logical standby database and return the "ORA-01749 you may not GRANT/REVOKE privileges to/from yourself" error message, it is probably caused by an attempt to execute a GRANT DDL statement to grant a privilege or a role to SYS. To verify that this is the problem, query the DBA_LOGSTDBY_EVENTS view to see the failed DDL statement along with it's transaction ID (shown in the XIDUSN, XIDSLT, XIDSQN columns) in the view. To fix the problem:

  1. Execute the DBMS_LOGSTDBY.GUARD_BYPASS_ON PL/SQL procedure
  2. Execute the failed DDL statement
  3. Enter the XIDUSN, XIDSLT, XIDSQN column values as parameters to the DBMS_LOGSTDBY.SKIP_TRANSACTION PL/SQL procedure
  4. Restart SQL apply operations on the logical standby database

  5.  

     

26.1.19 DBMS_LOGSTDBY.BUILD Returns ORA-16108

If the DBMS_LOGSTDBY.BUILD procedure returns an "ORA-16108 database is no longer a standby database" error message, execute the following procedure in it's place:

EXECUTE DBMS_LOGMNR_D.BUILD(options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

26.1.20 Refreshing Materialized Views on a Logical Standby Database

Materialized views must be refreshed separately on a logical standby database using the GUARD_BYPASS_ON and GUARD_BYPASS_OFF procedures of the DBMS_LOGSTDBY package. For example:

EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;

EXECUTE DBMS_MVIEW.REFRESH ( 'BMVIEW', 'F', '',TRUE,FALSE,0,0,0,FALSE);

EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
 
See Also:

Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the DBMS_LOGSTDBY package

Also, if you are using the DBMS_LOGSTDBY.APPLY_SET procedure but you are not using the default value of FULL for the TRANSACTION_CONSISTENCY parameter, you should stop SQL apply operations before refreshing materialized views on the logical standby database.

26.1.21 Apply Operations Stopped with Apply Servers Waiting On Other Apply Servers

For logical standby databases, if you set the MAX_SERVERS initialization parameter to a value greater than 5 and you use the default TRANSACTION_CONSISTENCY parameter value of FULL, then an apply process may wait an indefinite period of time if there is a transaction that is dependent on another transaction with a higher SCN. When an apply process detects such a dependency, information is recorded in the alert log and apply process trace file for the database.

To avoid such a situation, consider increasing the PCTFREE attribute value of the storage parameter for the schema object.

See Also:

Oracle9i Database Administrator's Guide for information about managing space for schema objects and the Oracle9i SQL Reference for syntax and details about the PCTFREE storage attribute parameter.

26.1.22 Initially Starting Logical Standby Fails with ORA-1652

LogMiner uses temporary tables to stage its internal dictionary as it is mining it out of the redo stream. If the temporary tablespace is not configured adequately (for example, it either does not have enough space or is not backed up by a temporary file), the initialization phase of a logical standby database will fail with the following errors:

ORA-1652:  unable to extend segment by 512 in tablespace TEMP
ORA-01332: internal Logminer Dictionary error

To work around the problem, increase the size of the temporary tablespace and start the logical standby database.

26.1.23 Switchover Operation Returns ORA-16104

After performing a switchover operation involving a logical standby database, the "ORA-16104 invalid Logical Standby option requested" error message may be returned. For example:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY

*

ERROR at line 1:

ORA-16104: invalid Logical Standby option requested

This is because immediately after a switchover operation involving a logical standby database, some additional work occurs in the background. This background work typically lasts a few minutes. Until this background work has completed, you cannot perform another switchover operation.

26.2 Data Guard Broker

26.2.1 Verify Warning Is Incorrect

Occasionally, when performing Data Guard broker operations, you may see the following message in the Data Guard configuration log file:

RSM: error in accessing metadata.

You can safely ignore this message. It is benign and does not indicate any operational problem with the broker or the databases included in the broker configuration.

26.2.2 Using Multiple Domain Names in a CONNECT Command

When connecting to a database using the Data Guard command-line interface (DGMGRL), a connect string that includes multiple domains separated by dots may result in the "invalid username/password; logon denied" message being returned. For example, the following CONNECT command can be problematic:

DGMGRL> CONNECT sys/change_on_install@standby.acme.com

Use one of the following solutions to work around this problem:

  1. Use double quotation marks. For example:
  2. DGMGRL> CONNECT sys/change_on_install@"standby.acme.com"
  3. Use connect strings that do not have multiple domains. This solution might require that you edit the TNSNAMES.ORA file.

  4.  

     

26.3 Oracle9i Data Guard Concepts and Administration

This section lists additions and corrections to Oracle9i Data Guard Concepts and Administration, Release 2 (9.2).

26.3.1 Database Attributes Not Supported By Logical Standby Databases

In Chapter 4, Section 4.1 "Preparing to Create a Logical Standby Database," the list of unsupported items should include the following:

26.3.2 Manually Finding an Archive Gap

In Appendix B, Section B.3.2 "Determining Whether an Archive Gap Exists," the text recommends querying the V$ARCHIVE_GAP view to determine whether there is an archive gap.

Instead, you should query the V$ARCHIVED_LOG and V$LOG views, which more accurately reflect if there is a gap on a particular standby destination. For example, the following query shows that there is a difference in the RECD and SENT sequence numbers for the destination specified by DEST_ID=2, indicating that there is a gap:

SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM

  2> V$ARCHIVED_LOG R, V$LOG L WHERE

  3> R.DEST_ID=2 AND L.ARCHIVED='YES';


LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
            7            10

Use the following query to determine the names of the archived redo logs on the local system that must be copied to the standby system that has the gap:

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND

  2> SEQUENCE# BETWEEN 7 AND 10;


NAME
--------------------------------------------------------------------------------
/primary/thread1_dest/arcr_1_7.arc

/primary/thread1_dest/arcr_1_8.arc

/primary/thread1_dest/arcr_1_9.arc

/primary/thread1_dest/arcr_1_10.arc

26.3.3 TRANSMIT_MODE Column of the V$ARCHIVE_DEST View

In Chapter 14 and in the New Features preface, the new values of the TRANSMIT_MODE column of the 
V$ARCHIVE_DEST view should be as follows:

The documentation incorrectly states that the new column names are SYNC=PARALLEL, SYNC=NOPARALLEL, and ASYNC.

26.3.4 DBMS_LOGSTDBY.INSTANTIATE_TABLE

The DBMS_LOGSTDBY PL/SQL procedure does not support the BLOB data type even though BLOB data types are supported by logical standby databases. Also, the order of the parameters for the INSTANTIATE_TABLE procedure is incorrect in the Oracle9i Data Guard Concepts and Administration and in the Oracle9i Supplied PL/SQL Packages and Types Reference. The correct parameter order is as follows:

DBMS_LOGSTDBY.INSTANTIATE_TABLE

     schema_name IN VARCHAR2,

     table_name  IN VARCHAR2,

     dblink      IN VARCHAR2);

27 ORACLE TRACE

27.1 Oracle Trace "Per Session" Collection Incompatible with Oracle Shared Server (Bug 2034863)

If only selected Oracle database session(s) are enabled for Oracle Trace data collection under Oracle Shared Server, using the ALTER SESSION command, then incomplete or irrelevant database event data may be collected by Oracle Trace. This will occur only if an Oracle Trace-enabled database session migrates to another shared server process which has not been collecting, and vice versa. This results from Oracle Trace collection being process-based, rather than session-based; and the process collection state (that is, whether it is collecting or not) is not correctly migrated during shared server session migration.

Conversely, this is not a problem if either Oracle Trace collection has been enabled for all database sessions (or none of them), or for non-shared server Oracle database environments.

28 OLAP

28.1 OLAP PS$ TABLE

One of the columns in the OLAP PS$ table was declared with too low a precision. The 10,000th attempt to update an analytic workspace will result in this error message:

ORA-00604: error occurred at recursive SQL level 1
ORA-01438: value larger than specified precision allows for this column 

To avoid this problem, connect to the database as the sysdba user and execute the following SQL statement:

ALTER TABLE PS$ MODIFY (PSGEN NUMBER(10))

28.2 OLAP and Installation

If you are planning to install Oracle9i release 9.2.0.1 into an Oracle Home containing Oracle 9i release 9.0.1 OLAP Services, and you plan to select either the General Purpose or Data Warehousing form of Enterprise Edition, or a Custom Install selecting the OLAP Option, you need to explicitly deinstall 9.0.1 OLAP Services before proceeding with the Oracle9i release 9.2.0.1 install.

28.3 Oracle9i OLAP DML Reference Help

28.3.1 ALLOCATE Command

The following sentences from the description of the BASIS basisname argument are incorrect.

If you do not specify a basis, then the ALLOCATE command uses the variable specified by the TARGET keyword as the basis. If you specify a variable as basisname and you do not specify a target variable, then ALLOCATE uses basisname as the target.

The following is correct.

If you do not specify a basis object, then the ALLOCATE command uses the source object as the basis.

The name of the SOURCELIST argument should be SOURCE, so the syntax of the ALLOCATE command is the following.

ALLOCATE source [SOURCE conjoint] -
[BASIS basisname [ACROSS dimname]] -
[TARGET targetname [TARGETLOG targetlogname]] -
USING aggmap [ERRORLOG errorlogfileunit]

Note that the ERRORLOG argument is after the USING argument and not before it.

28.3.2 ALLOCMAP Command

The "Editing an aggregation map in the Worksheet editor" section of the ALLOCMAP Command Examples incorrectly states that you can edit an ALLOCMAP type aggregation map with the OLAP Worksheet editor.

You cannot use the OLAP Worksheet edit window to either create or revise an aggregation map for allocation. To add contents to an ALLOCMAP type aggregation map, you must use the ALLOCMAP command.

28.3.3 AW Command

The following information should be added for the CREATE keyword.

When you create an analytic workspace with the CREATE keyword, Oracle OLAP automatically executes a COMMIT command.

If you want others to use the workspace, you must give them access to the relational table in which the workspace is stored. The name of the table is "AW$" followed by the workspace name that you specified in your AW CREATE command. To give read access to another user, execute a command like the following one in SQL. In this example, the workspace name is "demo" and the user's name is "scott".

GRANT SELECT ON aw$demo TO scott

To give write access to another user, execute a SQL command like the following one.

GRANT UPDATE ON aw$demo TO scott

As in any SQL GRANT command, you can specify a group or role instead of a user.

28.3.4 DEFINE Command

The description of the "name" argument incorrectly states that an object name must consist of 1 to 16 characters. The correct maximum is not 16 characters; it is 64 characters.

28.3.5 GROUPINGID Command

In the example, the definitions for geog.gid and geography.newparent are incorrect.

The following definitions are correct:

DEFINE geog.gid INTEGER VARIABLE <geography> 
DEFINE geography.newparent RELATION geography <geography>

28.3.6 HIERHEIGHT Command

In the example, the definitions for chan.gid, prod.gid, geog.gid, time.gid, chan.hierrel, prod.hierrel, geog.hierrel, time.hierrel, chan.inhier, prod.inhier, and geog.inhier are incorrect.

The following definitions are correct:

DEFINE chan.gid INTEGER VARIABLE <channel> 
DEFINE prod.gid INTEGER VARIABLE <product> 
DEFINE geog.gid INTEGER VARIABLE <geography> 
DEFINE time.gid INTEGER VARIABLE <time> 
DEFINE chan.hierrel RELATION channel <channel chan.lvldim> 
DEFINE prod.hierrel RELATION product <product prod.lvldim> 
DEFINE geog.hierrel RELATION geography <geography geog.lvldim> 
DEFINE time.hierrel RELATION time <time time.lvldim> 
DEFINE chan.inhier BOOLEAN VARIABLE <channel> 
DEFINE prod.inhier BOOLEAN VARIABLE <product> 
DEFINE geog.inhier BOOLEAN VARIABLE <geography> 
DEFINE time.inhier BOOLEAN VARIABLE <time>

28.3.7 RELATION Command (Allocation)

The keywords for the ARGS argument should be ADD or ASSIGN and not ADDTO or ASSIGN.

28.4 YESSPELL and NOSPELL options

The YESSPELL and NOSPELL options should be marked as read-only. Their values reflect the NLS parameter settings for the database. You cannot change their values using these OLAP DML options.

29 ORACLE INTERNET DIRECTORY

29.1 Post-Upgrade Step for iPlanet Synchronization

As part of the upgrade process, integration profiles for iPlanet synchronization, namely, iPlanetImport and iPlanetExport, are created. For these profiles to be configured and used for synchronization, they need to be added to configset1. If the profiles are available in configset1 as part of upgrade, it will be shown in Oracle Directory Manager under Integration Server as part of Configuration Set1. If the profiles are not available, then they can be added by using ldapmodify as follows:

ldapmodify -h <OID Host> -p <OID Port>  -D <OID Super-user> -w <OID Super-user password> <<EOF
dn: cn=configset1,cn=metadird,cn=configsets,cn=oracle internet directory
changetype: modify
add: orclodipconfigdns
orclodipconfigdns: orclodipagentname=IPlanetImport, cn=subscriber profile, cn=changelog 
subscriber, cn=oracle internet directory
orclodipconfigdns: orclodipagentname=IPlanetExport, cn=subscriber profile, cn=changelog 
subscriber, cn=oracle internet directory
EOF

30 OPEN BUGS

30.1 Migration Known Bugs

30.1.1 Unable to Start the Database When Trying to Upgrade to Oracle9i Release 9.2

Bug 2346907

If you install Oracle9i release 9.2 server on top of an existing ORACLE_HOME, then the existing databases can be started only using the startup migrate option. If you try to start an existing database without the migrate option, then you'll get 'ORA-01092: ORACLE instance terminated. Disconnection forced'.

30.2 Advanced Queuing Known Bugs

Bug 2248652

Enqueue of JMS types and XML types does not work with Sys.Anydata streams queues.

Workaround

Call dbms_aqadm.enable_jms_types(<queue_table_name>); after dbms_streams_adm.set_up_queue(). However enabling a streams queue for these types may affect import/export of the queue table. Refer to bug 2250422.

Bug 2134069

When messages are propagated to remote destination and remote Queue is not created (or disabled for enqueue or no subscribers are added, and so on) the message becomes UNDELIVERABLE and is not moved to the exception queue.

Workaround

Create the remote queues before scheduling propagation to that destination.

Bug 2188251

When there are multiple database links from the same source q to the same destination, internal errors [kwqpudpse: subelem] or [kwqprcvpbq: dest hwm < ] may occur.

Workaround

Use a single database link to propagate messages from one source to a destination.

Bug 2261168

A problem occurs if apply with rule set is altered and a drop propagation is issued. This causes the drop propagation to hang.

Workaround

Drop the apply and issue drop propagation. The drop propagation will succeed, and the user can recreate the the apply and create propagation again.

Bug 2265794

This problem occurs when the application issues a dequeue with a next message navigation mode. AQ keeps the cursors open until all the dequeues are complete. This results in the error snap shot too old [1555] being raised.

Workaround

Issue fewer number of dequeues with next_message navigation mode. (that is, issue a dequeue with first_message navigation mode more often).

Bug 2133320

Create an Oracle 8.1-style multi consumer queue and propagate using loopback database link to multiconsumer (80,81) and single conssumer (80,81 ) queues. Now, Drop queue/queue table hangs.

Workaround

Increase the inittrans parameter in the storage clause of create queue table.

Bug 2201535

This occurs only when the queue name provided to set_up_queue is greater than 24 characters, over the supported limit.

Workaround

Specify queue names within the supported limit.

Bug 2205405

Http propagation for anydata queues not supported for Oracle9i release 9.2.

Workaround

None.

Bug 2206689

Http propagation between XMLType queues not supported inOracle9i release 9.2

Workaround

None.

Bug 2223302

Http propagation of XML queues not supported for Oracle9i release 9.2 This occurs when propagation is scheduled between XMLType queues over http.

This is a duplicate of bug 2206689.

Bug 2235056

Currently, the queue owner must be enabled as a database user in order to enqueue or dequeue into their own secure queue (using dbms_aqadm.enable_db_privs).

Workaround

Use dbms.aqadm.enable_db_access to explicitly map the agent to the queue owner.

30.3 Streams Known Bugs

Bug 2346481

Row migration rules incorrectly filter out some logical change records (LCR), leading to incorrect results.

Workaround

Modify rules for row migration manually to use the old values of the LCR if the new values are not present.

Bug 2341596

When configuring Streams capture, if multiple archive log destinations are specified, the creation will fail with an ORA-1422 error.

Workaround

Do not specify multiple archive log destinations at a capture site.

Bug 2271626

If there are tables with delete cascade foreign key relationships among them and if the replication and subsequent apply of a delete DML on the parent table raises an ORA-1403 (row not found) at the destination sites, then you have run into this bug.

Workaround

Do not use delete cascade constraints among tables being replicated. Instead replace the delete cascade constraints with a before row trigger on the "parent" tables to delete the child rows.

Bug 2271669

When global DDL and DML rules are used, a create outline DDL will force the capture process to stop with an error saying "datatype not supported". The problem is that the create outline DDL inserts rows into the outln.ol$ table which contains a long column (which is not supported by capture).

Workaround

Modify the filtering rules to exclude DML statments on the outln.ol$ tables (and other similar outln.* tables).

Bug 2349883

Interrupting a DBMS_AQADM.UNSCHEDULE_PROPAGATION command can cause an ORA-600 error when you try to reschedule the propagation using DBMS_AQADM.SCHEDULE_PROPAGATION.

Workaround

Avoid interrupting the UNSCHEDULE_PROPAGATION command. The ORA-600 [kwqpaspse2c: invalid status] error can be cleared by restarting the database.

Bug 2238498

When global level rules are in place to replicate DDL in a Streams environment, DDL on public objects (e.g public synonyms) are placed in the apply error queue with an ORA-26687 error (instantiation scn not found).

Workaround

At the destination sites, set a schema level instantiation scn for the user PUBLIC.

Bug 2265773

If a Streams environment is set up for replication of GLOBAL DDL (such as grants and create user DDL) and if the user creates a user via a grant DDL (e.g grant connect, resource to new_user identified by ..) which is applied at the destination sites, then subsequent DDL/DML in the newly created user are not applied at the replicated destination and result in an ORA-26687 (instantiation scn not found).

Workaround

There are two workarounds:

a) Create any new users with the create user DDL. For example, give any desired grants to the new_user in separate grant DDL statements

b) If the new user has already been created via the grant DDL, then at the destination sites (for this DDL), set the schema level instantiation scn for this newly created user.

Bug 2345609

Streams capture and apply processes continue to run in restricted mode.

Workaround

Stop the capture and apply processes manually while in restricted mode.

Bug 2259124

DDL executed using execute immediate is not successfully applied at the destination sites in a Streams replication environment.

Workaround

Do not execute DDL using execute immediate.

Bug 2189441

Calling DBMS_PROPAGATION_ADM.DROP_PROPAGATION with the value of the parameter PROPAGATION_NAME specified in lowercase causes ORA-23601:PROPAGATION_NAME %prop_name% does not exist .

Workaround

Specify the PROPAGATION_NAME parameter value as an uppercase name.

Bug 2346811

If the value of the parallelism parameter for the apply process is greater than 1 and a transformation to change the schemaname of the LCR is specified, an apply error occurs.

Workaround

Either set the apply parallelism value to 1 or use a DML_HANDLER to change the schemaname of the LCR.

30.4 DBMS_STATS Package

User-defined statistics cannot currently be stored in an external table, so any DBMS_STATS operation which involves user-defined statistics (such as setting domain index statistics) will work incorrectly if an external table is specified as the source or target (the stattab argument is not null).

This restriction corresponds to bugs 2085026 and 2087959.

30.5 Logminer Known Bugs - Recovery

Bug 2259246

Querying v$logmnr_contents view returns the ORA:1280 error if the mining session was started with dbms_logmnr.continuous_mine option in conjunction with a specified endScn.

Workaround

Do not specify endScn parameter in start_logmnr() procedure call if dbms_logmnr.continuous_mine option is also specified. Similar functionality can be obtained by specifying the endTime parameter in start_logmnr() procedure and specifying the endScn in the where clause of the query.

30.6 External Table Known Bugs

Bug 1562814

Selecting column of rowid type from external tables fails.

Bug 1678633

ORA-01984 auditing directory object for write fails.

Bug 1679704

Audit trail for external tables not being recorded.

Bug #2227237

When specifying identifiers (for example, column or table names) in the external table access parameters certain values are considered to be reserved words by the access parameter parser. If a reserved word is to be used as an identifier it must be quoted using double quotes.

The following are considered to be reserved words by the access parameter parser:

and

fixed

position

are

float

raw

badfile

floattype

records

blanks

for

recnum

by

from

row

big

file

rtrim

bytes

in

second

bendian

interval

setid

bytestr

is

sizes

badfilename

integer

string

characterset

little

sign

column

load

scale

constant

lobfile

skip

concat

ltrim

terminated

characters

lrtrim

time

counted

logfile

timestamp

char

fields

options

charset

ldrtrim

processing

charstr

lendian

timezone

collength

logfilename

to

data

local

transforms

date_format

localtzone

termeof

date

make_ref

termws

day

mask

unsigned

debug

missing

uinteger

defaultif

month

values

delimited

missingfld

variable

decimal

newline

varrawc

double

no

varchar

doubletype

not

varraw

datemask

notequal

varcharc

discardfile

notrim

vlenlen

delimitby

null

vmaxlen

dscfilename

nullif

when

enclosed

nodiscardfile

whitespace

equal

nobadfile

with

endian

nologfile

year

eof

notermby

zoned

end_extended_parameters

oid

sign

extended_io_parameters

or

scale

exit

oracle_date

termws

external

oracle_number

externalkw

optionally

field

optenclose

Bug 2244083

The access parameter parser incorrectly handles the parsing of the VARCHAR, VARCHARC, VARRAW, and VARRAWC datatypes. If only a maximum length is specified without the "length of the length field" the parser fails, returning a syntax error to the user.

Workaround

Have the user specify a CHAR datatype

30.7 PL/SQL Known Bugs

Bug 1727399

In INSERT and UPDATE statements, if a function is used as the value for an object type column (or attribute), the function may potentially be invoked several times for each row. To avoid this overhead, the function should be declared to be DETERMINISTIC. Also, INSERT..VALUES statements should be rewritten as INSERT..SELECT ... FROM DUAL to avoid multiple calls to the function.

Bug 1720400

Errors caused by improperly configured native compilation are not currently trapped and sent to error$ or a trace file. Instead, they go to the stderr on the computer where Oracle is running. To diagnose such errors, you must run your client on the same computer as Oracle is running. (These errors are typically configuration errors, thus they should not occur after an initial correct ncomp setup.)

30.7.1 Bugs Related to Invoker's Rights within SQL

We have identified some problems in our mechanisms for choosing which userid or roles to use when executing certain constructs from within a SQL statement.

These concerns affect uses within a SQL statement of the DEREF operation and calls from a SQL statement to "invoker's rights" PL/SQL, Java, and callout C procedures. Calls to both user-written invoker's rights procedures and to Oracle-provided ones such as UTL_REF and DBMS_LOB are of concern.

Bug 1757894

When a SQL statement contains any of these affected operations which are meant to execute using their caller's privileges, it is somewhat unpredictable which userid and roles will be used if the statement is not parsed, executed, and fetched within identical privilege environments.

The privileges of the owner of an invoker's rights routine may be used instead of those of the caller to it when performing any of the affected operations from within a SQL statement, if the invoker's rights routine uses a separate FETCH statement or a cursor FOR loop construct on that SQL statement.

Bug 1759602

Views are not acting predictably as a definer's rights layer. Depending on circumstance either the view owner's privileges or the privileges of the code executing a query or DML statement on the view may be used when executing the affected operations.

Bug 1739477

When executing a Java or callout C routine through a definer's rights call spec, enabled session-level roles may sometimes be used when doing privilege checks. This is not desirable; a definer's rights routine should behave the same regardless of its caller's privileges.

Bug 2237890

If you are using JDB and the JDWP-based PL/SQL debugger to debug a PL/SQL program, you may see Oracle internal error 17278 when typing JDB command quit immediately after an exception occurred.

Workaround

There is no direct work around for this. Do not do quit immediately after an exception occurs; try doing step then doing quit.

Bug 2170121

In Oracle9i release 9.2, you can grant and revoke the DEBUG object privilege (as described in Table 17-3 of the Oracle9i SQL Reference), but Oracle does not yet meaningfully use this privilege. Debugging of PL/SQL and Java routines using the JDWP interface is currently allowed only if the session user has debugging permission on all objects, as granted through the DEBUG ANY PROCEDURE system privilege.

The DEBUG ANY PROCEDURE system privilege is currently checked along with the DEBUG CONNECT SESSION system privilege when deciding whether to allow a connection to be established between a database session and a JDWP-based debugger; a connection will only be allowed at all if the user has both of these system privileges.

Bug 2191174

The new JDWP-based debugging mechanisms for PL/SQL and Java do not yet follow our desired rules for single-stepping behavior. In particular:

a) Single-stepping will currently stop within PL/SQL program units that are compiled without debug information. In future releases it is likely that we will tune stepping to stop only within program units that were compiled with debug information and for which the current debugging session has DEBUG privilege.

b) At times the otherwise "correct" place to stop when single-stepping is a place at which we cannot actually stop. This may be due to it being in natively-compiled code, or due to a lack of sufficient debug information and/or privilege. In these cases, our current implementations of various subcomponents differ in the rules they apply regarding where to actually next stop instead. There are also similar differences in the rules used to decide where to actually stop on a step up or next/step over kind of command that causes the current frame to finish. In future releases it is likely that we will tune the various subcomponents to agree on their rules for such cases, causing some visible changes in where stops actually occur.

Workaround

Both application and development tool developers should avoid becoming dependent on our existing behavior in such cases. We consider the existing behavior simply incorrect and are likely to adjust it in future without providing any backwards compatibility mechanism.

Application developers should consider using the next or step over functionality exposed through their debugger, or setting a breakpoint explicitly where they next desire a stop and letting the program run freely until it hits that breakpoint, if inconveniencing stops are occurring within code that was compiled without debug information or is otherwise not of interest to the developer.

Bug 1894991

When using PL/SQL native compilation on NT, the program might get a stack overflow exception when the call stack is too deep either because of recursion or because of a long call chain.

Workaround

The workaround is to compile such highly recursive programs to interpreted mode.

Bug 1961723

The native compilation of very large PL/SQL procedures/functions fails on Windows NT because of a compiler limitation in Visual C++ compiler.

Workaround

In such cases, split the procedure into smaller procedures.

30.8 Restriction on Real Application Clusters Guard I

Bug 2232057

The server parameter file (SPFILE) cannot be used to start up the database in a Real Application Clusters Guard I environment. Use the parameter file (PFILE).

30.9 Replication Known Bugs

Bug 1788639

Materialized views based on user-defined types may fail with an ORA-00600 [4882] error if the data or index exceeds a single extent.

The workaround is to specify an initial storage extent size for both the materialized view and the index in the create statement so that the initial extent for each will be large enough to contain all the data of the materialized view and index in a single extent.

For example, if the following statement produces an ORA-600 [4882] error:

create materialized view foo refresh fast for update as select * from bar;

then modify the create statement similarly to the following:

create materialized view products

storage (initial 1M)

using index storage (initial 1M)

refresh fast for update as

select * from bar;

30.10 OCI Known Bugs:

Bugs 1634613 and 165200

Compatibility problem for AL16UTF16 NCHAR between the Oracle9i server and Oracle8i client:

There will be error for connection. OCI fixed the problem by changing the protocol, but the fix cannot be backported to Oracle8i. NLSRTL provides a workaround for it by backporting the AL16UTF16 character set to Oracle8i. Please see bug 1634613 and Bug 165200 (backport).

Bug 1733163

Clients of OCI connection pooling should be aware that it is possible to run into an artificial deadlock with competing DML statements.

Consider an example with two threads operating on a pool of one physical connection. Thread 1 updates a row and sleeps. Thread 2 tries to update the same row and blocks holding the only connection in the pool. Thread 1 comes back to commit the update and blocks on the pool. This scenario with one connection in the pool and two threads can be generalized to a pathological case of 'n' connections and 'm' threads where m > n.

The current workaround is to increase maximum connections in the pool so that threads holding locks will be allowed to proceed.

Bug 2245006

When the OCCI interface is used to populate a nested table or varray column with more than 245 elements and then SQL*Plus is used to query these columns, an internal error is generated.

Workaround

The above columns can be queried by using the OCCI interface.

Bug 1777165

If an OCI application creates a subtype and works on it in the same session, the following scenario will not work:

- First, select instances of super-type.

- Second, create the subtype.

- Now selecting instances of the subtype, in this same session, can lead to errors or incorrect results.

Workaround

After creating the subtype, reconnect the session to access instances of the new subtype correctly.

Bug 2114547

An OCI application which manipulates objects should not create more than 255 physical connections per OCI environment handle. If it does, an ORA-21522 error is raised during OCI calls manipulating objects.

Workaround

Use multiple OCI environment handles or use OCI?s connection pooling feature. The connection pooling feature will give you a large number of logical connections (sessions), mapped to fewer physical connections.

30.10.1 OCI - Failover

Bug 1701939

Failover does not work with OCI scrollable cursors

Workaround

None

30.10.2 OCI - Globalization

Bug 1691549

When inserting a string into a buffer with insufficient length, insertion finishes without warning although truncation has happened. In such scenarios calling SQL LENGTH() function will return incorrect value.

Workaround

Use larger buffers.

30.11 JDBC Known Bugs

Bug 1640110

JDBC-OCI driver will hang applications when executing a query with one extra double quote and environment variable, NLS_LANG, is set. Problem occurs in both single and multibyte database character set.

30.12 XML Known Bugs

XML Parser for C Known Bug

Bug 1684170

NLS: XSL:SORT does not sort base on XSL:LANG.

30.13 XML DB Known Bugs

Bug 2138262

SQLX functions currently do not accept BFILE parameters

Bug 2146333

Functional indexes are not picked up by queries on schema-based tables.

Workaround

Use a hint specifying the table and the index to be used for the query.

Bug 2194827

Oracle9i release 9.2.0.1, allows inserts of AnyData containing XMLtype into AnyData columns directly. This is invalid, since the XMLtype image may contain LOB or other data that cannot be stored in a packed AnyData form. This could lead to loss of data when trying to retrieve the same.

Workaround

No user visible workaround.

30.14 XML Type Known Bugs

Bug 2243369

ALTER TABLE MOVE TABLESPACE for an XMLType table raises an ORA-600 error.

Workaround

Create the table in the right tablespace during CREATE TABLE.

Bug 2219728

You cannot modify SYS_NC_ROWINFO$ in before-row triggers for XMLType tables.

Bug 1718333

XMLType is not supported through database links.

Bug 2100834

Spurious rows may be inserted into out-of-line tables, if there are triggers.

Bug 2211114

XMLType.toObject() does not handle subtypes.

Bug 2233067

Query rewrite on non-schema-based XMLType views using the XMLType member functions on SYS_NC_ROWINFO$ does not work.

Workaround

Use the SQL function extract() instead.

Bug 2244493

Duration datatype not supported in query rewrite.

Bug 2146333

Function-based indexes are not picked up on schema-based XMLType tables, when queries are executed which could use the function-based index.

Workaround

Use a query hint specifying the index to be used.

Bug 2138262

SQLX function does not support BFILE parameters.

Bug 2244530

Duration datatype not working with updateXML function.

Bug 1718333

NLS: CREATE MATERIALIZED VIEW REFRESH COMPLETE causes an ORA-00600 error.

Bug 2278001

Setting of user parameters does not work with XMLTransform or XMLType transform method.

Workaround

Use the PLSQL DOM package to set the parameters.

  1. If a namespace parameter was specified in the query, please set the event '19027 trace name context forever, level 2' to use the CTXXPATH index.
  2. If the query was on a XML schema-based XMLType column, then this could be due to documents of the wrong namespace being present in the table. In such a case, set the event 19027, level 2, and specify a namespace parameter.

  3.  

     

30.15 LOB Known Bugs:

Bug 1732811

The V7 OCI LONG API is not supported for LOBs. This includes functions such as oflng.

Bug 1473512

ORA-0932 in DECODE (NCHAR,NCLOB,NCHAR,NCHAR) in PL/SQL

If the arguments to DECODE are passed in the following order: nchar, nclob, nchar, nchar, then an ora-0932 error is returned. However if the arguments are passed with the nclob as the first parameter as: nclob,nchar,nchar,nchar, it is fine.

30.16 Materialized Views Known Bugs

Bug 1788639

A user may encounter ORA-600 [4882] when creating a materialized view based on an object table or a table with object columns having a sufficiently large amount of data.

Workaround

Specify storage parameters for the initial extent to be large enough both in the materialized view's storage clause as well as in the USING INDEX clause in the CREATE MATERIALIZED VIEW statement.

Another workaround would be to specify larger MINEXTENTS storage value for both the table and index segments. If a larger initial is specified the space is locked by the segment, which might be a undesirable (INITIAL extent values cannot be ALTERed.) Whereas a user specifying a large MINEXTENTS value can ALTER it down to smaller number later on.

30.17 NLS Known Bugs

Bug 2161678

Binds and defines in queries mapped to a remote database always use byte semantics in the remote database, even if the user specified character semantics or used nchar.

30.18 Online Table Reorganization Bugs

Bug 1625847

While performing an online redefinition of a table, if a TRUNCATE TABLE command is issued concurrently, the online redefinition task may hang or give incorrect results.

The workaround for this is to avoid issuing a TRUNCATE TABLE command while performing an online reorganization of the same table.
 


Note:

In Data Guard Manager, the READ-WRITE state is represented as the Paused state, and the READ-WRITE-XPTON state is represented as the Online state.


30.19 Fine Grained Auditing Known Bugs

Bug 1749180

Object type tables/views are not supported.

If you can use a language other than PL/SQL, you could construct the VARRAY using OCI or SQL directly.

30.20 SQL Known Bugs

Bug 2182530

Consider a type which has a user-defined constructor, that doesn't hide the attribute value constructor. Add a column to a table using the ALTER TABLE statement specifying a column default expression invoking the type's attribute value constructor. Instead of succeeding, an ORA-4044 error is returned.

For example, the following statement returns ORA-4044:

        ALTER TABLE tb2 ADD (c2 t1 DEFAULT t1(1));
Workaround

Use two ALTER TABLE statements. The first one should add the column without specifying a default clause and the second ALTER TABLE statement should modify the column indicating the default clause.

For example, use the following two statements in place of the single ALTER TABLE statement above:

         ALTER TABLE tb2 ADD (c2 t1);
         ALTER TABLE tb2 MODIFY (c2 DEFAULT t1(1));
Bug 2249032

SQL statements containing a call to a user-defined constructor will return error ORA-24409 when executed from an Oracle 8.1.7 client. Note that user-defined constructors are only available with Oracle9i release 9.2 compatibility.

Workaround

Embed the SQL statement inside a PL/SQL block.

Bug 1765350

ORA-00923 no longer gets raised when a foreign-key constraint is added to a table whose schema name contains a single-quote.

30.21 SQL Execution Known Bugs

Bug 2273219

Parallel multi-joins (INNER, CROSS, OUTER using ANSI syntax) of parallel table functions run on Oracle MTS mode can hang.

Workaround

Turn off parallelism for table functions or use regular non-ANSI joins.

Bug 2234068

In some rare cases, updating or deleting parallel DML operations may not achieve same degree of parallelism as before for tables with large number of partitions.

Workarounds
Bug 1900341

Parallel Join query plan with ORDER BY in the right input can hang.

Workaround

Turn off parallelism. Run the query serially.

Bug 2257754

Queries/subqueries which reference an external table defined with a named pipe as the data source may hang if the SQL operation requires the underlying data source(s) to be scanned more than once.

Workarounds

30.22 Oracle Enterprise Manager Web Site Known Bugs

Bug 2285765

NLS:T9N: EMWEBSITE_ZH_CN.HTML is corrupted. When using EM via browser in a Simplified Chinese environment, users may see garbled characters.

30.23 OLAP KNOWN Bugs

Bug 2363046

When the OLAP Cube Viewer is launched from OEM, it starts up normally and functions normally, but it causes OEM to go into a wait state until Cube Viewer is exited. Once Cube Viewer is exited, OEM functions normally.

Workaround

If the user wants to make changes to one of their OLAP Cubes and see it reflected in Cube Viewer, there have two methods. The first is to stop Cube Viewer, modify the Cube and then restart Cube Viewer. The second method is to simply launch another instance of the OEM client on your machine.

30.24 Miscellaneous Bugs

Bug 1766419

The Character Set Scanner is unable to detect character data stored within a VARRAY type.

Workaround

In order to scan character data in a VARRAY type, the data must be moved to a datatype such as CHAR/CLOB which the scanner can support in this release.

Bug 1397132

If the Oracle database server is running in Shared Server mode and a PL/SQL program attempts to connect to a host/port combo by means of the UTL_TCP.OPEN_CONNECTION() call where there is not currently a server listening at that remote host/port, then the connect will appear to succeed without actually doing so. However, when the server attempts to actually read or write on the socket for the first time, an error will be returned as if the remote end had suddenly disconnected (ORA-12537: TNS:connection closed). The same situation applies to users using the UTL_HTTP or the UTL_SMTP packages to connect to a HTTP or SMTP server. This problem does not exist when the database server is running in the dedicated server mode.

Workaround

No known workaround exists.

Bug 1488174

ALTER SYSTEM does not change the current session's setting of NLS_LENGTH_SEMANTICS, at least not in the opinion of DDL.

Workaround

Set NLS_LENGTH_SEMANTICS in the initialization parameter file and leave it alone, or use ALTER SESSION.

Bug 1719656

Internal error QCTSTC2O1 is returned when starting up an old database with Oracle9i with COMPATIBLE=9.0.0, and compatibility errors are returned when starting up with COMPATIBLE < 9.0.0. This only happens when the upgrade script wasn't run before starting up with COMPATIBLE=9.0.0.

Workaround

Setting event 10619 allows the database to be started up with COMPATIBLE=9.0.0.

Bug 1730215

TIMEOUT for OracleOCIConnectionPool only works in JDK 1.2.2_07 or a higher version

Workaround

None

Bug 1609487

OCI scrollable cursors for queries involving database links are not supported.

Workaround

None

Bug 1748260

Users get ORA-600 [6051] doing various index operations for indexeswith keys exceeding half a block size.

Workaround

Use larger block sizes for indexes, redefine tables to keep index key size of less than half a block, or do not use indexes.

Bug 1799651

LONG-to-LOB functionality will not work for distributed SQL.

Workaround

none

Bug 2198861

For a migrated tablespace, from dictionary managed to locally managed, when the metadata is exported for plugin tablespace the migration information in the metadata is not correctly exported. This results in internal errors, once the tablespace is plugged in and the metadata is imported, on dropping pre-existing segments and the tablespace. This results in internal errors and data corruption, once the tablespace is plugged in and the metadata is imported.

Workaround

None. Do not transport migrated tablespaces until the bug is resolved.

Bug 2198861

For a migrated tablespace, from dictionary managed to locally managed, when the metadata is exported for plugin tablespace the migration information in the metadata is not correctly exported. This results in internal errors, once the tablespace is plugged-in and the metadata is imported, on dropping pre-existing segments and the tablespace.

Workaround

None. Do not transport migrated tablespaces until the bug is resolved. A patch for this bug will be made available on the Metalink support website.


Oracle
Copyright © 2002 Oracle Corporation.
All Rights Reserved.