What's New in the SQL Language Reference?

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.

Oracle Database 11g Release 1 New Features in the SQL Language Reference

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 KEEP 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.

  • New clauses disk_offline_clause and disk_online_clause let you take a disk offline for repair and then bring it back online.

• ALTER INDEX has been enhanced as follows:

  • A new MIGRATE parameter lets you migrate a domain index from user-managed storage tables to system-managed storage tables.

  • A new 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_partitioningalter_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 MINING MODEL clause lets you provide descriptive comments for a data mining model.

• CREATE DISKGROUP and ALTER DISKGROUP have new syntax that lets you set various attributes of a disk group or a qualified disk group template.

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

  • A new local_domain_index_clause lets you create a locally partitioned domain index.

  • The index_attributes have been modified to let you create an index that is invisible to the optimizer.

  • A new XMLIndex_clause lets you create an XMLIndex index for XML data.

• 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 future, 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 BY SYSTEM

  • 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 ENCRYPT and DECRYPT clauses to enable and disable encryption of LOB columns for LOBs using SecureFile storage.

• CREATE TABLESPACE has new syntax which, along with a new ENCRYPT keyword in the storage_clause, lets you encrypt an entire tablespace.

• CREATE TRIGGER has the following enhancements:

  • A new compound_dml_trigger lets you create a compound trigger by specifying a multipart PL/SQL block.

  • A new "FOLLOWS Clause" lets you order multiple triggers.

  • A new clause ENABLE | DISABLE lets you create a trigger in enabled or disabled form.

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

• REGEXP_INSTR and REGEXP_SUBSTR now have an optional subexpr parameter that lets you target a particular substring of the regular expression being evaluated.

• 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, PREDICTION_COST, and PREDICTION_SET have been enhanced. New syntax let you specify that the stored cost matrix should be used only if it is available, or to specify a cost matrix inline.

• PREDICTION_BOUNDS is a new function that returns the lower and upper confidence bounds for a prediction.

• XMLCAST and XMLEXISTS are two new functions that let you cast XML data to SQL scalar datatypes and determine whether an XQuery expression returns a nonempty XQuery sequence, respectively.

• 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 TABLE and 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.

• Two new hints, "RESULT_CACHE Hint" and "NO_RESULT_CACHE Hint", let you override settings of the RESULT_CACHE_MODE initialization parameter.

• "Function Expressions" now permit positional, named, and mixed notation in the argument to a user-defined function being used as an expression.

• The index_partition_description syntax of ALTER TABLE and ALTER INDEX now lets you specify parameters for a partition of a domain index.

• A new object type object type is supported with Oracle Multimedia. See ORDDicom