This preface contains:
The following are changes in Oracle Database SQL Language Reference for Oracle Database 12c Release 1 (12.1.0.2).
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:
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.
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.
The following are changes in Oracle Database SQL Language Reference for Oracle Database 12c Release 1 (12.1.0.1).
The following features are new in this release:
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.
The following features provides 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.
The following features provides 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.
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.
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.
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.
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.
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.
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
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
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 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 ALTER SEQUENCE
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.
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 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:
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)
The following features provides 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.
The following features provide enhancements to the GRANT and REVOKE statements:
Grant object privileges on a user to users and roles.
See:
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
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.
The following feature provides enhancements to the TRUNCATE TABLE statement:
Recursively truncate child tables.
See the new clause CASCADE of TRUNCATE TABLE.
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.
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.
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.
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.
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.