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.