|Oracle® Database SQL Language Reference
11g Release 1 (11.1)
|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.
A number of SQL statement 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 Automatic Storage Management 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 Automatic Storage Management 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.
New syntax lets you modify an
XMLType table to add or remove one or more XMLSchemas.
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 tempfile.
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 SecureFile 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 SecureFile storage.
The LOB_parameters now include
DECRYPT clauses to enable and disable encryption of LOB columns for LOBs using SecureFile storage.
DROP DISKGROUP has a new
FORCE keyword that lets you drop a disk group that can no longer be mounted by an Automatic Storage Management 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 ORDDicom