Winzipper

home *** CD-ROM | disk | FTP | other *** search

/ Winzipper / Winzipper_ISO.iso / programming / oracle7 7.2 / DB / RDBMS72 / README.DOC < prev next >

Wrap

Text File | 1995-06-23 | 53.2 KB | 1,449 lines

... ... $Header: README.doc 7020200.2 95/06/20 10:57:26 cli Generic<base> $ ... ------------------------------------------------------------------------- Oracle7 Server Release 7.2.2 ------------------------------------------------------------------------- Copyright (C) Oracle Corporation 1993, 1994, 1955 Primary Author: Jason Durbin Contributing Authors: Bill Lee, Bill Maimone. Hari Sankar, Amitabh Sinha This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: RESTRICTED RIGHTS LEGEND: Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065. The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing.Oracle Corporation does not warrant that this document is error free. Oracle, CASE*Dictionary, Pro*Ada, Pro*COBOL, Pro*FORTRAN, Pro*Pascal, Pro*PL/I, SQL*Connect, SQL*DBA, SQL*Forms, SQL*Loader, SQL*Net, and SQL*Plus are registered trademarks of Oracle Corporation. CASE*Designer, CASE*Method, Oracle7, Oracle Parallel Server, PL/SQL, Pro*C/C++, SQL*Module, Oracle Server Manager and Trusted Oracle7 are trademarks of Oracle Corporation. All trade names referenced are the service mark, trademark, or registered trademark of the respective manufacturer. ---------------------------------------------------------------------------- TABLE OF CONTENTS ----------------- Section Description ------- ----------- Introduction ------------ 0.1 Purpose of this README 0.2 Cover Letter and Licensing 0.3 Oracle7 Documentation Currently Available Oracle Version 6 ---------------- 1.0 Before You Migrate 1.1 Offline Tablespaces 1.2 ORA-1556 When Running the Migration Utility 2.0 National Language Support 2.1 WW Date Format Oracle7 Release 7.2.2 --------------------- 3.0 Forward Compatibility 4.0 Backward Compatibility 5.0 Server Changes 5.1 SQL Syntax 5.2 Indexing for Fixed Tables 5.3 Initialization Parameters 5.3.1 MAX_TRANSACTIONS_BRANCHES 5.4 Data Dictionary Views 5.5 Star Hint 6.0 Upgrading and Downgrading 6.1 General Comments about Upgrading and Downgrading 6.2 Upgrading to Release 7.2.2 6.2.1 New Compatibility Type and Resizable Datafiles 6.3 Downgrading from Release 7.2.2 6.4 Special Upgrading and Downgrading Issues 6.4.1 CAT70102.SQL and the Parallel Query Option 6.4.2 ORA_TQ_BASE$ and the Parallel query Option 6.4.3 CATSVRMG.SQL and Server Manager 7.0 Fixes/Enhancements 7.1 Fixes/Enhancements in Release 7.2.2 8.0 Known Server Restrictions 8.1 SORT_WRITE_BUFFER_SIZE Problem 8.2 Interoperability Problem with Time-Stamp Package "STANDARD" 8.3 Restrictions Awaiting Fixes 8.3.1 TO_NUMBER Function 8.3.2 Snapshot Incompatibility 9.0 National Language Support 9.1 General 9.2 Character Sets 9.2.1 Applications and Multibyte Character Sets 9.3 Conversion Between Non-String and String Values 10.0 PL/SQL Behavior Changes After Upgrade 10.1 Behavior Change Caused By Bugfix 237911 After Upgrading To PL/SQL 2.1.5 Or Any Higher Release 10.2 Behavior Change Caused By Bugfix 190119 After Upgrading To PL/SQL 2.1.6 Or Any Higher Release 10.3 Behavior Change Caused By Bugfixes 235190 And 235193 After Upgrading To PL/SQL 2.1.6 Or Any Higher Release 10.4 PLS-905 Error During Execution/Parse of a Cursor Holding an Anonymous PL/SQL Block ----------------------------------------------------------------------------- ********************** * * * Introduction * * * ********************** 0.1 Purpose of this README -------------------------- This README file is relevant only to the delivered Oracle7 Server and its integral parts, such as SQL, PL/SQL, the Oracle Call Interface (OCI), SQL*Loader, Import/Export utilities, SQL*DBA, Server Manager and so on. On many operating systems, the file name of this document is readme.doc. This README documents differences between the server (and its integral parts) and documented functionality. Each operating system release, such as UNIX, DEC VAX/VMS, MS-DOS, AOS/VS, and so on, usually has an additional README document. Oracle products, such as SQL*Forms, may have an additional README document. A README equivalent would be release notes, system bulletins, or similar publications. 0.2 Cover Letter and Licensing ------------------------------ Please read the cover letter included with your Oracle7 package. It contains important information about licenses for Oracle7 product options. 0.3 Oracle7, Release 7.2 Documentation Currently Available --------------------------------------------- A19486-2 PL/SQL User's Guide and Reference A20325-2 Oracle7 Server SQL Reference A19485-2 Oracle7 Server Utilities A19483-2 Oracle7 Server Messages A21022-2 Programmer's Guide to the Oracle Precompilers A19489-2 Programmer's Guide to the Oracle Call Interfaces A21027-1 Pro*COBOL Precompiler Supplement A21020-1 Pro*FORTRAN Precompiler Supplement A21021-2 Programmer's Guide to the Oracle Pro*C/C++ Precompiler A19487-2 Oracle7 Parallel Server A19691-1 Trusted Oracle7 Server Administrator's Guide A19484-2 Oracle7 Server Migration Guide A20321-2 Oracle7 Server Concepts A20322-2 Oracle7 Server Administrator's Guide A20323-2 Oracle7 Server Application Developer's Guide A14808-2 Oracle Server Manager(TM) User's Guide A25434-1 Oracle7 Server Distributed Systems, Volume I: Distributed Data A19488-2 Oracle7 Server Distributed Systems, Volume II: Replicated Data A25421-1 Oracle7 Server Tuning A20327-2 Oracle7 Server Reference A21026-2 SQL*Module User's Guide and Reference A21027-2 Pro*COBOL Supplement to the Oracle Precompilers Guide A21020-2 Pro*FORTRAN Supplement to the Oracle Precompilers Guide A30625-2 Programmer's Guide to the Oracle Call Interface for Ada A16148-2 Programmer's Guide to the Pro*Ada Precompiler A30888-2 Oracle Server Manager for Windows Installation Guide ----------------------------------------------------------------------------- > ************************ * * * Oracle Version 6 * * * ************************ 1.0 Before You Migrate ====================== IMPORTANT: ONCE YOU MIGRATE AN ORACLE VERSION 6 DATABASE TO Oracle7, YOU MUST **NOT** RUN ORACLE VERSION 6 AGAINST THAT DATABASE. To migrate to release 7.2 from Oracle Version 6, follow the instructions documented in the Oracle7 Server Migration Guide and the Oracle7 Server Documentation Addendum. You can migrate directly from Version 6 to release 7.2; it is not necessary to install version 7.0. 1.1 Offline Tablespaces ----------------------- If you are running 6.0.34.3 or earlier, make sure that you pay special attention to the information about managing offline tablespaces in the Oracle7 Server Migration Guide. 1.2 ORA-1556 When Running the Migration Utility ----------------------------------------------- If you encountered an ORA-01556 error message while running the migration utility, then the cluster index I_FILE#_BLOCK# created for the new Version7 catalog tables is not large enough. The default cluster index size is insufficient. The solution to this problem is: Rerun the migration utility and specify a larger integer value for the parameter MULTIPLIER. For example, set MULTIPLIER=20. The default value for the parameter MULTIPLIER is 15, which creates the version cluster index size 1.5 times the Version 6 I_FILE#_BLOCK# cluster index. A value of 20 creates the Version 7 cluster index size 2.0 times the Version 6 cluster index. 2.0 National Language Support ============================= If you use National Language Support features, you should also read Section 9.0, "National Language Support." 2.1 WW Date Format ------------------ The WW date format element behaves differently in Oracle7 than in Oracle Version 6. In version 6, depending on the territory component of the value of the LANGUAGE initialization parameter, WW returned a week number based on either the ISO standard or the number of days from January 1. In Oracle7, WW always returns a week number based on the number of days from January 1, regardless of the value of the NLS_TERRITORY initialization parameter, and the new IW date format element returns the ISO standard week number. If your version 6 application used WW to return the ISO standard week number, replace WW with IW. ----------------------------------------------------------------------------- ***************************** * * * Oracle7 Release 7.2.2 * * * ***************************** 3.0 Forward Compatibility ========================= Future releases will have changes for which you can prepare now. For example, to comply with future SQL ANSI standards. If you follow these recommendations, upgrading to newer releases of Oracle will be simplified. Feature Recommendation --------------------------------- ---------------------------------------- A string of zero length ('') Your application should use a NULL when is not equivalent to a NULL the value is unknown. GLOBAL_NAMES initialization If you use or will use distributed parameter processing, set this parameter to TRUE to ensure a unique identifying name for your database in a networked environment. SELECT privilege will be Always grant the SELECT privilege to a required on tables that user or role if you grant the UPDATE users update. or DELETE privileges on the table. See also Oracle7 Server Reference, Release 7.2 for more information about the initialization parameter, SQL92_SECURITY. 4.0 Backward Compatibility ========================== If a new release of Oracle7 incorporates changes or new features, the changes and new features must be disabled before you downgrade to a previous release. For more information about backward compatibility, see Oracle7 Server Migration, Release 7.2, Chapter 8, "Upgrading and Downgrading." 5.0 Server Changes ================== 5.1 SQL Syntax -------------- 5.2 Indexing for Fixed Tables -------------------------------- Certain V$ views now have a fast access method that can greatly improve query performance on these views. In previous versions, queries of V$SESSION, V$SESSTAT, and V$SQLAREA could cause performance bottlenecks due to very large sort and scan operations. With Release 7.2, queries using the numeric columns of these tables as the search key can perform significantly faster due to an "index" on the fixed tables' numeric columns. The specific columns on which there is now an index are: VIEW COLUMNS ON WHICH THERE IS NOW AN INDEX ---------- ---------------------------------------- V$SESSION SID V$SESSTAT SID STATISTIC# V$SQLAREA HASH_VALUE Care must be taken in the writing of the queries. * Single table queries can use the fast access feature as long as they do not contain an OR clause in the WHERE list. * Joins must specify the nested loops join optimizer hint. Here is an example of a query that illustrates the application of this feature. SELECT /*+ use_nl(w, i) use_nl(i,p) use_nl(p,k) use_nl(k,s) */ s.sid, s.username oracle_user, s.command, s.status, s.server, s.schemaname, s.osuser client_osuser, s.process client_osprocess, s.machine client_machine, s.terminal client_terminal, s.program client_program, p.username process_osuser, p.pid process_orapid, p.spid process_ospid, p.background, p.latchwait, s.lockwait, w.event wait_event, w.p1text, w.p1, w.p2text, w.p2, w.p3text, w.p3, w.wait_time, w.seq# wait_seq#, i.block_gets, i.consistent_gets, i.physical_reads, i.block_changes, i.consistent_changes, k.kglnaobj sql, k.kglnahsh sql_hash_value, k.kglhdadr sql_addr FROM v$session_wait w, v$sess_io i, x$kglcursor k, v$session s, v$process p WHERE p.addr = s.paddr and w.sid = s.sid and k.kglnahsh (+) = s.sql_hash_value and k.kglhdadr (+) = s.sql_address and i.sid = s.sid; 5.3 Initialization Parameters ----------------------------- 5.3.1 MAX_TRANSACTIONS_BRANCHES Default value: 8 Range of values: 1 - 32 This parameter controls the number of branches in a distributed transaction. For example, a certain TP monitor uses one branch per server involved in a distributed transaction. Another TP monitor uses one branch per server group involved in a distributed transaction. The previously fixed maximum number of branches limited the number of servers or server groups involved in a distributed transaction to 8 per Oracle instance. With the MAX_TRANSACTION_BRANCHES parameter, the maximum number of branches can be increased to 32, allowing for 32 servers or server groups per Oracle instance to work on one distributed transaction. Setting MAX_TRANSACTION_BRANCHES to a lower value will reduce shared pool memory usage slightly (MAX_TRANSACTION_BRANCHES * DISTRIBUTED_TRANSACTIONS * 72 bytes). 5.4 Data Dictionary Views ------------------------- Data dictionary views maintained in Oracle7 for backward compatibility to Oracle Version 5 and Version 6, created in the files CATALOG5.SQL and CATALOG6.SQL, will be obsoleted starting with Oracle, Version 8. All references to these dictionary views should be removed. 5.5 Star Hint ------------- The STAR hint forces a star query plan to be used if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least 3 tables and the large table's concatenated index has at least 3 columns and there are no conflicting access or join method hints. 6.0 Upgrading and Downgrading ============================= 6.1 General Comments about Upgrading and Downgrading ---------------------------------------------------- For more information about upgrading and downgrading issues, see Oracle7 Server Migration, Release 7.2, Chapter 8, "Upgrading and Downgrading." 6.2 Upgrading to Release 7.2 ------------------------------ 6.2.1 New Compatibility Type and Resizable Datafiles A new compatibility type has been added to Release 7.2. It guarantees that, when any feature of resizable datafiles is used, Oracle will either automatically upgrade to Release 7.2 or issue an error message if such operation is not allowed, according to the COMPATIBLE parameter. WARNING FOR RELEASE 7.2 BETA CUSTOMERS: The 7.2 beta release did not contain this compatibility type. If you were a beta customer and used any of the resizable data file features, Oracle may, under Release 7.2.2, issue meaningless internal errors in certain situations. The following procedures can be used to avoid such internal errors: 1. If, while using the 7.2 beta version, you downgraded to an earlier release, upgrade your database to be 7.2 compatible when starting the new release of Oracle. 2. Perform either of the following operations: a. Resize all data files to their original size. b. Set AUTOEXTEND ON and then reset it to AUTOEXTEND OFF. The database administrator can query V$COMPATIBILITY to confirm the upgrade. 6.3 Downgrading from Release 7.2 ---------------------------------- Downgrading from Release 7.2 is discussed in Oracle7 Server Migration, Release 7.2, Chapter 8, "Upgrading and Downgrading". 6.4 Special Upgrading and Downgrading Issues -------------------------------------------- 6.4.1 CAT70102.SQL and the Parallel Query Option If you have the 7.1.1 parallel query option and you have already run queries in parallel against a given database, you will receive an error when running CAT70102.SQL on that database because the sequence will already exist. In this case, the error can be ignored. 6.4.2 ORA_TQ_BASE$ and the Parallel Query Option The ORA_TQ_BASE$ sequence, introduced in 7.1.2, is required if you use the Parallel Query option. The sequence is created when any of the following scripts are run: o SQL.BSQ (automatically run as part of database creation) o MIGRATE.BSQ (when migrating from Oracle version 6) o CAT70102.SQL (when upgrading Oracle7 to 7.1.3 and installing Server Manager views) 6.4.3 CATSVRMG.SQL and Server Manager In 7.1.2, two new SQL scripts were added for the Server Manager product. CATSVRMG.SQL is automatically run during database creation by CATALOG.SQL. This script installs several views and one public synonym (SM$VERSION) required by Server Manager to administer the database. CATNOSVM.SQL is a script that DROPs all objects created by CATSVRMG.SQL, de-installing the Server Manager. 7.0 Fixes/Enhancements ====================================== This section lists fixes or enhancements introduced in Release 7.2.2. Each fix or enhancement is noted with a fix number. Your support contract may allow you access to the Oracle bug database; if so, you can use the fix number, also known as the bug number, to obtain more information about a particular fix. 7.1 Fixes/Enhancements in Release 7.2.2 --------------------------------------- 25778 NOT IN subqueries now use an index when indexed columns have NOT NULL constraints. 130007 The current ROWID being waited upon by each session involved in a deadlock is now included within the deadlock graph. 142749 When ROWIDs from several tables are stored in another table and used to delete rows from one of the former tables (say T), error ORA-01410 is raised whenever any ROWID does not belong to table T. 156147 V$ views are shown in the view DICTIONARY, even if the user has no privileges to select from them. The view definition has been updated to show these views only if the user has sufficient privileges to select from them 158950 The views NLS_SESSION_PARAMETERS, NLS_INSTANCE_PARAMETERS, and NLS_DATABASE_PARAMETERS now appear in the DICTIONARY view. 170736 CREATE AS SELECT now retains column null constraints through database links. 176156 A wrong column name, when selecting from a fixed table as a non-sys user, would cause a [4828] internal error instead of an ORA-00942 'table or view does not exist' error. This has been fixed. 179221 The correct OS related information for terminal now appears in V$PROCESS. 184513 DBMS_SHARED_POOL runs as SYS instead of as the caller. This has been fixed. In addition, if you call DBMS_SHARED_POOL.KEEP for a package or procedure, only the object itself is loaded and pinned. The Diana and Machine Pcode for the procedures are not. This can result in an ORA-4031 error when a procedure in the package is later executed, because there is not enough memory to load the procedure. This has been fixed so that when you KEEP a package or procedure, all the necessary information for the package, the package body, and the procedures are loaded. 185132 There is a limit on the size of the INCLUDE parameter initialization files (8192 bytes). Previously, the code enforced this by silently truncating the file. This has been corrected and will no longer do this in Release 7.2. 185629 Sub-roles of directly granted roles are now enabled as default roles when used in the DEFAULT ROLE ALL EXCEPT clause. 185643 The undocumented parameter SORT_MTS_BUFFER_FOR_FETCH_SIZE was removed. 186155 There are now public synonyms for all DBA_% views, so that a user with privileges to select from these views no longer needs to run CATDBSYN.SQL. 186269 An improved system default hash function has been provided in 7.2. It is used by default when COMPATIBLE=7.2.0 in INIT.ORA. Old hash clusters will continue to use the old hash function. 187803 INSTR and INSTRB will no longer accept a 0 as its fourth argument, as it used to, which caused it to return an incorrect result. 190987 When a session was killed by the idle sniper, the status column of the V$SESSION view was not being properly updated. The status column will now indicate SNIPED to show that a session is no long active, and is just waiting for the client to realize this. 195693 An attempt to add a referential constraint through a public synonym fails with ORA-00942. This has been fixed. 195888 We now exchange system commit numbers when the distributed operation starts to synchronize them to have a consistent view evaluation involving remote tables. Before this fix, this was done after the operation. 199168 This bug fix generates a new kind of undo, so it is only enabled when COMPATIBLE=7.2.0.0 or greater in INIT.ORA. When enabled, it fixes a possible corruption when running parallel server and doing TRUNCATE in one instance while a process dies in a different instance with outstanding undo on that table. 200230 Problem: PL/SQL was not raising an error for fully qualified columns that were otherwise out of SQL scope, e.g., SELECT dept.dname FROM emp; This has been fixed. 200914 Queries involving certain simple UNION ALL views now consider indexes on tables inside the views as access-path for nested loop joins. 205721 For some timeout values, DBMS_PIPE.SEND_MESSAGE, DBMS_PIPE.RECEIVE_MESSAGE, DBMS_ALERT.WAITONE, and DBMS_ALERT.WAITANY did not process the timeout accurately. This has been fixed. 210384 Views with UNION ALL were given cost estimates by the optimizer that were too high. This has been fixed. 210565 Using the current of cursor on a closed cursor did not generate INVALID ROWID. This has been fixed. 210783 When using the CBO on unanalyzed hash-clustered tables, the cost for the hash access method is zero. The CBO will thus always choose the hash access method when possible. 210784 Hints were being ignored in the presence of MIN or MAX functions for simple, single-table queries. This has been fixed. 211500 First-rows processing used sort-merge joins and order-by sorts in cases where these could be avoided. This has been fixed. 211543 The accuracy of costing is now improved for queries with NOT LIKE and <<> predicates. 211563 The Cost Based Optimizer now generates more accurate cost estimates for queries with multiple copies of the same predicate. 212827 When analyzing a cluster or an index, the cursors that depended on the cluster or index are now invalidated and reparsed. 213951 RULE, ALL_ROWS, and FIRST_ROWS hints were not having an effect in set queries (i.e. UNION, INTERSECT, or MINUS queries). This has been fixed. 214868 Joins with outer-join operators that were not real outer-joins due to the existence of other predicates w/o outer-join operators no longer result in performance inferior to regular joins. 216381 The usage comments showed incorrect usage of SET SERVEROUTPUT. The comment has been corrected. 217822 The cost of ORDER BY sort of set queries was not computed by the cost-based optimizer. This has been fixed. 218218 RULE, ALL_ROWS, and FIRST_ROWS hints were not having an effect in set queries (i.e.UNION, INTERSECT, or MINUS queries). This has been fixed. 220509 The username printed in ORA-1045 was not the database user. This has been fixed. 221252 The cause statement for message ORA-01771 has been updated to include the TABLESPACE option. The OERR utility will see this change since it just reads the error message file itself. 221789 Some monitor screens in SQL*DBA may fail with DBA-328 if run by a non-dba user granted only the MONITOR role. You must rerun the modified ULTMONTR script to add in the missing grant statements. 225112 UPIDPR returns a type of DTYBOL for a parameter of the type ref cursor. This has been fixed. 233779 Session attachment is now done based upon an event, rather than after waiting for a fixed amount (1 sec) of time. 237046 Parallel load file name is no longer lower-cased when the parallel load file is specified in an options statement in the control file. 244183 Previously a fully qualified reference to a sequence (e.g. schema.sequence.nextval) within a PL/SQL block would result in ORA-6550 and PLS-00389. This has been corrected. 245817 The PL/SQL parser no longer loops when encountering an unfinish qouted identifier. 247484 Grant and revoke now correctly detect reserved keywords in the user/role list, and report an ORA-00987 accordingly. 247871 The general failcase is: SELECT varchar_funct INTO non-char-variable FROM ... in a PL/SQL block, where "varchar_funct" is some built in or user function returning varchar2, for which the return length is not known in advance. "non-char-variable" is some variable (or a record field) of type DATE or BINARY_INTEGER. On some platform it may also fail for NUMBER/INTEGER/ROWID RAW, and other types. For example the following statements also fail due to this bug: DECLARE bi binary_integer; d date; BEGIN SELECT '03-NOV'||'-94' INTO d FROM dual; -- any usage of d after this fails SELECT '123'||'45' INTO bi FROM dual; -- fails w/ORA-01460 SELECT nvl(null,'12345') into bi FROM dual; -- fails w/ ORA-01460 END; 249055 The performance of Cartesian product joins, which are used in star queries, is significantly improved, especially when the points of the star consist of more than one table. 249703 Execution times for logically equivalent queries with slightly different WHERE clauses differ dramatically. This has been fixed. 250063 Functions returning records with embedded index tables may cause a bus error during execution because required initializations are not done. 250197 Queries that contain subqueries require excessively long execution times. Execution time for such queries can now be reduced by using the hint PUSH_SUBQ which causes all subqueries in a query block to be executed at the earliest possible time. Normally subqueries are executed as the last operation in the query. The PUSH_SUBQ hint is not applied to subqueries where the table on which the subquery is written is outerjoined, remote, or joined with a merge join. 250482 When error message files are missing or not in the expected directory, the snapshot background processes (i.e., the job queue processes) will die the first time they look for work. 255776 MIN/MAX optimization using an index no longer occurs if the HAVING clause is present. 255969 Optimizer now uses transitivity for LIKE predicates. 258136 The new USE_CONCAT hint will force ORs to be expanded with CONCAT nodes regardless of cost. 258249 You can now use the role privileges in a PL/SQL function from within SQL. 259099 PL/SQL functions modifying a package state may now be used in set queries, such as UNION, MINUS, etc. 259193 Grant and revoke of roles "DBA" and "RESOURCE" now process the UNLIMITED TABLESPACE privilege correctly. 259639 ALL_OBJECTS did not shows other package bodies in other users' schemas. This has been fixed. 261002 If ALTER TABLE ADD CONSTRAINT UNIQUE.. was done on a table for which the table owner's name was more than 17 characters long, an internal error was generated in some cases. This does not happen anymore. 261035 The choice of index is unpredictable in case of cost ties in the cost-based optimizer. 262073 It is not possible to specify TOP_ON_ERROR or EXECUTE_AS_USER in DBMS_DEFER_SYS.SCHEDULE_EXECUTION. 262074 DBMS_DEFER_SYS.SCHEDULE_EXECUTION does not record the number of the job that pushes the deferred RPC queue. Consequently, DBMS_DEFER_SYS.SCHEDULE_EXECUTION cannot remove the job. 263137 When a background job or a background snapshot refresh raised an exception,the background process would go into an infinite loop and continually append to the snp*.trc file. This happened on some, but not all platforms. 264122 IS NULL predicate always returned false on index only scans. This has been fixed. 264746 An ORA-1219 error is no longer generated if a fixed table or view is accessed in a from-clause subquery when the database was not open. 265372 Unhandled exceptions, such as SNAPSHOT TOO OLD, in the propagation of the deferred RPC queue, could cause a communication deadlock. 265376 Unhandled exceptions during DEFCALL cleanup by DBMS_DEFER_SYS.EXECUTE can leave junk in DEFCALL. 265383 The DEFERRED_TRAN_DB argument accidentally specified both the origin/copying node for the transaction as well as the transaction identifier in DBMS_DEFER_SYS.DELETE_TRAN. This has been fixed. 266068 An error ORA-6502 would be returned by PL/SQL when fetching a select-list item whose length is not determined at compile time (e.g. 'a'||'b') into a %rowtype variable. 266317 The "size" limit at which PL/SQL Packages generate PLS-00123 has been increased for Package Bodies. For typical code the limit is roughly doubled. 266542 If there are multiple call records in a deferred transaction in the error queue, DBMS_DEFER_SYS.EXECUTE_ERROR raises 1403 when Deferred-TRAN_ID is NULL or when both DEFERRED_TRAN_ID is NULL and DEFERRED_TRAN_DB is NULL. This has been fixed. 267152 When DBMS_DEFER_ST~YS.EXECUTE is used by two or more processes to push deferred transactions to different destinations simultaneously, already pushed transactions may remain in DEF$_CALL but will not appear in DEFCALL. 268399 <HR> Multiple before update triggers that modify different columns will no longer erroneously report ORA-1401 and ORA-2652 errors. 268441 When a deferred transaction is simultaneously propagated to several destinations, a conflict at one destination can leave junk in system.DEF$_CALLDEST at the source database. This can prevent the transaction from being propagated to its last destination. This has been fixed. 269304 The problem is that COMPILED and WRAPPED were added as reserved words in 2.2, although they weren't reserved words in 2.1. So, users declaring COMPILED and WRAPPED would start seeing syntax errors in 2.2. The fix is to make these two words keywords instead of reserved words. 269904 Pro*C could not parse certain va_start() macro expansions Some va_start() macro expansions used '&...' as part of the expansion which Pro*C could not parse. Some also used the concat operator (ie; '##') as part of the expansion of va_start() which, in some cases, Pro*C was also not able to process correctly. 269987 If too many conflicts occur during one call to DBMS_DEFER_SYS.EXECUTE, the error transaction cannot be successfully created at the destination because too many cursors are open. This has been fixed. 272610 When the destination for pushing the deferred RPC queue is the global name of the database and there are one or more calls queued for that destination, dbms_defer_sys.execute will loop. 275401 There were some cases in which the compiler did not do a correct job of privilege-checking. The compiler would incorrectly generate error pls-904 or pls-201 under the following circumstances (reported under bug 275401, 181934) - connected to <<user2> - compiling PL/SQL code that had a reference to one of the following: <<user1>.<<proc/func>.<<parameter>%type <<user1>.<<package>.<<variable>%type <<user1>.<<package>.<<cursor>%<<attribute> (where <<attribute> is any allowed attribute: ROWTYPE, NOTFOUND, FOUND, ROWCOUNT, ISOPEN, etc.) - EXECUTE privilege on <<user1>'s package/procedure/function was granted to <<user2>. Replacing a reference to, for example, <<user1>.<<package>.<<cursor>%NOTFOUND with a reference to <<synonym>.<<cursor>%NOTFOUND where the synonym translates to <<user1>.<<package> will still show the same problems as when there's a direct reference to <<user1>.<<package>.<<cursor>%NOTFOUND. 278040 Previously, evaluation of a LIKE predicate in an index on multibyte data could give inconsistent results. The result is now consistent in both index and table scans. 279019 Wrong results were possible for certain combinations of outer-joins and NOT IN subqueries. 278331 Anonymous PL/SQL blocks referencing a packaged object using a fully qualified name (e.g. schema.package.procedure) was not shared in memory. 281047 An error ORA-1578 may be returned during an update to a chained row if the update results in the column being moved to another block. 8.0 Known Server Restrictions ============================= 8.1 SORT_WRITE_BUFFER_SIZE problem ---------------------------------- Due to a bug in the initial release, the default value of SORT_WRITE_BUFFER_SIZE is not correct. If you set SORT_DIRECT_WRITES=TRUE, you must also set SORT_WRITE_BUFFER_SIZE to either 32768 or 65536. Failure to set SORT_WRITE_BUFFER_SIZE to one of these values may result in excessive use of disk space for sorts. 8.2 Interoperability Problem with Time-Stamp Package "STANDARD" --------------------------------------------------------------- Certain PL/SQL programs may fail to compile on 7.0.15/7.0.16 under the following conditions: 1) use of db links defined on a 7.0 database to connect to a 7.2 database 2) use of %type or %rowtype either on the 7.0 or the 7.2 side. Possible symptoms of failure, while compiling a PL/SQL program on 7.0.15/7.0.16 are, as follows: 1) The 7.0.15/7.0.16 session may crash with the message: "Unexpected oracle server termination by signal 6" and a trace file might be generated with: "Exception signal: 11" and dumps about LIBRARY OBJECTs. 2) The 7.0.15/7.0.16 session may report the following error: ORA-06501: PL/SQL: internal error 74402, arguments: [],.... and a trace file might be generated with a stack trace through the functions: ksedmp, ksfdmp, pppjmp, pdtity, pdtity, pdtiit, pdtidc, pdlifu, etc. This problem can be fixed by either 1) upgrading the 7.0.15/7.0.16 database to 7.1.3 2) applying a back-port patch to 7.0.15 or 7.0.16 to include the following two bug-fixes: a) fix for bug 187531 b) fix for bug 232292 8.3 Restrictions Awaiting Fixes ------------------------------- 8.3.1 TO_NUMBER Function 248776 The TO_NUMBER function, using the NLS_CURRENCY clause, does not work as expected. The function expects the currency text to be tencharacters. For example, the following will not work: SELECT TO_NUMBER( 'Fl100.00', 'L999D99', 'NLS_CURRENCY = ''Fl'' ') FROM dual; A possible workaround would be to use the LPAD function. For example: SELECT TO_NUMBER( LPAD('Fl100.00',length('Fl100.00') +10-length('Fl'), ' '), 'L999D99', 'NLS_CURRENCY = ''Fl'' ') FROM dual; 8.3.2 Snapshot Incompatibility 255394 When a 7.1.3+ read-only snapshot performs a fast refresh from a 7.0 master, the snapshot will not delete rows that were deleted by the master if the snapshot's selection criteria had no WHERE clause. There are four possible workarounds. Workaround 1: Upgrade the master database to 7.1.3+. Workaround 2: Replace the snapshot. Replace the following code: CREATE SNAPSHOT foo AS SELECT c1, c2, c3, FROM foo@master; with CREATE SNAPSHOT foo AS SELECT c1, c2, c3, FROM foo@master WHERE c1 is NULL OR c1 is NOT NULL; or CREATE SNAPSHOT foo AS SELECT c1, c2, c3, FROM foo@master WHERE DECODE(c1, c2, 0) = 0; Workaround 3: Replace the snapshot's underlying MVIEW$ view. Replace the following code: CREATE or REPLACE MVIEW$foo AS SELECT c1, c2, c3, FROM foo@master; with CREATE or REPLACE MVIEW$foo AS SELECT c1, c2, c3, FROM foo@master WHERE c1 is NULL OR c1 is NOT NULL; or CREATE or REPLACE MVIEW$foo AS SELECT c1, c2, c3, FROM foo@master WHERE DECODE(c1, c2, 0) = 0 NOTE: The advantage of replacing the view, something not recommended by Oracle, is that you don't have to reinstantiate large snapshots if the 7.1 snapshot was not yet refreshed from the 7.0 snapshot. If the 7.1 snapshot has been refreshed, replacing the view could lose deletes; you should drop and recreate the snapshot instead. Workaround 4: Apply the patch to Bug #111890 to the 7.0 master database. 9.0 National Language Support ============================= This section is only relevant if you use National Language Support features. If you used National Language Support features in Oracle Version 6, see Section 2.0, "National Language Support." 9.1 General ----------- Some National Language Support interactive interface prompts require YES/NO responses rather than the appropriate National Language Support language-independent equivalent. 9.2 Character Sets ------------------ Single byte and multibyte character encoding schemes are supported. RESTRICTION: Character sets that replace "|" (vertical bar) and "@" (at) with another character CANNOT use these characters in object names. For example, D7DEC replaces "|" with O-umlaut, thus O-umlaut is not allowed in object names as it would be interpreted as the concatenation symbol. This is the same restriction that applied with 7-bit character sets in Oracle version 6. RESTRICTION: If your system has a character set that does not contain "^" (caret) and "~" (tilde), you MUST use "<<>" or "!=" for inequality testing. (The ANSI/ISO SQL standard permits only "<<>".) 9.2.1 Applications and Multibyte Character Sets --------------------------------------------- The use of multibyte character sets affects the way that many Oracle applications interpret the lengths of character data. Problems can arise when the client and the server use a character representation with a differing number of bytes per character. Note: None of the problems mentioned in the remainder of this section should arise if one of following conditions are met: * Both the client and the server use a single-byte character set. * The multibyte character set is guaranteed to have the same number of bytes per character as the single-byte character set. However, remember that when the length of character string data is specified in an OCI bind or define call, it is always specified in bytes, not characters. Also, when CHAR(n) and VARCHAR(n) table columns or PL/SQL variables are declared, n is the number of bytes in the column or variable. The basic problem occurs when a character string is moved between client and server; its length in bytes can change. In the worst case, the length of the string can shrink or increase by a factor of five. In any conversion, four ratios and as many as three character sets may be involved. The characters sets are client, network, and server. The four ratios are: Client-Send (ratio between client character set and network) Server-Send (ratio between server character set and network) Client-Receive (ratio between client character set and network for receiving) Server-Receive (ratio between server character set and network for receiving) Usually, three of the four are 1:1. For example, in the case of a JEUC client and a WE8DEC server, the network character set is WE8DEC: Client Server Send 1:1 1:1 Receive 2:1 1:1 These character set conversion problems affect OCI applications and tools that use the UPI, such as SQL*Forms and the precompilers. 9.3 Conversion Between Non-String and String Values --------------------------------------------------- When a string variable is to receive the output from a non-string datatype, the string variable must be large enough to receive the translated output. String lengths are specified in bytes on the client side, and this may cause problems when a non-string value must be converted to a multibyte string value. The following examples demonstrate this using SQL*DBA; analogies are made to OCI and precompiler applications. The following examples use SQL*DBA to illustrate this point. Note: The declaration of "my_string" in the VARIABLE statement is always in bytes. my_string VARCHAR2(4) is a four byte variable named "my_string", and my_string VARCHAR2(8) is an eight byte variable. When running on a client that uses a single byte character set, the following example works: SQLDBA> variable my_string VARCHAR2(4); SQLDBA> BEGIN SQLDBA> SELECT 1234 INTO :my_string from dual; SQLDBA> END; SQLDBA> / The example works because the numeric literal 1234 has a precision of four, and the varchar2 variable is four BYTES long. On a client machine that is using a multibyte character set, this example would fail, returning an ORA-006502 error. Note: Even the following example could fail if the character set on the client side is multibyte: SQLDBA> variable my_string VARCHAR2(8) SQLDBA> BEGIN 2> SELECT 1234 INTO :my_string FROM dual; 3> END; 4> / because there is no guarantee that characters are always represented in two bytes. In OCI applications, make sure that the output length that you use in the ODEFIN call (the BUFL parameter) and the host language string variable are sized sufficiently to receive converted values. In precompiler applications, make sure that the host variable string for the output is large enough. 10.0 PL/SQL Behavior Changes After Upgrade --------------------------------------------------- 10.1 Behavior Change Caused by Bugfix 237911 After Upgrading to PL/SQL 2.1.5 or Any Higher Release Description of Behavior Change: The PL/SQL compiler must check that the elements of the INTO-target list of a select statement or cursor fetch statement are assignable and report errors PLS-00363 or PLS-00403 if they are not assignable. PL/SQL 2.1.4 and earlier releases failed to correctly check the assignability of all but the last element in the INTO-target list of select and fetch statements. Consider the following example PL/SQL procedure: PROCEDURE p (...) IS ... BEGIN SELECT * INTO <<a>, <<b>, <<c> FROM <<table>; END; PL/SQL 2.1.4 correctly checked the assignability of <<c> while failing to do the complete set of checks for <<a> and <<b>. In particular, it failed to report errors on <<a> and <<b> if they were any of the following: - a reference to an IN parameter - the name of a function - a string-literal - a reference to a scalar for-loop variable - a reference to a constant - a reference to an enumeration-literal (e.g.: TRUE, FALSE) Releases 2.1.5 and higher now correctly report this error. If you upgrade to release 2.1.5, or higher, and your procedure has the constructs described above, an attempt to recompile your procedure, either automatically or manually, will result in either - error PLS-00363, if the upgrade is to 2.1.5 - error PLS-00403, if the upgrade is to any release higher than 2.1.5 Corrective Action: You have two choices: a) If you are in a position to fix the PL/SQL code: Fix your PL/SQL code and recompile the procedure. (example: decide carefully if your procedure should use IN parameter or an IN OUT parameter and fix your code accordingly). b) If fixing the PL/SQL code is not an option, then: If you are running 2.2.x or 2.3.x, do one of the following: 1. Disable bugfix 237911 permanently by performing the following steps: a. Shutdown the database instance b. Add the following line to the INIT.ORA file: event="10925 trace name context forever" c. Startup the database instance 2. Disable bugfix 237911 temporarily to allow your procedure to compile by performing the following steps: a. Turn off bugfix 237911 temporarily by doing: ALTER SESSION SET EVENTS='10925 trace name context forever'; b. ALTER PROCEDURE <<procedure-name> COMPILE; c. Enable bugfix 237911 again by doing: ALTER SESSION SET EVENTS='10925 trace name context off'; If you are running 2.1.6, do the following to permanently disable bugfix 237911 in 2.1.6: Call your Support Rep and ask for a 2.1.6 patch for bugfix 284597 to backout bugfix 237911. 10.2 Behavior Change Caused by Bugfix 190119 After Upgrading To PL/SQL 2.1.6 or Any Higher Release Description of Behavior Change: The "end" identifier that is optional at the end of a package, procedure or function must match the name of the entity it ends; if there is a mismatch, the compiler must report error PLS-00113. PL/SQL 2.1.5 and lower versions did not report any errors if the identifiers did not match. PL/SQL 2.1.6 and higher revisions now correctly report the error if the identifiers do not match, for example, as in: PACKAGE PKG IS ... END PKGXXX; Corrective Action: You have two choices: a) If you are in a position to fix the pl/sql code: Fix the "end" identifier so that it matches or remove it and recompile your procedure. b) If fixing the pl/sql code is not an option, then: If you are running 2.2.x or 2.3.x, do one of the following: 1. Disable bugfix 190119 permanently by performing the following steps: a. Shutdown the database instance b. Add the following line to the INIT.ORA file: event="10926 trace name context forever" c. Startup the database instance 2. Disable bugfix 190119 temporarily to allow your procedure to compile by performing the following steps: a. Turn off bugfix 190119 temporarily by doing: ALTER SESSION SET EVENTS='10926 trace name context forever'; b. ALTER PROCEDURE <<procedure-name> COMPILE; c. Enable bugfix 190119 again by doing: ALTER SESSION SET EVENTS='10926 trace name context off'; If you are running 2.1.6, do the following to permanently disable bugfix 190119: Call your Support Rep and ask for a 2.1.6 patch for bugfix 284599 to backout bugfix 190119. 10.3 Behavior Change Caused by Bugfixes 235190 and 235193 After Upgrading to PL/SQL 2.1.6 or Any Higher Release Description of Behavior Change: The PL/SQL compiler must flag multiple incomplete (forward) type declarations for the same type with error PLS-00105. It must also flag incomplete types that are not completed within the same compilation unit (stored PL/SQL procedure/package/function) with error PLS-00311. PL/SQL 2.1.5 and earlier releases failed to flag these errors (see bugs 235190 and 235193). PL/SQL 2.1.6 and higher releases now correctly flag these errors. However, because of this combination of errors, certain PL/SQL programs that previously compiled without errors will now compile with either error PLS-00105 or PLS-00311. For example: PACKAGE PKG1 IS TYPE r; TYPE r; -- will now compile with error PLS-00105 TYPE r IS record (a VARCHAR2(10)); END; PACKAGE PKG2 IS TYPE r; -- will now compile with error PLS-00311 END; Corrective Action: You have two choices: a) If you are in a position to fix the PL/SQL code: Remove all but one of the incomplete type-declarations and recompile procedure. b) If fixing the PL/SQL code is not an option, then: If you are running 2.2.x or 2.3.x, do one of the following: 1. Disable bugfixes 235190 & 235193 permanently by performing the following steps: a. Shutdown the database instance b. Add the following line to the INIT.ORA file: event="10927 trace name context forever" c. Startup the database instance 2. Disable bugfixes 235190 and 235193 temporarily to allow your procedure to compile by performing the following steps: a. Turn off bugfix 235190 & 235193 temporarily by doing: ALTER SESSION SET EVENTS='10927 trace name context forever'; b. ALTER PROCEDURE <<procedure-name> COMPILE; c. Enable bugfixes 235190 and 235193 again by doing: ALTER SESSION SET EVENTS='10927 trace name context off'; If you are running 2.1.6, do the following to permanently disdable bugfixes 235190 and 235193: Call your Support Rep and ask for a 2.1.6 patch for bugfix 284598 to backout bugfixes 235190 and 235193. 10.4 PLS-905 Error During Execution/Parse of a Cursor Holding an Anonymous PL/SQL Block This error can occur when an anonymous PL/SQL block references a remote object (through a dblink) and the remote object has been altered. For this to happen, the cursor holding the anonymous PL/SQL needs to be in the following state ... * It has been previously fully compiled * Since it was last fully compiled, it has been partly aged out of the cache (Note that "parse" and "compile" are synonymous in this description.) The problem here is that the cursor keeps an old dependency reference on the remote object (which has the old timestamp of the object). During PL/SQL compilation we notice that the timestamp on the remote object doesn't match the timestamp in the dependency entry and the dependency is marked invalid. Later, the PL/SQL compiler trys to lock the remote object through the dependency entry and discovers that the dependency is invalid.It then signals the PLS-905 error. It is important to note that if the cursor had not been aged out at all, then we would have executed and found the timestamp mismatch. This would have instead signalled an error such as: ORA-04068: existing state of packages has been discarded ORA-04062: timestamp of procedure "SCOTT.P1" has been changed To summarize, here are the conditions that can be encoutered when an anonymous PL/SQL block is parsed and executed and which references a remote object that has been modified since the cusor was last completely compiled. 1. If the cursor has been fully aged out of the cache (the parse/compilation of the cursor has to start at square 1) then the dependency timestamp will be set to the new one and the cursor will parse and execute without errors. 2. If the cursor is in the state that it is partly compiled (it still has the old dependency information), then PL/SQL compilation will discover a timestamp mismatch and signal the ORa-905 error. 3. If the cursor is fully compiled (nothing has been aged out of the shared pool), then PL/SQL execution will discover the timestamp mismatch and signal the error ORA-4062. The workaround if condition 2 or 3 is encountered is to reissue the the operation that signaled the error. This works because the cursor is marked to be fully recompiled when the timestamp mismatches are detected. <<End of 7.2.2 README file>>