Use these feature changes to help prepare for changes that you include as part of your planning for Oracle Database 18c upgrades.
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 (188.8.131.52) and later releases is 255 bytes. In previous releases, JSON search indexes that were created did not index key names greater than 64 bytes.
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
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.
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
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.
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).
CTXRULE indexes store tokens in a table column of type
VARCHAR2 (64 BYTE).
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_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
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
For instance: Note the changes in the create-or-replace package
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
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_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_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
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_SUBNAME, you use the
TYPE_OBJECT_TYPE column. The possible values include
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 (184.108.40.206), 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 220.127.116.11, a procedure with no arguments was presented as a single row in the
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
IDENTIFIER, when the character set is derived from another variable identifier.
ATTRIBUTE: This column contains the attribute value when
%attributeis used in a variable declaration. The possible values are
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
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
NULLconstraint is used by a variable declaration, this column is set. The possible values are
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
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
When you create a new
LIBRARYobject in a PDB that has a predefined
LIBRARYmust use a
DIRECTORYobject enforces the rules of
LIBRARYobject. Failure to use a
DIRECTORYobject in the
LIBRARYobject results in a
If a database is plugged into a CDB as a PDB with a predefined
PATH_PREFIX, then attempts to use a
LIBRARYobject that does not use a DIRECTORY object result in an
ORA-65394runtime error. The
LIBRARYobject is not invalidated. However, to make the
LIBRARYuseful (as opposed to always issuing a runtime error), you must recreate the
LIBRARYobject so that it uses a
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.
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
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.