Changes in This Release for Oracle Database SQL Language Reference

This preface contains:

• Changes in Oracle Database 12c Release 2 (12.2.0.1)

• Changes in Oracle Database 12c Release 1 (12.1.0.2)

• Changes in Oracle Database 12c Release 1 (12.1.0.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:

• Analytic views are metadata objects that provide a fast and efficient way to create and store analytic queries of data in existing database tables and views. With analytic views you can easily create complex analytic queries on large amounts of hierarchical and dimensional data. Attribute dimensions, hierarchies, and analytic views are new database schema objects.

  See the following new statements:

  • CREATE ANALYTIC VIEW

  • CREATE ATTRIBUTE DIMENSION

  • CREATE HIERARCHY

  • ALTER ANALYTIC VIEW

  • ALTER ATTRIBUTE DIMENSION

  • ALTER HIERARCHY

  • DROP ANALYTIC VIEW

  • DROP ATTRIBUTE DIMENSION

  • DROP HIERARCHY

• Join groups allow you to optimize join queries for table columns that are populated in the In-Memory Column Store.

  See the following new statements:

  • CREATE INMEMORY JOIN GROUP

  • ALTER INMEMORY JOIN GROUP

  • DROP INMEMORY JOIN GROUP

• Oracle Sharding supports distribution and replication of data across a pool of discrete Oracle databases that share no hardware or software.

  See the following new statements:

  • CREATE TABLESPACE SET

  • ALTER TABLESPACE SET

  • DROP TABLESPACE SET

  See the new clauses SHARDED and DUPLICATED of CREATE TABLE.

• You can use PDB lockdown profiles in a multitenant container database (CDB) to restrict user operations in PDBs.

  See the following new statements:

  • CREATE LOCKDOWN PROFILE

  • ALTER LOCKDOWN PROFILE

  • DROP LOCKDOWN PROFILE

ADMINISTER KEY MANAGEMENT Enhancements

• You can instruct the database to force a keystore open for certain operations.

  See Notes on the FORCE KEYSTORE Clause.

• You can use keystore passwords stored in an external store.

  See the new clause EXTERNAL STORE in Notes on Specifying Keystore Passwords.

• You can specify an encryption algorithm for a master key.

  See the new clause USING ALGORITHM of set_key and create_key.

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:

  • The new FLEX keyword in the REDUNDANCY Clause of CREATE DISKGROUP.

  • The new quotagroup_clauses of ALTER DISKGROUP.

  • The new filegroup_clauses of ALTER DISKGROUP.

• 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

• You can create restore points for a PDB.

  See the new clauses CLEAN and FOR PLUGGABLE DATABASE of CREATE RESTORE POINT.

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 calculated measure expression defines a calculated measure in an analytic view.

  See Calculated Measure 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:

  • CAST - In addition, this function now lets you to specify a format model and NLS parameters to be applied for the conversion.

  • TO_BINARY_DOUBLE

  • TO_BINARY_FLOAT

  • TO_DATE

  • TO_DSINTERVAL

  • TO_NUMBER

  • TO_TIMESTAMP

  • TO_TIMESTAMP_TZ

  • TO_YMINTERVAL

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

  • VALIDATE_CONVERSION

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

  • TO_BLOB (bfile) converts a BFILE value to a BLOB value.

  • TO_CHAR (bfile|blob) converts BFILE or BLOB data to the database character set.

  • TO_CLOB (bfile|blob) converts BFILE or BLOB data to the database character set and returns the data as a CLOB value.

• 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:

  • FEATURE_COMPARE uses a Feature Extraction model to compare two different documents.

  • ORA_DM_PARTITION_NAME returns the name of the partition associated with the input row.

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

  • CLUSTER_DETAILS

  • CLUSTER_DISTANCE

  • CLUSTER_ID

  • CLUSTER_PROBABILITY

  • CLUSTER_SET

  • FEATURE_COMPARE

  • FEATURE_DETAILS

  • FEATURE_ID

  • FEATURE_SET

  • FEATURE_VALUE

  • ORA_DM_PARTITION_NAME

  • PREDICTION

  • PREDICTION_BOUNDS

  • PREDICTION_COST

  • PREDICTION_DETAILS

  • PREDICTION_PROBABILITY

  • PREDICTION_SET

• 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:

  • JSON_QUERY

  • JSON_VALUE

• 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:

  • INMEMORY Hint

  • NO_INMEMORY Hint

  • INMEMORY_PRUNING Hint

  • NO_INMEMORY_PRUNING Hint

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

  See the following conditions:

  • IS JSON Condition

  • JSON_EXISTS Condition

  • JSON_TEXTCONTAINS Condition

  See the following functions:

  • JSON_QUERY

  • JSON_TABLE

  • JSON_VALUE

  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:

  • CLUSTERING Hint

  • NO_CLUSTERING Hint

• 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:

  • CREATE PLUGGABLE DATABASE

  • ALTER PLUGGABLE DATABASE

  • DROP PLUGGABLE DATABASE

• 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:

  • CREATE AUDIT POLICY (Unified Auditing)

  • ALTER AUDIT POLICY (Unified Auditing)

  • DROP AUDIT POLICY (Unified Auditing)

  • AUDIT (Unified Auditing)

  • NOAUDIT (Unified Auditing)

• 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:

• Audit operations on a SQL translation profile.

  See the new clause ON SQL TRANSLATION PROFILE of AUDIT.

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 domain indexes on hash- and interval-partitioned tables.

  See CREATE INDEXTYPE and ALTER INDEXTYPE.

CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW Enhancements

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

• Materialized views, which are noneditioned objects, can depend on editioned objects.

  See:

  • The new clauses evaluation_edition_clause and unusable_editions_clause of CREATE MATERIALIZED VIEW

  • The new clauses evaluation_edition_clause and unusable_editions_clause of ALTER MATERIALIZEED VIEW

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:

  • The new for_refresh_clause of CREATE MATERIALIZED VIEW LOG

  • The new for_refresh_clause of ALTER MATERIALIZED VIEW LOG

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:

  • The new clauses KEEP and NOKEEP of CREATE SEQUENCE

  • The new clauses KEEP and NOKEEP of ALTER SEQUENCE

• 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:

  • The new clauses SESSION and GLOBAL of CREATE SEQUENCE

  • The new clauses SESSION and GLOBAL of ALTER SEQUENCE

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:

  • The new CREATE TABLE clause period_definition

  • The new ALTER TABLE clauses add_period_clause and drop_period_clause

  • The enhanced SELECT flashback_query_clause

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

  See:

  • The new clauses evaluation_edition_clause and unusable_editions_clause of CREATE TABLE

  • The new clause modify_virtcol_properties of ALTER TABLE

• 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:

  • The new clauses identity_clause of CREATE TABLE and identity_options of CREATE TABLE

  • The new clauses identity_clause of ALTER TABLE and identity_options of ALTER TABLE

• Hide and unhide columns in tables.

  See:

  • The new clauses VISIBLE | INVISIBLE of CREATE TABLE

  • The new clauses VISIBLE | INVISIBLE of CREATE TABLE for virtual columns

  • The new clause modify_col_visibility of ALTER TABLE

• 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:

  • Add one or more range, list, or system partitions to a table. See add_table_partition.

  • Add one or more range subpartitions to a partition. See add_range_subpartition.

  • Add one or more list subpartitions to a partition. See add_list_subpartition.

  • Split one range or list partition into two or more partitions. See split_table_partition.

  • Split one range or list subpartition into two or more subpartitions. See split_table_subpartition.

  • Merge two or more range, list, or system partitions into one new partition. See merge_table_partitions.

  • Merge two or more range or list subpartitions into one new subpartition. See merge_table_subpartitions.

  • Truncate one or more partitions or subpartitions. See truncate_partition_subpart.

  • Drop one or more partitions. See drop_table_partition.

  • Drop one or more subpartitions. See drop_table_subpartition.

• 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:

• Grant object privileges on a user to users and roles.

  See:

  • The new clause ON USER of GRANT

  • The new clause ON USER of REVOKE

• Grant object privileges on a SQL translation profile to users and roles.

  See:

  • The new clause ON SQL TRANSLATION PROFILE of GRANT

  • The new clause ON SQL TRANSLATION PROFILE of REVOKE

• Grant code based access control (CBAC) roles to program units.

  See:

  • The new clause grant_roles_to_programs of GRANT

  • The new clause revoke_roles_from_programs of REVOKE

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:

• The GATHER_OPTIMIZER_STATISTICS Hint and NO_GATHER_OPTIMIZER_STATISTICS Hint allow you to enable and disable statistics gathering during bulk loads.

• The PQ_CONCURRENT_UNION Hint and NO_PQ_CONCURRENT_UNION Hint allow you to enable and disable concurrent processing of UNION and UNION ALL operations.

• The PQ_FILTER Hint allows you to instruct the optimizer on how to process rows when filtering correlated subqueries.

• The PQ_SKEW Hint and NO_PQ_SKEW Hint allow you to advise the optimizer of whether the distribution of the values of the join keys for a parallel join is skewed.

• The USE_CUBE Hint and NO_USE_CUBE Hint allow you to specify whether to use or exclude cube joins.

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.