|Oracle® Database SQL Language Reference
11g Release 2 (11.2)
|PDF · Mobi · ePub|
This section describes new features of Oracle Database 11g and provides pointers to additional information.
For information on features that were new in earlier versions of Oracle Database, refer to the documentation for the earlier release.
The following top-level SQL statements are enhanced in this release:
TABLE now support the clause deferred_segment_creation for partitions and subpartitions. This lets you postpone creation of a segment until the first row of data is inserted into the partition or subpartition.
TABLE has a new clause DROP ALL STORAGE that lets you deallocate all segments for a table.
TABLE has a new clause DROP ALL STORAGE that lets you deallocate all segments for a partition or subpartition.
A number of sections of this book that were made up primarily of PL/SQL were moved to Oracle Database PL/SQL Language Reference in Oracle Database 11g Release 1. Please refer to "Structural Changes in the SQL Language Reference" for details on this migration of material.
The following top-level SQL statements are new or enhanced in this release:
A new top-level SQL statement ALTER DATABASE LINK lets you update the fixed user password in a database link when the password of a connection or authentication user has changed.
DISKGROUP statement has the following changes:
A new disk_region_clause lets you determine the Intelligent Data Placement attribute of the disk group file.
New diskgroup_volume_clauses let you manipulate logical Oracle ASM Dynamic Volume Manager (Oracle ADVM) volumes corresponding to physical volume devices.
NOAUDIT contain a new clause ALL STATEMENTS that lets you enable and disable auditing of all top-level SQL statements executed. In
AUDIT only, a new clause IN SESSION CURRENT allows you to limit auditing to the current session.
DISKGROUP have the following changes:
A new clause QUORUM | REGULAR let you designate a disk or failure group as a quorum disk or failure group, which can contain the voting file for Cluster Synchronization Services (CSS).
SESSION contains a new clause SYNC WITH PRIMARY that lets you synchronize the physical standby database with the primary database. A new session parameter STANDBY_MAX_DATA_DELAY lets you specify a session-specific apply lag tolerance for queries to a physical standby database that is in real-time query mode.
LOG has a new clause COMMIT SCN that instructs the database to use commit SCN data instead of timestamps to refresh the materialized view, which improves the speed of the refresh.
LOG have a new clause mv_log_purge_clause that lets you specify the purge time for the materialized view log.
TABLE are enhanced in the following ways:
A new clause deferred_segment_creation lets you postpone creation of the table segment until the first row of data is inserted into the table. This clause is also applicable to materialized views.
The clause table_compression has new syntax and terminology. Use
OLTP to specify OLTP table compression. (In earlier releases, the syntax was
BASIC to specify basic table compression. (In earlier releases, the syntax was
OPERATIONS and this type of compression was called DSS table compression.)
A new RESULT_CACHE Clause lets you specify whether the results of queries that name the table are considered for result caching.
The nested_table_col_properties provides a
LOCAL keyword to equipartition a nested table with partitioned base table. This is the default behavior in this release. The default in earlier releases was not to equipartition the nested table with the partitioned base table. Now you must specify the
GLOBAL keyword to store an unpartitioned nested table with a partitioned base table.
VIEW has a new keyword EDITIONING that lets you create an editioning view.
The statement GRANT has a new
EXECUTE object privilege on directory objects. The
ORACLE_LOADER access driver for external tables references this privilege when deciding whether to execute a preprocessor program.
The following built-in functions are new or enhanced in this release:
For a specified measure, the function LISTAGG orders data within each group specified in an
BY clause and then concatenates the values of the measure column.
A new NTH_VALUE function returns the value of a measure in a specified row of a window of data.
The following miscellaneous features are new or enhanced in this release:
Hints, which were introduced in Oracle7, are now superseded by several Oracle tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer. See "Hints" for more information.
Beginning with Oracle Database 11g Release 2 (22.214.171.124), the
NO_PARALLEL_INDEX hints are statement-level hints and supersede the earlier object-level hints. See "Note on Parallel Hints".
A new APPEND_VALUES Hint lets you use direct-path
INSERT with the
When specifying a redo log file, you can use the new keyword BLOCKSIZE Clause to override the operating system-dependent sector size.
The LOB_compression_clause now has a new
LOW setting, which results in significantly higher decompression and compression speeds, at the cost of a slightly lower compression ratio.
The subquery_factoring_clause now supports recursive subquery factoring (recursive WITH), which lets you query hierarchical data. This feature is more powerful than
BY in that it provides depth-first search and breadth-first search, and supports multiple recursive branches. A new search_clause and cycle_clause let you specify an ordering for the rows and mark cycles in the recursion.
A number of SQL statements are constructed almost entirely of PL/SQL elements. Those statements continue to appear in this reference, but the bulk of their syntax and semantics has been moved to Oracle Database PL/SQL Language Reference. The following table contains links to both the abbreviated SQL syntax and semantics in this book and to the full syntax and semantics in Oracle Database PL/SQL Language Reference.
|Abbreviated SQL Section||Full Syntax and Semantics|
|CREATE FUNCTION||CREATE FUNCTION|
|CREATE PACKAGE||CREATE PACKAGE|
|CREATE PACKAGE BODY||CREATE PACKAGE BODY|
|CREATE PROCEDURE||CREATE PROCEDURE|
|CREATE TRIGGER||CREATE TRIGGER|
|CREATE TYPE||CREATE TYPE|
|CREATE TYPE BODY||CREATE TYPE BODY|
|ALTER FUNCTION||ALTER FUNCTION|
|ALTER PACKAGE||ALTER PACKAGE|
|ALTER PROCEDURE||ALTER PROCEDURE|
|ALTER TRIGGER||ALTER TRIGGER|
|ALTER TYPE||ALTER TYPE|
The following top-level SQL statements are new or enhanced in this release:
ALTER DATABASE has been enhanced as follows:
The clause managed_standby_recovery has been greatly simplified. A number of subclauses have been deprecated as the database now handles much of the recovery process automatically.
The supplemental_db_logging contains new syntax that lets you enable or disable supplemental logging of PL/SQL calls.
The standby_database_clauses have new syntax that lets you convert a physical standby database into a snapshot standby database or convert a snapshot standby database into a physical standby database.
The clause managed_standby_recovery has new
IDENTITY syntax that lets you use the rolling upgrade feature provided by a logical standby and also revert to the original configuration of a primary database and a physical standby.
ALTER DISKGROUP has been enhanced as follows:
The check_diskgroup_clause has simplified syntax for checking the consistency of disk groups, disks, and files in an Oracle ASM environment.
The clause diskgroup_availability offers new options when mounting a disk group.
ALTER INDEX has been enhanced as follows:
MIGRATE parameter lets you migrate a domain index from user-managed storage tables to system-managed storage tables.
INVISIBLE parameter lets you modify an index so that it is invisible to the optimizer.
The "PARAMETERS Clause" now lets you rebuild an XMLIndex index as well as a domain index.
ALTER SYSTEM has been enhanced as follows:
New syntax lets you kill a session on another instance in an Oracle Real Application Clusters (Oracle RAC) environment.
New rolling_migration_clauses let you prepare an Oracle ASM cluster for migration and return it to normal operation after all nodes have migrated to the same software version.
ALTER TABLE has been enhanced as follows:
The behavior of the add_column_clause when you specify a
DEFAULT value has been enhanced for improved performance.
The syntax for READ ONLY | READ WRITE lets you put a table into read-only mode, to prevent DDL or DML changes during table maintenance, and then back into read/write mode.
The clause add_table_partition has expanded syntax to let you add a system partition.
The flashback_archive_clause lets you enable or disable historical tracking for the table.
The add_column_clause now lets you add a virtual column to a table.
A new clause alter_interval_partitioning lets you convert a range-partitioned table to an interval_partitioned table.
A new dependent_tables_clause lets you instruct the database to cascade various partition maintenance operations on a table to reference-partitioned child tables.
ALTER TABLESPACE has new syntax that lets you shrink the space taken by a temporary tablespace or an individual temp file.
ASSOCIATE STATISTICS has syntax that lets you specify that the database should manage storage of statistics collected on a system-managed domain index.
AUDIT has new syntax that lets you audit various activities on data mining models.
CALL now permits positional, named, and mixed notation in the argument to the routine being called, if the routine takes any arguments.
COMMENT has a new
MODEL clause lets you provide descriptive comments for a data mining model.
The new statements CREATE FLASHBACK ARCHIVE, ALTER FLASHBACK ARCHIVE, and DROP FLASHBACK ARCHIVE let you create, modify, and drop flashback data archives, which in turn let you track historical changes to tables.
CREATE INDEX has been enhanced as follows:
CREATE INDEXTYPE and ALTER INDEXTYPE let you specify that domain indexes built on the subject indextypes can be range partitioned, and will have their storage tables and partition maintenance operations managed by the database.
CREATE PFILE has new syntax that lets you create a parameter file from current system-wide parameter settings.
CREATE RESTORE POINT has new syntax that lets you create a restore point for a specified datetime or SCN in the past, and to preserve a flashback database.
CREATE SPFILE has new syntax that lets you create a system parameter file from current system-wide parameter settings.
CREATE TABLE has been enhanced as follows:
The flashback_archive_clause lets you create the table with tracking of historical changes enabled
The clause system_partitioning lets you partition the table
A new virtual_column_definition lets you create a virtual column.
New syntax for XML storage lets you store XML data in binary XML format.
A new clause reference_partitioning lets you partition a table by reference to another partitioned table.
The LOB_parameters now include a
SECUREFILE parameter, which lets you specify a new storage for LOBs that is faster, more efficient, and allows for new features such as LOB compression, encryption, and deduplication.
A new LOB_compression_clause lets you enable or disable server-side LOB compression for LOBs using SecureFiles storage.
A new LOB_deduplicate_clause lets you coalesce duplicate data into a single shared repository, reducing storage consumption and simplifying storage management for LOBs using SecureFiles storage.
The LOB_parameters now include
DECRYPT clauses to enable and disable encryption of LOB columns for LOBs using SecureFiles storage.
DROP DISKGROUP has a new
FORCE keyword that lets you drop a disk group that can no longer be mounted by an Oracle ASM instance.
GRANT contains several new system and object privileges that enable the grantee to work with data mining models.
LOCK TABLE has new syntax that lets you specify the maximum number of seconds the statement should wait to obtain a DML lock on the table.
MERGE now supports operations on tables with domain indexes.
SELECT has new
PIVOT syntax that lets you rotate rows into columns. A new
UNPIVOT operation lets you query data to rotate columns into rows.
The following SQL built-in functions have been added or enhanced:
CUBE_TABLE is a new built-in function that extracts data from a cube or dimension and returns it in the two-dimensional format of a relational table.
INSERTXMLAFTER let you add one or more nodes of any kind immediately after a target node that is not an attribute node.
REGEXP_COUNT is a new built-in function that counts the number of occurrences of a specified regular expression pattern in a source string.
PREDICTION_BOUNDS is a new function that returns the lower and upper confidence bounds for a prediction.
XMLDIFF and XMLPATCH are two new functions that provide SQL interfaces to the corresponding XMLDiff and XMLPatch C APIs. They let you compare two XMLType documents and use the diff file to patch an XMLType document.
The following miscellaneous changes have been made:
In earlier releases, one form of expression in Chapter 6, "Expressions" was the variable expression. This form has been renamed to placeholder expression for consistency with other books in the documentation set. See "Placeholder Expressions".
In earlier releases, the
TRUNCATE statement was presented as a single statement with separate syntactic branches for
CLUSTER. That command has now been divided into TRUNCATE CLUSTER and TRUNCATE TABLE for consistency with other top-level SQL statements. No actual syntax or semantic changes have occurred.
"Function Expressions" now permit positional, named, and mixed notation in the argument to a user-defined function being used as an expression.
A new object type object type is supported with Oracle Multimedia. See Media Types