Feature Changes for Oracle Database 18c Upgrade Planning

Use these feature changes to help prepare for changes that you include as part of your planning for Oracle Database 18c upgrades.

Topics:

Support Indexing of JSON Key Names Longer Than 64 Characters
If you use JSON keys, then you can take advantage of increased efficiency of searching JSON documents generated from HASH MAP-like structures by using longer key names.
Upgrading Existing Databases is Replaced With Image Installations
Starting with Oracle Database 18c, existing services are no longer migrated by the installation. Use Database Upgrade Assistant (DBUA) to migrate services.
RPM-Based Oracle Database Installation
On Linux systems, you can install Oracle Database 18c with an RPM image installation.
Token Limitations for Oracle Text Indexes
Starting with Oracle Database Release 18c, the indexed token maximum size is increased to 255 characters for single-byte character sets.
Changes to /ALL/USER/DBA User View and PL/SQL External Libraries
Starting in Oracle Database 18c, there are changes to the /USER/ALL/DBA_ARGUMENTS and /USER/ALL/DBA_IDENTIFIERS views, and to LIBRARY object creation in PDBs.
Symbolic Links and UTL_FILE
You cannot use UTL_FILE. with symbolic links. Use directory objects instead.
Deprecation of Direct Registration of Listeners with DBCA
Using Database Configuration Assistant (DBCA) to register Oracle Database to Oracle Internet Directory (OID) is deprecated in Oracle Database 18c.

Parent topic: Behavior Changes, Deprecated and Desupported Features for Oracle Database 18c

Support Indexing of JSON Key Names Longer Than 64 Characters

If you use JSON keys, then you can take advantage of increased efficiency of searching JSON documents generated from HASH MAP-like structures by using longer key names.

The upper limit is increased for JSON key names that can be indexed by the JSON Search index. The JSON key name upper limit in Oracle Database 12c Release 2 (12.2.0.2) and later releases is 255 bytes. In previous releases, JSON search indexes that were created did not index key names greater than 64 bytes.

Parent topic: Feature Changes for Oracle Database 18c Upgrade Planning

Upgrading Existing Databases is Replaced With Image Installations

Starting with Oracle Database 18c, existing services are no longer migrated by the installation. Use Database Upgrade Assistant (DBUA) to migrate services.

If you have an existing Oracle Database with services that you want to migrate, then to migrate those services, you must install the new release Oracle Database software in the Oracle home, and then start DBUA.

On Windows, to migrate the Microsoft Transaction Service to the new Oracle home, you must also run the command %ORACLE_HOME%\bin\oramtsctl.exe -new

Parent topic: Feature Changes for Oracle Database 18c Upgrade Planning

RPM-Based Oracle Database Installation

On Linux systems, you can install Oracle Database 18c with an RPM image installation.

Starting with Oracle Database 18c, you can use the Oracle Preinstallation RPM and the rpm -ivh command to perform an RPM-based Oracle Database installation. RPM-based installation is supported for single-instance Oracle Database on Oracle Linux systems only.

Parent topic: Feature Changes for Oracle Database 18c Upgrade Planning

Token Limitations for Oracle Text Indexes

Starting with Oracle Database Release 18c, the indexed token maximum size is increased to 255 characters for single-byte character sets.

Before Oracle Database Release 18c, all Oracle Text index types except SDATA sections stored tokens in a table column of type VARCHAR2 (64 BYTE). Starting with Oracle Database Release 18c, all Oracle Text index types except CTXCAT and CTXRULE indexes store tokens in VARCHAR2 (255 BYTE) table column types. This change is an increase for the maximum size of an indexed token to 255 characters for single-byte character sets. The size increase is less with multibyte or variable-length character sets. Tokens longer than 255 bytes are truncated. Truncated tokens do not prevent searches on the whole token string. However, the system cannot distinguish between two tokens that have the same first 255 bytes.

Note:

Before Oracle Database Release 18c, tokens that were greater than 64 bytes were truncated to 64 bytes. After upgrading to Oracle Database Release 18c, the token tables are increased to 255 bytes from 64 bytes. Searches with more than 64 bytes in the search token (that is, any single word in search string) cannot find any tokens which were truncated to 64 bytes. To avoid this problem, rebuild the index. If you never use search tokens longer than 64 bytes, it is not necessary to rebuild the index.

SDATA sections store tokens in a table column of type VARCHAR2 (249 BYTE). CTXCAT and CTXRULE indexes store tokens in a table column of type VARCHAR2 (64 BYTE).

Parent topic: Feature Changes for Oracle Database 18c Upgrade Planning

Changes to /ALL/USER/DBA User View and PL/SQL External Libraries

Starting in Oracle Database 18c, there are changes to the /USER/ALL/DBA_ARGUMENTS and /USER/ALL/DBA_IDENTIFIERS views, and to LIBRARY object creation in PDBs.

Review the changes that can affect your work.

ALL/USER/DBA_ARGUMENTS User Views Changes

ARGUMENTS views contain fewer rows. In particular, only top-level (DATA_LEVEL=0) items are stored in the ARGUMENTS views.

In earlier Oracle Database releases, the PL/SQL compiler collected metadata for all nested types in a PL/SQL datatype. DATA_LEVEL represented the nesting level of the type. Starting in Oracle Database 18c, only top-level type metadata (DATA_LEVEL=0) is stored in the ARGUMENTS views.

For instance: Note the changes in the create-or-replace package NestedTypesExample:

Type Level2Record is RECORD (Field1 NUMBER);
Type Level1Collection is TABLE of Level2Record index by binary_integer;
Type Level0Record is RECORD (Field1 Level1Collection);
Procedure NestedTypesProc (Param1 Level0Record);

In previous Oracle Database releases, the top-level type of the NestedTypeProc procedure, parameter Param1, Level0Record, is returned, and also an expanded description of all the nested types within Level0Record. For example:

SQL> select argument_name,type_subname,position,sequence,data_level from user_arguments where object_name='NESTEDTYPESPROC';
ARGUMENT_NAME   TYPE_SUBNAME      POSITION   SEQUENCE  DATA_LEVEL 
--------------- ----------------- ---------- ---------- --------- 
PARAM1          LEVEL0RECORD             1          1           0
FIELD1          LEVEL1COLLECTION         1          2           1 
                LEVEL2RECORD             1          3           2
FIELD1                                   1          4           3

In contrast, the same query in an 18.1 database returns the following:

ARGUMENT_NAME   TYPE_SUBNAME      POSITION   SEQUENCE  DATA_LEVEL 
--------------- ----------------- ---------- ---------- --------- 
PARAM1          LEVEL0RECORD             1          1           0

In releases earlier than Oracle Database 12c (12.1), PL/SQL package type descriptive metadata was not accessible in the way that metadata is accessible for top-level object types. With Top-level object types and collections, you can query ALL_TYPES and the associated user views, ALL_TYPE_ATTRS, and ALL_COLL_TYPES, to obtain type metadata. However, before Oracle Database 12.1, there was no way to obtain type metadata for PL/SQL package types, such as records and packaged collections. Function or procedure parameters that referenced those PL/SQL package types resulted in publishing all metadata about these types in the ARGUMENTS views, including any nested types.

The problem with this approach is that deeply nested types can consume extensive memory in the SYS tablespace. Also, because there is no way to share the type metadata in the ARGUMENTS views, each parameter with deeply nested types required its own redundant copy of the type metadata. The amount of metadata in the ARGUMENTS views and SYS tablespace, can lead to various issues, including PL/SQL compiler performance degradation. The degradation is caused because of the time it takes PL/SQL to update rows in the underlying dictionary tables.

In the Oracle Database 12.1 release, PL/SQL introduced enhanced support for package types, including the new user views, ALL_PLSQL_TYPES, ALL_PLSQL_TYPE_ATTRS, and ALL_PLSQL_COLL_TYPES. As the names imply, these views are similar to the ALL_TYPES view family. However, you can use the enhanced PL/SQL type views to query metadata about PL/SQL package types, instead of top-level object and collection types.

Because of the package types added with Oracle Database 12.1, there is no longer a need to insert large amounts of descriptive metadata into the ARGUMENTS views. A single row of metadata that includes the type name is all that is required in the ARGUMENTS views for each parameter type. You can obtain a full description of the type name in a query against the PL/SQL type views, and any nested types.

OCIDescribeAny() is based on the same metadata used by the ARGUMENTS views. OCIDescribeAny() also returns a single row for each parameter type, instead of the multiple rows commonly returned before the change in Oracle Database 12.1.

ALL/DBA/USER_ARGUMENTS contains a new column type, TYPE_OBJECT_TYPE. To determine the type of the type described by TYPE_OWNER, TYPE_NAME and TYPE_SUBNAME, you use the TYPE_OBJECT_TYPE column. The possible values include TABLE, VIEW, PACKAGE, and TYPE.

If you prefer to continue to collect the ALL_TYPES and the associated user views, ALL_TYPE_ATTRS and ALL_COLL_TYPES in ARGUMENTS views, then you can set events to events='10946, level 65536'. Setting this event reverts the ARGUMENTS views back to the behavior in Oracle Database releases earlier than 12.1, in which DATA_LEVEL can be greater than 0, and descriptive metadata for the type and any nested types is included in the view. If you make this change, then you must recompile affected packages after you set the event. When you recompile the affected packages, the compiler recollects the additional metadata. This event also reverts OCIDescribeAny() to the behavior in Oracle Database releases earlier than 12.1.

Starting in Oracle Database 12c release 1 (12.1.0.2), if you enter a procedure with no arguments, then the ARGUMENTS views do not have any rows. This change is an additional change that is separate from the row reduction change to ARGUMENTS views. Before Oracle Database 12.1.0.2, a procedure with no arguments was presented as a single row in the ARGUMENTS views.

USER/ALL/DBA_IDENTIFIERS User View Changes

Starting with Oracle Database 18c, PL/Scope is enhanced to capture additional information about user identifiers in PL/SQL code. The additional information includes constraints placed on the identifiers, and an indicator that notes when a function is a SQL builtin in PL/SQL.

The following columns are new in the USER/ALL/DBA_IDENTIFIERS views in Oracle Database 18c:

CHARACTER_SET: This column contains the value of the character set clause, when the column is used in a variable identifier declaration. The possible values are CHAR_CS, NCHAR_CS, and IDENTIFIER, when the character set is derived from another variable identifier.
ATTRIBUTE: This column contains the attribute value when %attribute is used in a variable declaration. The possible values are ROWTYPE, TYPE, and CHARSET.
CHAR_USED: This column contains the type of the length constraint when a constraint is used in a string length constraint declaration. The possible values are CHAR and BYTE.
LENGTH: This column contains the numeric length constraint value for a string length constraint declaration.
PRECISION: This column contains the numeric precision when it is used in a variable declaration.
PRECISION2: This column contains the numeric second precision value (for instance, interval types) used in a variable declaration.
SCALE: This column contains the numeric scale value used in a variable declaration.
LOWER_RANGE: This column contains the numeric lower range value used by a variable declaration with a range constraint.
UPPER_RANGE: This column contains the numeric upper range value used by a variable declaration with a range constraint.
NULL_CONSTRAINT: When a NULL constraint is used by a variable declaration, this column is set. The possible values are NULL, or NOT NULL.
SQL_BUILTIN: When an identifier is a SQL builtin used in a SQL statement issued from PL/SQL, this column is set to YES. If the identifier is not a SQL builtin, then the column is set to NO.

PL/SQL EXTERNAL LIBRARY Changes

Starting with Oracle Database 18c, the methods change for how to create LIBRARY objects in an Oracle Database 18c PDB with a pre-defined PATH_PREFIX.

When you create a new LIBRARY object in a PDB that has a predefined PATH_PREFIX, the LIBRARY must use a DIRECTORY object. The DIRECTORY object enforces the rules of PATH_PREFIX for the LIBRARY object. Failure to use a DIRECTORY object in the LIBRARY object results in a PLS-1919 compile-time error.
If a database is plugged into a CDB as a PDB with a predefined PATH_PREFIX, then attempts to use a LIBRARY object that does not use a DIRECTORY object result in an ORA-65394 runtime error. The LIBRARY object is not invalidated. However, to make the LIBRARY useful (as opposed to always issuing a runtime error), you must recreate the LIBRARY object so that it uses a DIRECTORY object.

These changes enhance the security and manageability of LIBRARY objects in a PDB by accounting for the value of the PATH_PREFIX, which describes where the LIBRARY dynamic link library (DLL) can appear in the file system. The use of a DIRECTORY object also allows administrators to determine which users can access the DLL directory.

Parent topic: Feature Changes for Oracle Database 18c Upgrade Planning

Symbolic Links and UTL_FILE

You cannot use UTL_FILE. with symbolic links. Use directory objects instead.

After an upgrade if applications address the database using symbolic links through, UTL_FILE, then these links fail. Oracle recommends that you use directory objects. If necessary, you can create real files that are the targets of file names in UTL_FILE..

Example 8-1 Example of Error Messages with UTL_FILE And Symbolic Links

Applications that use symbolic links that address UTL_FILE encounter an error. For example. suppose you attempt to create a symbolic link, where Ia.c is a symbolic link file:

create or replace directory TEMP as '/home/PLSQL/TEMP';

declare
f utl_file.file_type;
begin
f := utl_file.fopen('TEMP','la.c','r');
end;
/

This command fails with the following errors:

ERROR at line 1: 
ORA-29283: invalid file operation 
ORA-06512: at "SYS.UTL_FILE", line 536 
ORA-29283: invalid file operation 
ORA-06512: at line 4

Parent topic: Feature Changes for Oracle Database 18c Upgrade Planning

Deprecation of Direct Registration of Listeners with DBCA

Using Database Configuration Assistant (DBCA) to register Oracle Database to Oracle Internet Directory (OID) is deprecated in Oracle Database 18c.

Instead of using DBCA to migrate or register listeners to a database home during an upgrade, use Net Configuration Assistant or Net Manager to create a LISTENER.ORA file for the new release Oracle home, and then start this listener. You can also use DBCA to de-register and register listeners again to OID.

Parent topic: Feature Changes for Oracle Database 18c Upgrade Planning