1. SQL Language Reference
  2. Changes in This Release for Oracle Database SQL Language Reference

Changes in This Release for Oracle Database SQL Language Reference

This preface contains:

Changes in Oracle Database 18c, Version 18.1

The following are changes in Oracle Database SQL Language Reference for Oracle Database 18c, Version 18.1.

New Features

The following features are new in this release:

New Features

Analytic View Enhancements

  • Filtered facts and added measures for analytic view queries

    The WITH and FROM clauses of a SELECT statement now have syntax with which you can filter the fact data before aggregation in a query of an analytic view and add new calculated measures to include in a query.

    See SELECT.

  • Analytic view measure expressions

    The expressions that you can use in the calculated measures of an analytic view now include the following functions:

    • The hierarchy functions:

      HIER_CAPTION  HIER_DESCRIPTION  HIER_MEMBER_NAME
HIER_DEPTH    HIER_LEVEL        HIER_MEMBER_UNIQUE_NAME

    • The analytic and aggregation functions:

      APPROX_PERCENTILE  REGR_AVGX            STATS_MW_TEST
APPROX_MEDIAN      REGR_AVGY            STATS_ONE_WAY_ANOVA
CORR               REGR_COUNT           STATS_T_TEST_INDEP
CORR_K             REGR_INTERCEPT       STATS_T_TEST_ONE
CORR_S             REGR_SLOPE           STATS_T_TEST_PAIRED
COUNT              REGR_R2              STATS_WSR_TEST
COVAR_POP          REGR_SXX             STDDEV
COVAR_SAMP         REGR_SXY             STDDEV_POP
CUME_DIST          REGR_SYY             STDDEV_SAMP
DENSE_RANK         STATS_BINOMIAL_TEST  STATS_MODE
PERCENTILE_CONT    STATS_CROSSTAB       VAR_POP
PERCENTILE_DISC    STATS_F_TEST         VAR_SAMP
PERCENT_RANK       STATS_KS_TEST        VARIANCE
RANK

    Also, window functions now allow the use of any SQL aggregate function.

    See Analytic View Expressions.

  • Support for snowflake schemas and denormalized fact tables

    The JOIN PATH keywords in a CREATE ATTRIBUTE DIMENSION statement support the use of tables in snowflake schemas.

    The REFERENCES DISTINCT keywords in a CREATE ANALYTIC VIEW statement support the use of denormalized fact tables.

Automating Correction of Non-logged blocks in Dataguard Standby

Standby nologging instructs the database to not log operations that qualify to be done without logging, and to send the data blocks that were created by the operation to each qualifying standby database in the Data Guard configuration, typically resulting in those standbys not having invalid blocks.

You can set standby no logging for load performance or data availability in the following statements:

  • ALTER DATABASE

  • ALTER PLUGGABLE DATABASE

  • CREATE DATABASE

  • CREATE CONTROLFILE

Ability to Create Schema Only Accounts

You can now use the NO AUTHENTICATION clause with the CREATE USER statement to create a user account that does not require external authentication by password to log in. To remove external authentication by password on an existing user account you must use this clause with the ALTER USER statement.

Shadow Lost Write Protection

Shadow lost write protection provides fast detection and immediate response to a lost write, thus minimizing the data loss that can occur in a database due to data corruption. This feature creates a storage area for lost write records called a shadow tablespace. You can enable shadow lost write protection for a tablespace, a database, or a data file without requiring an Oracle Data Guard standby database.

You can use the lost_write_protection clause with the following statements:

  • CREATE TABLESPACE

  • ALTER TABLESPACE

  • DROP TABLESPACE

  • ALTER DATABSAE

  • ALTER PLUGGABLE DATABASE

Data-Bound Collation Support

Table partitioning key columns can now have any declared collations. In the earlier Oracle Database release, the table partitioning key columns were restricted to have only BINARY, USING_NLS_COMP, USING_NLS_SORT, and USING_NLS_SORT_CS collations.You can now partition a table using character values in a case-insensitive way by declaring a case-insensitive collation for the table partitioning key column.

Private Temporary Tables

Private temporary tables are temporary database objects that are automatically dropped at the end of a transaction or a session. A private temporary table is stored in memory and is visible only to the session that created it. This gives application developers greater flexibility, easier code maintenance, and improved ready-to-use functionality.

You can create a private temporary table using PRIVATE TEMPORARY in the CREATE TABLE statement. You can define the scope of the private temporary table, either transaction or session with the ON COMMIT clause.

Sharding Scale Clusters

Oracle RAC Sharding affinitizes table partitions to Oracle RAC instances. The affinity logically partitions data across RAC instances so that a distinct subset of data is assigned to each instance. You can use the affinity_clauses in the ALTER SYSTEM statement to enable data-dependent routing to provide cache affinity on a RAC database. When data is accessed with a sharding key, the request will be routed to the instance that holds the corresponding subset of data. The benefits of affinity are:

  • Sharded access for shard-aware applications and transparency for non-sharded applications.

  • Better cache utilization and reduced block pings.

Integration of Active Directory Services with Oracle Database

Starting with this release, you can authenticate and authorize users directly with Microsoft Active Directory. The direct integration with directory services supports better security through faster and easier configuration with the enterprise identity management architecture. In the past, users may have avoided the security practice of integrating the database with directory services due to the difficulty and complexity. With the direct integration, you can improve your security posture by more easily integrating Oracle Database to the enterprise directory service.

You can specify the GLOBALLY AS clause with the ALTER ROLE statement to map a directory group to a global role.

User-Defined Sharding Method

The user-defined sharding method in Oracle Sharding allows you to define a LIST or RANGE based partitioning strategy and explicitly specify mapping of data to individual shards.

User-defined sharding is used when certain data needs to be stored on a particular shard because of performance, regulatory, or other reasons, and you need to have full control over moving data between shards. User-defined sharding also provides that, in case of planned or unplanned downtime of a shard, you know exactly what data is not available. The cost of user-defined sharding is that it requires you monitor and maintain balanced distribution of data and workload across shards.

Ability to Create a Keystore for Each Pluggable Database

Each PDB keystore can have its own password and its own master encryption key. This design offers greater isolation between PDBs. It also enables independent key management operations to be performed by each tenant (PDB) in a multitenant environment rather than having to share a keystore at the CDB root level. This feature benefits both multitenant and non-multitenant environments, because it provides parameters to facilitate the configuration of the keystore location and the keystore type, eliminating the need for editing the sqlnet.ora file.

The ADMINISTER KEY MANAGEMENT statement has been extended with two modes, isolated and united.

Ability to Encrypt Sensitive Credential Data in the Data Dictionary

Starting with this release, you can encrypt sensitive credential data that is stored in the data dictionary SYS.LINK$ and SYS.SCHEDULER$_CREDENTIAL system tables.

In previous releases including this release, the data in these tables is obfuscated. However, because of the rise of de-obfuscation algorithms that are available on the Internet, it is important to use a more secure solution to protect this type of sensitive data. You can manually encrypt this data by using the ALTER DATABASE DICTIONARY statement.

Ability to Create a User-Defined Master Encryption Key

This release introduces the ability to create a user-defined master encryption key, also known as “bring your own key”.

To create the user-defined key, you supply your own master key identification value when you create the master encryption key. You can use the the ADMINISTER KEY MANAGEMENT SET [ENCRYPTION] KEY or ADMINISTER KEY MANAGEMENT CREATE [ENCRYPTION] KEY statements. This enhancement applies to master encryption keys that are used in software keystores only, not hardware keystores. It can be used in non-multitenant, standalone environments and in multitenant environments.

Unicode 9.0 Support

This enhancement enables Oracle Database to conform to the latest version of the Unicode Standard.

Convert Normal or High Redundancy Disk Groups to Flex Disk Groups without Restricted Mount

You can convert a conventional disk group, that is a disk group created before Oracle ASM release 18c, to an Oracle ASM flex disk group without using the MOUNTED RESTRICTED option.

Dropping Oracle ASM File Groups With a Cascade Option

You can drop a non-empty file group and its associated files using the CASCADE keyword with ALTER DISKGROUP ... DROP FILEGROUP statement.

Oracle ASM Flex Disk Group Support for Multitenant Cloning

You can create an Oracle ASM mirror clone of a container database or a pluggable database in a multitenant enviroment. The Oracle ASM file group properties, and the statements CREATE DATABASE and CREATE PLUGGABLE DATABASE have been extended to support this feature.

Modifying the Partitioning Strategy

You can change the partitioning strategy of a regular (heap-organized) table with the ALTER TABLE MODIFY PARTITION SQL statement. Modifying the partitioning strategy, such as hash partitioning to range partitioning, can be performed offline or online. Indexes are maintained as part of the table modification. When performed in online mode, the conversion has no impact on ongoing DML operations

Online Merging of Partitions and Subpartitions

You can use the ONLINE keyword with the ALTER TABLE MERGE PARTITION and ALTER TABLE MERGE SUBPARTITION statements to enable online merge operations for regular (heap-organized) tables, providing concurrent data manipulation language (DML) operations with the ongoing partition merge operation.

Inline External Tables

Inline external tables enable the runtime definition of an external table to be part of a SQL statement, thus eliminating the need to create an external table as a persistent database object in the data dictionary.

The query_table_expression clause of the SELECT statement has two new clauses: inline_external_table and modified_external_table.

Centralized Diagnosability and Manageability for Sharded Databases

The new SHARDS clause enables users to query Oracle supplied objects, such as V$, DBA/USER/ALL views and dictionary tables across all shards from a central shard catalog. A query with the SHARDS clause can only be executed on the shard catalog database.

The SHARDS clause is part of the table_reference clause in the SELECT statement.

Memoptimized Rowstore

The memoptimized rowstore improves the data query performance of the applications, such as Internet of Things (IoT), which frequently query tables based on primary key columns.

You can specify the memoptimize_read_clause in the ALTER TABLE and CREATE TABLE statements to enable fast lookup.

Database In-Memory Support for External Tables

The In-Memory Column Store (IM column store) supports population of external tables, enabling users to perform advanced analytical queries on external data stores. This feature is particularly valuable for repeated queries of external data because it leverages the performance features of Oracle Database In-Memory and avoids unnecessary access of external storage.

You can use the new inmemory_table_clause of the CREATE TABLE statement.

Approximate Top-N Query Processing

By making use of approximate query processing, you can instantly improve the performance of existing analytic workloads and enable faster ad-hoc data exploration.

Three new functions are added:

  • APPROX_COUNT

  • APPROX_SUM

  • APPROX_RANK

SQL Enhancements for JSON

Multiple SQL enhancements are provided for storing and querying JSON data in the database.

  • TREAT (... AS JSON) lets you specify that the return value from a given SQL expression is to be treated as JSON data. Such expressions can include PL/SQL function calls and columns specified by a SQLWITH clause. New data-guide views make it easy to access path and type information for JSON fields, which is recorded for index-backed data guides. Returning generated and queried JSON data in LOB instances widens the scope of the use of relational data.

  • SQL/JSON query and generation functions can return results as LOB data.

  • SQL/JSON generation functions can accept input in a variety of SQL data types, and they can optionally be required to return well-formed JSON data.

  • SQL/JSON path expressions accept additional item methods, to filter data that is not convertible to a given SQL data type. Item methods can be used in more SQL/JSON functions and can return LOB data.

  • You can use data-dictionary views to extract JSON field path and type information that is recorded in a data guide-enabled JSON search index.

  • SQL/JSON function JSON_TABLE supports simpler syntax: dot-notation to access fields and automatic naming of projected columns. Materialized views created using JSON_TABLE can be automatically synchronized.

Non-Persistable Data Types

Non-persistable data types extend SQL data types to allow PL/SQL data types to be used when defining type attributes. Ensures that SQL data types created by these groups cannot be persisted to disk. You can use this feature to enable smoother upgrades and downgrades.

The CREATE TYPE statement describes the rules and restrictions of non-persistable data types.

PDB Lockdown Profile Enhancements

This feature benefits environments that need enforced security and isolation in PDB provisioning. This release introduces several enhancements for PDB lockdown profiles.

  • You now can create PDB lockdown profiles in the application root, as well as in the CDB root. In previous releases, you only could create the profile in the CDB root. The ability to create a PDB lockdown profile in an application container enables you to more finely control access to the applications that are associated with the application container.

  • You now can create a PDB lockdown profile that is based on another PDB lockdown profile, either a static base profile or a dynamic base profile. You can control whether subsequent changes to the base profile are reflected in the newly created profile that uses the base profile.

The following statements contain the new enhancements:

  • CREATE LOCKDOWN PROFILE

  • ALTER LOCKDOWN PROFILE

  • DROP LOCKDOWN PROFILE

Manual termination of Run-Away Queries

This feature enables the database administrator to terminate a runaway SQL query using the ALTER SYSTEM CANCEL SQL statement. This frees the system resources consumed by the runaway SQL query so that other SQL queries can use them.

Cloud-Scale PDB Management Using a CDB Fleet

A CDB fleet is a collection of CDBs that you manage as one logical CDB. A lead CDB is the central location for monitoring and managing the CDBs in the fleet. After you configure the fleet, PDB information from the various CDBs is synchronized with the lead CDB. All PDBs in the fleet are now accessible in the lead CDB, enabling you to manage the PDBs as if they were all contained within the lead CDB.

A CDB is limited to 4096 PDBs. By configuring a CDB fleet, you can access an order of magnitude more PDBs within a single lead CDB. In this way, you reduce operational costs and minimize risk in the management of cloud-scale applications.

You can configure a CDB fleet using the ALTER DATABASE statement.

PDB Snapshot Carousel

A PDB snapshot is a named copy of a PDB at a specific point in time. When a PDB is enabled for PDB snapshots, you can create up to eight snapshots of it. The set of snapshots is called a snapshot carousel.

A PDB snapshot carousel is a useful way of maintaining a frequently refreshed library of recent copies of PDBs. You can perform either point-in-time recovery or point-in-time cloning of any snapshot in the carousel. For example, if you take a snapshot of a PDB at 12:01 a.m. every morning, then you can restore a snapshot and roll it forward to any desired timestamp or SCN within the last week.

You can create and manage PDB snapshots using the snapshot_clauses of the ALTER PLUGGABLE DATABASE statement.

Deprecated Features

The following features are deprecated in this release, and may be desupported in a future release:

  • SQL/JSON functions returning a Boolean JSON value as a number (zero or one). This will be desupported in the future. When desupported, an error will be raised if NUMBER is returned for a JSON value of true or false.

  • Oracle Multimedia is deprecated in Oracle Database Release 18c, and may be desupported in a future release. Oracle recommends that you stop using deprecated features as soon as possible. There is no replacement for Oracle Multimedia. Oracle recommends that you replace Oracle Multimedia by using Oracle SecureFiles with third-party products for metadata extraction and image processing and conversion.

Desupported Features

Please see theOracle Database Upgrade Guide for a list of desupported features for 18c Version 18.1.

Changes in Oracle Database 12c Release 2 (12.2.0.1)

The following are changes in Oracle Database SQL Language Reference for Oracle Database 12c Release 2 (12.2.0.1).

New Features

The following features are new in this release:

Long Identifiers

The maximum length for most database object names has increased from 30 bytes to 128 bytes.

See Database Object Naming Rules.

Data-Bound Collation and Case-Insensitive Databases

Data-bound collation allows you to declare character comparison rules at the column level. The collation declared for a column is automatically applied to all collation-sensitive SQL operations that reference the column. This enables applications to consistently apply language-specific comparison rules to the exact data that requires these rules. Data-bound collation also allows you to declare a case-insensitive collation for a table or a schema, so that all columns in a table or schema can be compared in a case-insensitive manner. This enables you to create a case-insensitive database.

See Data-Bound Collation.

Features that Introduce New SQL Statements

The following features introduce new SQL statements:

ADMINISTER KEY MANAGEMENT Enhancements

ALTER SESSION Enhancement

  • When you switch to a different container in a CDB, you can specify the service you would like to use in the new container.

    See the new clause SERVICE.

AUDIT and NOAUDIT (Unified Auditing) Enhancements

  • You can enable or disable unified audit policies for users who have been directly granted specific roles.

    See the new clauses by_users_with_roles of AUDIT and NOAUDIT.

CREATE DATABASE and ALTER DATABASE Enhancements

  • You can specify a local temporary tablespace for your database.

    See the clause default_temp_tablespace of CREATE DATABASE and the DEFAULT [LOCAL] TEMPORARY TABLESPACE Clause of ALTER DATABASE.

  • You can specify local undo mode or shared undo mode for a CDB.

    See the new undo_mode_clause of CREATE DATABASE and undo_mode_clause of ALTER DATABASE.

  • For Oracle Real Application Clusters (Oracle RAC) or Oracle RAC One Node databases, you can control the number of instances on a physical standby that Redo Apply uses.

    See the new clause USING INSTANCES of ALTER DATABASE.

  • You can perform offline encryption or decryption of a data file using Transparent Data Encryption (TDE).

    See the new clauses ENCRYPT | DECRYPT of ALTER DATABASE.

CREATE DIRECTORY Enhancement

  • You can create directories that are application common objects. Application common objects can be shared by application PDBs in an application container.

    See the new clause SHARING of CREATE DIRECTORY.

CREATE DISKGROUP and ALTER DISKGROUP Enhancements

  • You can create Oracle ASM flex disk groups, which support quota groups and file groups. Flex disk groups enable you to define a quota limit for the files of a group of databases within a disk group.

    See:

  • You can pause, restart, and change the power of active disk group rebalance operations. You can also specify which phases of a rebalance operation to perform.

    See the rebalance_diskgroup_clause of ALTER DISKGROUP.

  • The following new Oracle ASM disk group attributes are listed in Table 13-2:

    • LOGICAL_SECTOR_SIZE allows you to set the logical sector size of a disk group.

    • PREFERRED_READ.ENABLED allows you to specify whether preferred read functionality is enabled for a disk group in an Oracle extended cluster.

CREATE INDEX, ALTER INDEX, and DROP INDEX Enhancements

  • You can specify whether to invalidate dependent cursors while creating an index, rebuilding an index, marking an index UNUSABLE, or dropping an index.

    See the new clause { DEFERRED | IMMEDIATE } INVALIDATION of CREATEINDEX, ALTERINDEX, and DROPINDEX.

  • Advanced index compression provides a HIGH compression level.

    See the new HIGH keyword of the clause advanced_index_compression of CREATE INDEX.

CREATE JAVA Enhancement

  • You can create Java schema objects that are application common objects. Application common objects can be shared by application PDBs in an application container.

    See the new clause SHARING of CREATE JAVA.

CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW Enhancements

  • A real-time materialized view provides fresh data to user queries even when the materialized view is not in sync with its base tables due to data changes.

    See the new clause { ENABLE | DISABLE } ON QUERY COMPUTATION of CREATEMATERIALIZEDVIEW and ALTERMATERIALIZEDVIEW.

  • The ON STATEMENT refresh mode causes an automatic refresh to occur every time a DML operation is performed on any of a materialized view's base tables.

    See the new ON STATEMENT Clause of CREATE MATERIALIZED VIEW.

CREATE PLUGGABLE DATABASE and ALTER PLUGGABLE DATABASE Enhancements

  • An application container is a component of a CDB that stores data for one or more applications. It consists of an application root, an optional application seed, and application PDBs.

    See the new clauses AS APPLICATION CONTAINER and AS SEED of CREATE PLUGGABLE DATABASE.

  • You can perform the following operations in an application container:

    • Install, patch, upgrade, and uninstall applications

    • Register application versions and patch numbers

    • Keep applications in sync between the application root and application PDBs

    See the new application_clauses of ALTER PLUGGABLE DATABASE.

  • You can create a refreshable PDB when cloning a PDB. Changes in the source PDB can be propagated to the refreshable PDB, either manually or automatically.

    See the new pdb_refresh_mode_clause of CREATE PLUGGABLE DATABASE and the new clauses REFRESH and pdb_refresh_mode_clause of ALTER PLUGGABLE DATABASE.

  • A proxy PDB references a PDB in a different CDB and provides fully functional access to the referenced PDB.

    See the new clauses AS PROXY FROM and HOST and PORT of CREATE PLUGGABLE DATABASE.

  • You can relocate a PDB from one CDB to another.

    See the new clause RELOCATE of CREATE PLUGGABLE DATABASE.

  • When cloning a PDB, you can instruct the database to clone a tablespace using storage snapshots or clone the data model definition of a tablespace, but not the tablespace's data.

    See the new clauses { SNAPSHOT COPY | NO DATA } of CREATE PLUGGABLE DATABASE.

  • When plugging in a PDB, you can instruct the database to copy or move tablespace files to a new location.

    See the new clauses { COPY | MOVE | NOCOPY } of CREATE PLUGGABLE DATABASE.

  • In earlier releases, you could specify a permanent default tablespace only when creating a PDB from seed. You can now also specify a permanent default tablespace when cloning a PDB or plugging in a PDB.

    See the clause default_tablespace of CREATE PLUGGABLE DATABASE.

  • You can use parallel execution servers to copy a new PDB's data files to a new location. This may result in faster creation of the PDB.

    See the new parallel_pdb_creation_clause of CREATE PLUGGABLE DATABASE.

CREATE PROFILE and ALTER PROFILE Enhancements

  • In profiles, you can specify the permitted number of consecutive days of no logins after which an account will be locked.

    See the new INACTIVE_ACCOUNT_TIME setting of CREATE PROFILE and ALTER PROFILE.

CREATE RESTORE POINT Enhancement

CREATE SEQUENCE Enhancement

  • You can create sequences that are application common objects. Application common objects can be shared by application PDBs in an application container.

    See the new clause SHARING of CREATE SEQUENCE.

CREATE SYNONYM Enhancement

  • You can create synonyms that are application common objects. Application common objects can be shared by application PDBs in an application container.

    See the new clause SHARING of CREATE SYNONYM.

CREATE TABLE and ALTER TABLE Enhancements

  • You can create partitioned external tables.

    See the external_table_clause of CREATE TABLE and the clause alter_external_table of ALTER TABLE.

  • You can specify constraints on external tables.

    See External Table Constraints.

  • You can specify up to 16 partitioning key columns for list-partitioned tables, and up to 16 subpartitioning key columns for composite-partitioned tables that use list subpartitioning. In earlier releases, you could specify only one partitioning or subpartitioning key column.

    See the list_values_clause of CREATE TABLE and ALTER TABLE.

  • You can create an automatic list-partitioned table. This type of table enables the database to create additional list partitions on demand.

    See the new clauses AUTOMATIC of CREATE TABLE and alter_automatic_partitioning of ALTER TABLE.

  • You can change a nonpartitioned table to a partitioned table.

    See the new clause modify_to_partitioned of ALTER TABLE.

  • You can create a table that matches the structure of an existing partitioned table. The two tables are then eligible for exchanging partitions and subpartitions.

    See the new clause FOR EXCHANGE WITH TABLE of CREATE TABLE.

  • You can specify which rows to preserve during the following operations: moving, splitting, or merging table partitions or subpartitions; moving a table; or converting a nonpartitioned table to a partitioned table.

    See the new clause filter_condition of ALTER TABLE.

  • You can specify read-only mode for a table, partition, or subpartition.

    See the new read_only_clause of CREATE TABLE and ALTER TABLE.

  • DML operations on a table are allowed while splitting its partitions and subpartitions.

    See the new ONLINE keyword of the clauses split_table_partition and split_table_subpartition of ALTER TABLE.

  • Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.

    See the move_table_clause of ALTER TABLE.

  • You can specify whether to invalidate dependent cursors while performing table partition maintenance operations.

    See the new clause { DEFERRED | IMMEDIATE } INVALIDATION of ALTER TABLE.

  • You can create tables that are application common objects. Application common objects can be shared by application PDBs in an application container.

    See the new clause SHARING of CREATE TABLE.

  • Table column encryption supports the following additional algorithms: ARIA192, ARIA256, GOST256, and SEED128.

    See the clause encryption_spec of CREATE TABLE and ALTER TABLE.

  • When specifying default In-Memory Column Store (IM column store) settings for a table, you can specify the Oracle RAC or Oracle Active Data Guard instances in which the table is eligible to be populated in the IM column store.

    See the new clause FOR SERVICE of CREATE TABLE and ALTER TABLE.

  • You can create Automatic Data Optimization policies that enable, disable, or recompress tables in the IM column store.

    See the new clause ilm_inmemory_policy of CREATE TABLE and ALTER TABLE.

  • Automatic Data Optimization compression policies support an additional compression method: COLUMN STORE COMPRESS FOR QUERY.

    See the clause ilm_compression_policy of CREATE TABLE and ALTER TABLE.

CREATE TABLESPACE and ALTER TABLESPACE Enhancements

  • Tablespace encryption enhancements: You can encrypt both offline and online tablespaces. You can encrypt undo tablespaces, temporary tablespaces, and the SYSTEM and SYSAUX tablespaces. Tablespace encryption supports the following additional algorithms: ARIA192, ARIA256, GOST256, and SEED128.

    See the new tablespace_encryption_clause of CREATE TABLESPACE and the new clause alter_tablespace_encryption of ALTER TABLESPACE.

  • You can create local temporary tablespaces, which are useful for Oracle Real Application Clusters and Oracle Flex Clusters. They store a separate, nonshared temp file for each database instance, which can improve I/O performance.

    See the new LOCAL TEMPORARY TABLESPACE clause of the temporary_tablespace_clause of CREATE TABLESPACE.

  • You can specify a default index compression method for a tablespace.

    See the new clause default_index_compression of CREATE TABLESPACE and the clause default_tablespace_params of ALTER TABLESPACE.

  • In earlier releases of Oracle Database, you could specify a default table compression method for a tablespace. You can still do this in Oracle Database 12c Release 2 (12.2), however, the syntax now includes the TABLE keyword to differentiate it from the new default index compression syntax.

    See the new clause default_table_compression of CREATE TABLESPACE and the clause default_tablespace_params of ALTER TABLESPACE.

CREATE USER and ALTER USER Enhancements

  • You can assign a local temporary tablespace to a user.

    See the new LOCAL keyword for the TEMPORARY TABLESPACE clause of CREATEUSER and ALTERUSER.

CREATE VIEW Enhancement

  • You can create views that are application common objects. Application common objects can be shared by application PDBs in an application container.

    See the new clause SHARING of CREATE VIEW.

FLASHBACK DATABASE Enhancement

  • You can flash back a PDB.

    See the new clause PLUGGABLE of FLASHBACK DATABASE.

SELECT Enhancement

  • The query_table_expression clause of the FROM clause of the SELECT statement now accepts a hierarchy or an analytic view in a subquery.

    See the clause query_table_expression of SELECT.

New Operator

  • The new COLLATE operator determines the collation for an expression. This operator enables you to override the collation that the database would have derived for the expression using standard collation derivation rules.

    See COLLATE Operator.

New or Enhanced Expressions

  • The new analytic view expressions define calculated measures in an analytic view.

    See Analytic View Expressions.

  • JSON object access expressions have been enhanced to let you access specific elements of a JSON array.

    See the clause array_step of JSON Object Access Expressions.

Enhanced Condition

  • The JSON_EXISTS condition now lets you pass values to the path expression.

    See the new clause JSON_passing_clause of JSON_EXISTS.

New or Enhanced Functions

  • Approximate Query Processing Functions

    The following new functions return approximate results with negligible deviation from the exact result:

    • APPROX_MEDIAN takes a numeric or datetime value and returns an approximate median value.

    • APPROX_PERCENTILE takes a percentile value and a sort specification, and returns the value that would fall into that percentile value with respect to the sort specification.

    The following new functions support materialized view-based summary aggregation strategies for approximate distinct value counts:

    • APPROX_COUNT_DISTINCT_DETAIL calculates information about the approximate number of rows that contain a distinct value for an expression and returns a BLOB value, called a detail, which contains that information in a special format.

    • APPROX_COUNT_DISTINCT_AGG takes as its input a column of details containing information about approximate distinct value counts, and enables you to perform aggregations of those counts.

    • TO_APPROX_COUNT_DISTINCT takes as its input a detail containing information about an approximate distinct value count, and converts it to a NUMBER value.

    The following new functions support materialized view-based summary aggregation strategies for approximate percentile values:

    • APPROX_PERCENTILE_DETAIL calculates approximate percentile information for the values and returns a BLOB value, called a detail, which contains that information in a special format.

    • APPROX_PERCENTILE_AGG takes as its input a column of details containing approximate percentile information, and enables you to perform aggregations of that information.

    • TO_APPROX_PERCENTILE takes as its input a detail containing approximate percentile information, a percentile value, and a sort specification, and returns an approximate value that would fall into that percentile value with respect to the sort specification.

  • Collation Functions

    The following new functions return information about collation settings:

    • COLLATION returns the name of the derived collation for an expression.

    • NLS_COLLATION_ID takes as its argument a collation name and returns the corresponding collation ID number.

    • NLS_COLLATION_NAME takes as its argument a collation ID number and returns the corresponding collation name.

  • Conversion Functions

    The following conversion functions now allow you to specify a value to be returned if a conversion error occurs:

    The following new function lets you determine whether an expression can be converted to a specified data type:

    The following new functions allow additional data types to be converted to BLOB, character, and CLOB values:

  • Data Mining Functions

    The data mining functions are enhanced so they can be applied to models built using the native algorithms of Oracle, as well as those built using R through the extensibility mechanism of Oracle Advanced Analytics. See Data Mining Functions.

    The following are new data mining functions:

    The syntax of the following functions is enhanced so that the functions can use the GROUPING hint when scoring a partitioned model:

  • JSON Functions

    The following new functions enable you to query and generate JavaScript Object Notation (JSON) data:

    • JSON_ARRAY takes as its input one or more SQL expressions, converts each expression to a JSON value, and returns a JSON array that contains those JSON values.

    • JSON_ARRAYAGG takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values.

    • JSON_DATAGUIDE takes as its input a table column of JSON data. Each row in the column is referred to as a JSON document. For each JSON document in the column, this function returns a CLOB value that contains a flat data guide for that JSON document.

    • JSON_OBJECT takes as its input one or more property key-value pairs, and returns a JSON object that contains an object member for each of those key-value pairs.

    • JSON_OBJECTAGG takes as its input a property key-value pair, constructs an object member for each key-value pair, and returns a single JSON object that contains those object members.

    The following JSON functions have been enhanced to let you specify a return value when no match is found:

  • LISTAGG Function

    • LISTAGG now allows you to control how the function behaves when the return value exceeds the maximum length of the return data type.

New or Enhanced Privileges

The following are new or enhanced system privileges and object privileges:

  • The following system privileges, which are listed in Table 18-1, have been enhanced:

    • CREATE JOB and CREATE ANY JOB now allow you to manage resource objects and incompatibility resource objects.

  • The following new system privileges are listed in Table 18-1:

    • These system privileges allow the creation and management of analytic views:

      CREATE ANALYTIC VIEW, CREATE ANY ANALYTIC VIEW, ALTER ANY ANALYTIC VIEW, DROP ANY ANALYTIC VIEW

      CREATE ATTRIBUTE DIMENSION, CREATE ANY ATTRIBUTE DIMENSION, ALTER ANY ATTRIBUTE DIMENSION, DROP ANY ATTRIBUTE DIMENSION

      CREATE HIERARCHY, CREATE ANY HIERARCHY, ALTER ANY HIERARCHY, and DROP ANY HIERARCHY

    • CREATE LOCKDOWN PROFILE, ALTER LOCKDOWN PROFILE, and DROP LOCKDOWN PROFILE allow you to manage PDB lockdown profiles.

    • INHERIT ANY REMOTE PRIVILEGES allows you to execute definer's rights procedures or functions that contain current user database links.

    • USE ANY JOB RESOURCE allows you to associate any schedule resource object with any program or job in the grantee’s schema.

  • The following new object privileges are listed in Table 18-2:

    • INHERIT REMOTE PRIVILEGES can be granted on a user to users and roles. It allows the user on whom this privilege is granted to execute definer's rights procedures or functions that contain current user database links and are owned by the grantee.

    • The USE privilege can be granted on job scheduler objects. It allows you to associate the specified scheduler resource object with programs and jobs.

New Hints

The following are new hints:

  • The CONTAINERS Hint lets you pass a hint to the query of each PDB in a CDB or application container during a SELECT ... containers_clause ... query.

  • The FRESH_MV Hint is part of the new real-time materialized view feature. This hint allows you to fetch up-to-date data from a stale real-time materialized view.

  • The GROUPING Hint applies to data mining scoring functions when scoring partitioned models.

  • The USE_BAND Hint and NO_USE_BAND Hint allow you to use or exclude band joins in a query. Band joins are new for this release. For more information, see Band Joins.

Deprecated Features

The following features are deprecated in this release, and may be desupported in a future release:

  • The Oracle Multimedia support for object types that comply with the first edition of the ISO/IEC 13249-5:2001 SQLMM Part5:StillImage standard (commonly referred to as the SQL/MM Still Image standard) is deprecated.

    See Oracle Multimedia Reference for more information.

  • The XMLROOT function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you instead use the SQL/XML function XMLSERIALIZE with a version number.

    See Oracle XML DB Developer's Guide for more information on the XMLSERIALIZE function.

Desupported Features

Some features previously described in this document are desupported in Oracle Database 12c Release 2 (12.2). See Oracle Database Upgrade Guide for a list of desupported features.

Changes in Oracle Database 12c Release 1 (12.1.0.2)

The following are changes in Oracle Database SQL Language Reference for Oracle Database 12c Release 1 (12.1.0.2).

New Features

The following features are new in this release:

  • The In-Memory Column Store (IM column store) is an optional, static SGA pool that stores copies of tables and partitions in a special columnar format optimized for rapid scans.

    See the inmemory_table_clause of CREATE TABLE, the inmemory_clause of CREATE TABLESPACE, and the inmemory_table_clause of CREATE MATERIALIZED VIEW

    See the following hints:

  • Oracle Database now supports JavaScript Object Notation (JSON).

    See the following conditions:

    See the following functions:

    See "JSON Object Access Expressions"

  • Attribute clustering lets you cluster table data in close physical proximity based on the content of specified columns.

    See the attribute_clustering_clause of CREATE TABLE and the attribute_clustering_clause of ALTER TABLE

    See the following hints:

  • Zone maps let you reduce the I/O and CPU costs of table scans by tracking the sets of contiguous data blocks, or zones, in which certain column values are stored. You can use zone maps with or without attribute clustering.

    See the statements CREATE MATERIALIZED ZONEMAP, ALTER MATERIALIZED ZONEMAP, and DROP MATERIALIZED ZONEMAP, and the zonemap_clause of CREATE TABLE

    See the NO_ZONEMAP Hint and the function SYS_OP_ZONE_ID

  • You can now create range-partitioned hash clusters.

    See the cluster_range_partitions clause of CREATE CLUSTER and the allocate_extent_clause of ALTER CLUSTER

  • The new function APPROX_COUNT_DISTINCT returns the approximate number of distinct values for a column. This function is an alternative to the COUNT function. It processes large amounts of data significantly faster than COUNT, with negligible deviation from the exact result.

    See APPROX_COUNT_DISTINCT

  • A new type of index compression called advanced index compression lets you improve compression ratios significantly while still providing efficient access to indexes.

    See the advanced_index_compression clause of CREATE INDEX

  • For tables compressed with Hybrid Columnar Compression, you can now control whether row-level locking is used during DML operations.

    See the [NO] ROW LEVEL LOCKING clause of CREATE TABLE

  • The database now supports force full database caching mode, which allows you to designate the entire database, including NOCACHE LOBs, as eligible for caching in the buffer cache.

    See the [NO] FORCE FULL DATABASE CACHING clause of ALTER DATABASE

  • When you grant a database role to a user who is responsible for CBAC grants, you can now specify WITH DELEGATE OPTION in the GRANT statement to prevent giving the grantee additional privileges on the role. WITH DELEGATE OPTION is an alternative to WITH ADMIN OPTION. It enables a role to be granted to program units, but it does not permit the granting of the role to other principals or the administration of the role itself.

    See the WITH DELEGATE OPTION clause of GRANT

  • The new READ object privilege and READ ANY TABLE system privilege allow users to query tables, materialized views, views, and their synonyms.

    The READ object privilege is an alternative to the SELECT object privilege. In addition to querying objects, the SELECT object privilege allows users lock rows of a table with the LOCK TABLE and SELECT ... FOR UPDATE statements. The READ object privilege only allows users to query objects. See Table 18-2 for more information.

    The READ ANY TABLE system privilege is an alternative to the SELECT ANY TABLE system privilege. In addition to querying objects, the SELECT ANY TABLE privilege allows users to lock rows of a table with the SELECT ... FOR UPDATE statement. The READ ANY TABLE privilege only allows users to query objects. See Table 18-1 for more information.

Changes in Oracle Database 12c Release 1 (12.1.0.1)

The following are changes in Oracle Database SQL Language Reference for Oracle Database 12c Release 1 (12.1.0.1).

New Features

The following features are new in this release:

Features that Introduce New SQL Statements

The following features introduce new SQL statements:

  • The multitenant architecture offers the capability that enables an Oracle database to function as a multitenant container database (CDB). A CDB is an Oracle database that includes one or more pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle client as a non-CDB. You can unplug a PDB from a CDB and plug it into a different CDB.

    See the following new statements:

  • Unified auditing provides a full set of enhanced auditing features. It enables you to create named unified audit policies, enable or disable unified audit policies, apply users to or exclude users from policies, and set whether an audit record is created if the audited behavior fails, succeeds, or both. It also enables you to capture application context values in audit records.

    See the following new statements:

  • A new unified key management interface for Transparent Data Encryption (TDE) eases key administration tasks, provides for better compliance and tracking, and improves separation of duty between the database administrator and security administrator.

    See the new ADMINISTER KEY MANAGEMENT statement.

ALTER DATABASE Enhancements

The following features provide enhancements to the ALTER DATABASE statement:

  • Storage Snapshot Optimization enables you to use a third-party storage snapshot of the database taken without backup mode for all types of recovery operations, including point-in-time recovery. The ALTER DATABASE statement has been enhanced with the new SNAPSHOT TIME clause to enable you to recover the database using such a storage snapshot.

    See the new SNAPSHOT TIME clause of the ALTER DATABASE full_database_recovery clause.

  • Move an online data file to a new location while the database is open and accessing the data file.

    See the new move_datafile_clause of ALTER DATABASE.

  • Create a control file for a Data Guard far sync instance.

    See the enhanced controlfile_clauses of ALTER DATABASE.

  • Performing switchovers and failovers to a physical standby database is simplified.

    See the new ALTER DATABASE clauses switchover_clause and failover_clause.

  • Real-time apply is now enabled by default during Redo Apply on a physical standby database. You can disable real-time apply by specifying USING ARCHIVED LOGFILE.

    See the enhanced managed_standby_recovery clause of ALTER DATABASE.

ALTER SYSTEM Enhancements

The following features provide enhancements to the ALTER SYSTEM statement:

  • Relocate a client to the least loaded Oracle ASM instance.

    See the new RELOCATE CLIENT clause of ALTER SYSTEM.

  • Apply one-off patches released for Oracle ASM in a rolling manner.

    See the new rolling_patch_clauses of ALTER SYSTEM.

AUDIT and NOAUDIT (Traditional Auditing) Enhancements

The following feature provides enhancements to the AUDIT and NOAUDIT statements for traditional auditing:

CREATE DISKGROUP and ALTER DISKGROUP Enhancements

The following features provide enhancements to the CREATE DISKGROUP statement, ALTER DISKGROUP statement, or both:

  • Check logical data corruptions and repair them automatically in normal and high redundancy Oracle ASM disks groups.

    See the new scrub_clause of ALTER DISKGROUP.

  • Replace a user in an Oracle ASM disk group.

    See the enhanced user_clauses of ALTER DISKGROUP.

  • Change the permissions, owner, and user group of an Oracle ASM disk group file while it is open.

    See the enhanced ALTER DISKGROUP clauses file_permissions_clause and the file_owner_clause.

  • Replace one or more disks in an Oracle ASM disk group with a single operation.

    See the new replace_disk_clause of ALTER DISKGROUP.

  • Rename a disk in an Oracle ASM disk group.

    See the new rename_disk_clause of ALTER DISKGROUP.

  • The following are new Oracle ASM disk group attributes:

    • CONTENT.CHECK allows you to enable or disable content checking when performing data copy operations for rebalancing a disk group.

    • FAILGROUP_REPAIR_TIME allows you to specify a default repair time for the failure groups in the disk group.

    • PHYS_META_REPLICATED allows you to track the replication status of a disk group.

    • THIN_PROVISIONED allows you to enable or disable the functionality to discard unused storage space after a disk group rebalance is completed.

    See Table 13-2.

CREATE FLASHBACK ARCHIVE and ALTER FLASHBACK ARCHIVE Enhancements

The following feature provides enhancements to the CREATE FLASHBACK ARCHIVE and ALTER FLASHBACK ARCHIVE statements:

  • Instruct the database to optimize the storage of data in history tables.

    See the new clause [NO] OPTIMIZE DATA of CREATE FLASHBACK ARCHIVE and the new clause [NO] OPTIMIZE DATA of ALTER FLASHBACK ARCHVE.

CREATE INDEX and ALTER INDEX Enhancements

The following features provide enhancements to the CREATE INDEX statement, ALTER INDEX statement, or both:

  • Create partial indexes on a subset of the partitions of a table.

    See the new partial_index_clause of CREATE INDEX.

  • Remove orphaned index entries for records that were previously dropped or truncated by a table partition maintenance operation.

    See the new keyword CLEANUP of ALTER INDEX ... COALESCE and the new keyword CLEANUP of ALTER INDEX ... MODIFY PARTITION ... COALESCE.

  • Create multiple indexes on the same set of columns, column expressions, or both if the indexes are of different types, use different partitioning, or have different uniqueness properties.

    See the index_expr clause of CREATE INDEX.

CREATE INDEXTYPE and ALTER INDEXTYPE Enhancements

The following feature provides enhancements to the CREATE INDEXTYPE and ALTER INDEXTYPE statements:

CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW Enhancements

The following feature provides enhancements to the CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW statements:

CREATE MATERIALIZED VIEW LOG and ALTER MATERIALIZED VIEW LOG Enhancements

The following feature provides enhancements to the CREATE MATERIALIZED VIEW LOG and ALTER MATERIALIZED VIEW LOG statements:

  • Specify the refresh method for which a materialized view log will be used. You can specify synchronous refresh, which is introduced in Oracle Database 12c, or fast refresh, which is also available in earlier releases.

    See:

CREATE SEQUENCE and ALTER SEQUENCE Enhancements

The following features provide enhancements to the CREATE SEQUENCE and ALTER SEQUENCE statements:

  • Control whether the sequence pseudocolumn NEXTVAL retains its original value during replay for Application Continuity.

    See:

  • Create a session sequence, which is a special type of sequence that is specifically designed to be used with global temporary tables that have session visibility.

    See:

CREATE TABLE and ALTER TABLE Enhancements

The following features provide enhancements to the CREATE TABLE statement, ALTER TABLE statement, or both:

  • The maximum size for the VARCHAR2, NVARCHAR2, and RAW data types is increased to 32767 bytes.

    See "Extended Data Types".

  • Temporal Validity support enables you to associate a valid time dimension with a table. You can use Oracle Flashback Query to retrieve rows from that table based on whether they are considered valid as of a specified time or during a specified time period.

    See:

  • Virtual columns, which are noneditioned objects, can depend on editioned objects.

    See:

  • Performance has been improved when you specify a DEFAULT value for a nullable column.

    See the DEFAULT clause of ALTER TABLE.

  • Specify a default column value that includes the sequence pseudocolumns CURRVAL and NEXTVAL.

    See:

    • The DEFAULT clause of CREATE TABLE

    • The DEFAULT clause of ALTER TABLE

  • The DEFAULT clause has the new clause ON NULL, which instructs the database to assign a specified default column value when an INSERT statement attempts to assign a value that evaluates to NULL.

    See:

    • The ON NULL clause of CREATE TABLE

    • The ON NULL clause of ALTER TABLE

  • Specify an identity column, which is assigned an increasing or decreasing integer value from a sequence generator.

    See:

  • Hide and unhide columns in tables.

    See:

  • Recursively cascade a truncate operation to child tables.

    See the new keyword CASCADE of the clause truncate_partition_subpart of ALTER TABLE.

  • Recursively cascade an exchange operation to child tables.

    See the new keyword CASCADE of the clause exchange_partition_subpart of ALTER TABLE.

  • Store XMLType data, and abstract data types that contain attributes of type XMLType, CLOB, BLOB, or NCLOB, in an ANYDATA column.

    See the new clause modify_opaque_type of ALTER TABLE.

  • Enable a table for row archival for In-Database Archiving.

    See the new ROW ARCHIVAL clause of CREATE TABLE.

  • Manage policies for Automatic Data Optimization.

    See the new ilm_clause of CREATE TABLE and the new ilm_clause of ALTER TABLE.

  • Create a reference-partitioned child table whose parent is an interval-partitioned table.

    See the enhanced clause reference_partitioning of CREATE TABLE.

  • Specify multiple table partitions or table subpartitions for the following ALTER TABLE operations:

  • In earlier releases, the following DDL operations required a DML-blocking lock. You can use the new ONLINE keyword to allow the execution of DML statements during the following DDL operations:

    • Dropping an index (using DROP INDEX ... ONLINE ...)

    • Marking an index as UNUSABLE (using ALTER INDEX ... UNUSABLE ONLINE)

    • Marking a column as UNUSED (using ALTER TABLE ... SET UNUSED ... ONLINE ...)

    • Dropping a constraint (using ALTER TABLE ... DROP ... ONLINE ...)

    • Moving a table partition (using ALTER TABLE ... MOVE PARTITION ... ONLINE)

    • Moving a table subpartition (using ALTER TABLE ... MOVE SUBPARTITION ... ONLINE)

CREATE VIEW Enhancements

The following features provide enhancements to the CREATE VIEW statement:

  • Hide and unhide columns in views.

    See the new clause VISIBLE | INVISIBLE of CREATE VIEW.

  • Specify whether functions referenced in the view are executed using the view invoker's rights or the view definer's rights.

    See the new clause BEQUEATH of CREATE VIEW.

GRANT and REVOKE Enhancements

The following features provide enhancements to the GRANT and REVOKE statements:

SELECT Enhancements

The following features provide enhancements to the SELECT statement:

  • Pattern matching enables you to recognize patterns found across multiple rows in a table.

    See the new row_pattern_clause of SELECT.

  • Perform top-N queries by specifying an offset, and the number of rows or percentage of rows to return.

    See the new row_limiting_clause of SELECT.

  • In a query that performs outer joins of more than two pairs of tables, a single table can now be the null-generated table for multiple tables.

    See "Outer Joins".

  • Perform a variation of an ANSI CROSS JOIN or an ANSI LEFT OUTER JOIN with left correlation support. You can specify a table reference or collection expression on the right side of the join clause.

    See the new cross_outer_apply_clause of SELECT.

  • Specify a lateral inline view in a query expression.

    See the new keyword LATERAL of SELECT.

  • Declare and define PL/SQL functions and procedures in the WITH clause of a query. You can then reference the PL/SQL functions in the query and its subqueries.

    See the new clause plsql_declarations on SELECT.

TRUNCATE TABLE Enhancements

The following feature provides enhancements to the TRUNCATE TABLE statement:

  • Recursively truncate child tables.

    See the new clause CASCADE of TRUNCATE TABLE.

New or Enhanced Functions

The following are new or enhanced functions:

  • CLUSTER_DETAILS is a new function that predicts cluster membership for each row. It can use a pre-defined clustering model or perform dynamic clustering. The function returns an XML string that describes the predicted cluster or a specified cluster.

  • CLUSTER_DISTANCE is a new function that predicts cluster membership for each row. It can use a pre-defined clustering model or perform dynamic clustering. The function returns the raw distance between each row and the centroid of either the predicted cluster or a specified.

  • CLUSTER_ID has been enhanced so that it can either use a pre-defined clustering model or perform dynamic clustering.

  • CLUSTER_PROBABILITY has been enhanced so that it can either use a pre-defined clustering model or perform dynamic clustering. The data type of the return value has been changed from NUMBER to BINARY_DOUBLE.

  • CLUSTER_SET has been enhanced so that it can either use a pre-defined clustering model or perform dynamic clustering. The data type of the returned probability has been changed from NUMBER to BINARY_DOUBLE

  • FEATURE_DETAILS is a new function that predicts feature matches for each row. It can use a pre-defined feature extraction model or perform dynamic feature extraction. The function returns an XML string that describes the predicted feature or a specified feature.

  • FEATURE_ID has been enhanced so that it can either use a pre-defined feature extraction model or perform dynamic feature extraction.

  • FEATURE_SET has been enhanced so that it can either use a pre-defined feature extraction model or perform dynamic feature extraction. The data type of the returned probability has been changed from NUMBER to BINARY_DOUBLE.

  • FEATURE_VALUE has been enhanced so that it can either use a pre-defined feature extraction model or perform dynamic feature extraction. The data type of the return value has been changed from NUMBER to BINARY_DOUBLE.

  • ORA_INVOKING_USER is a new function that returns the name of the database user who invoked the current statement or view. This function takes into account the BEQUEATH property of intervening views referenced in the statement.

  • ORA_INVOKING_USERID is a new function that returns the identifier of the database user who invoked the current statement or view. This function takes into account the BEQUEATH property of intervening views referenced in the statement.

  • PREDICTION has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction.

  • PREDICTION_BOUNDS now returns the upper and lower bounds of the prediction as the BINARY_DOUBLE data type. It previously returned these values as the NUMBER data type.

  • PREDICTION_COST has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction. The data type of the returned cost has been changed from NUMBER to BINARY_DOUBLE.

  • PREDICTION_DETAILS has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction.

  • PREDICTION_PROBABILITY has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction. The data type of the returned probability has been changed from NUMBER to BINARY_DOUBLE.

  • PREDICTION_SET has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction. The data type of the returned probability has been changed from NUMBER to BINARY_DOUBLE.

  • STANDARD_HASH is a new function that computes a hash value for a given expression using one of several standardized hash algorithms.

  • SYS_CONTEXT enables you to query a new built-in namespace, SYS_SESSION_ROLES, which allows you to determine if a specified role is currently enabled for the session.

New or Enhanced Privileges

The following are new or enhanced system privileges and object privileges:

  • The behavior has changed for the following system privilege, which is listed in Table 18-1:

    • SELECT ANY DICTIONARY now does not allow you to query the following objects in the SYS schema: DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS.

  • The following new system privileges are listed in Table 18-1:

    • CREATE SQL TRANSLATION PROFILE, CREATE ANY SQL TRANSLATION PROFILE, ALTER ANY SQL TRANSLATION PROFILE, USE ANY SQL TRANSLATION PROFILE, and DROP ANY SQL TRANSLATION PROFILE allow you to manage SQL translation profiles.

    • EXEMPT REDACTION POLICY allows you to bypass any existing Oracle Data Redaction policies.

    • INHERIT ANY PRIVILEGES allows you to execute invoker's rights procedures with the privileges of the invoker.

    • KEEP DATE TIME allows the SYSDATE and SYSTIMESTAMP functions to return their original values during replay for Application Continuity.

    • KEEP SYSGUID allows the SYS_GUID function to return its original value during replay for Application Continuity.

    • LOGMINING allows you to perform LogMiner operations in a multitenant container database (CDB).

    • PURGE DBA_RECYCLEBIN allows you to remove all objects from the system-wide recycle bin.

    • SYSBACKUP allows you to perform backup and recovery tasks.

    • SYSDG allows you to manage Oracle Data Guard.

    • SYSKM allows you to perform encryption key management.

    • TRANSLATE ANY SQL allows you to translate SQL for any user.

  • The following new object privileges are listed in Table 18-2:

    • The ALTER and USE privileges authorize operations on SQL translation profiles.

    • INHERIT PRIVILEGES is a new type of object privilege that can be granted on a user to users and roles. It allows invoker's rights procedures owned by the grantee to be executed with the privileges of the invoker when the invoker is the user on whom this privilege is granted.

    • KEEP SEQUENCE allows the sequence pseudocolumn NEXTVAL to retain its original value during replay for Application Continuity.

    • TRANSLATE SQL is a new type of object privilege that can be granted on a user to users and roles. It allows the grantee to translate SQL through the grantee's SQL translation profile for the user on whom this privilege is granted.

New Hints

The following are new hints:

Deprecated Features

The following features are deprecated in this release, and may be desupported in a future release:

  • Stored outlines are deprecated. They are still supported for backward compatibility. However, Oracle recommends that you use SQL plan management instead. SQL plan management creates SQL plan baselines, which offer superior SQL performance stability compared with stored outlines.

    See Oracle Database SQL Tuning Guide for more information about SQL plan management.

  • The use of PKI encryption with Transparent Data Encryption is deprecated. To configure Transparent Data Encryption, use the ADMINISTER KEY MANAGEMENT statement.

    See Oracle Database Advanced Security Guide for more information.

Desupported Features

Some features previously described in this document are desupported in Oracle Database 12c Release 1 (12.1). See Oracle Database Upgrade Guide for a list of desupported features.