What's New in the SQL Reference?

This section describes new features of Oracle Database 10g and provides pointers to additional information.

For information on features that were new in earlier versions of Oracle Database, please refer to the documentation for the earlier release.

Oracle Database 10g Release 2 New Features in the SQL Reference

The following top-level SQL statements are new or enhanced in this release:

• ALTER DATABASE has been enhanced as follows:

  • New syntax in the standby_database_clauses lets you bring a logical standby database to the same state as the primary database.

  • Additional new syntax in the standby_database_clauses lets you convert a primary database outside the Data Guard environment into a physical standby database.

  • New syntax in the managed_standby_recovery clause lets you create a logical standby database from the physical standby database.

  • New syntax in the database_file_clauses lets you rename tempfiles as well as datafiles and redo log files.

• ALTER DISKGROUP has new syntax that lets you specify when in the course of a diskgroup rebalance operation control should be returned to the user.

• ALTER SYSTEM has new syntax that lets you load information from the server wallet into memory for database access, and to generate a new transparent database encryption master key:

• ALTER TABLESPACE contains new syntax that lets you drop an empty datafile or tempfile from the data dictionary and remove it from the operating system.

• ALTER USER contains new syntax that lets you expose a user to proxy use by enterprise users.

• COMMIT contains a new WRITE clause that lets you specify the priority with which the redo information generated by the commit operation is written to the redo log.

• CREATE DATABASE LINK has new syntax that helps Data Pump provide an encoded password for the database link during import of data.

• CREATE DIMENSION and ALTER DIMENSION contain new syntax that lets you preserve the hierarchical chain of parent-child relationship by an alternative path that skips over a specified level if it is null.

• CREATE RESTORE POINT is a new SQL statement that lets you create a restore point, to which you can flash back a table or the database.

• CREATE TABLE documents the new limit on number of partitions and subpartitions as 1024K - 1.

• CREATE TABLE and ALTER TABLE contains new syntax that lets you encrypt column data.

• CREATE USER and ALTER USER contain new syntax for determining how global and external users are identified.

• DROP RESTORE POINT is a new SQL statement that lets you drop a restore point.

• FLASHBACK DATABASE has new syntax that lets you flash back the database to a restore point.

• FLASHBACK TABLE has new syntax that lets you flash back a table to a restore point.

The following clauses are modified in this release:

• All of the DML statements (INSERT, UPDATE, DELETE, MERGE) now have an error logging clause. See for example INSERT.

• "Model Expressions" have been enhanced to allow analytic functions and FOR loops.

The following built-in data mining functions are new in this release:

• CLUSTER_ID

• CLUSTER_PROBABILITY

• CLUSTER_SET

• FEATURE_ID

• FEATURE_SET

• FEATURE_VALUE

• PREDICTION

• PREDICTION_COST

• PREDICTION_DETAILS

• PREDICTION_PROBABILITY

• PREDICTION_SET

The following built-in XML functions are new in this release:

• APPENDCHILDXML

• DELETEXML

• INSERTCHILDXML

• INSERTXMLBEFORE

• XMLCDATA

• XMLCOMMENT

• XMLPI

• XMLROOT

• XMLPARSE

• XMLPI

• XMLQUERY

• XMLSERIALIZE

• XMLTABLE

The following datatypes are new in this release:

• "SDO_TOPO_GEOMETRY"

The following pseudocolumns are new in this release:

• COLUMN_VALUE Pseudocolumn

The following miscellaneous changes have been made:

• Appendix C, "Oracle Regular Expression Support" lists the Perl-influenced operators that are now supported in Oracle regular expression functions and conditions.

• Two new hints are provided to handle parallel join bitmap filtering: "PX_JOIN_FILTER Hint" and "NO_PX_JOIN_FILTER Hint".

• The new CHANGE NOTIFICATION system privilege is documented in GRANT.

Oracle Database 10g Release 1 New Features in the SQL Reference

The following datatypes are new in this release:

• The binary floating-point datatypes BINARY_FLOAT and BINARY_DOUBLE

• The spatial datatype SDO_GEORASTER

• The interMedia datatype SI_StillImage and six related Still Image object types

The following top-level SQL statements are new or enhanced in this release:

• A number of new top-level SQL statements have been added to support Automatic Storage Management:

  • CREATE DISKGROUP

  • ALTER DISKGROUP

  • DROP DISKGROUP

  In addition, the following statements have added syntax in support of Automatic Storage Management:

  • file_specification subclauses, datafile_tempfile_spec and redo_log_file_spec, let you specify Automatic Storage Management files in the form of ASM_filename , as well as file system files

  • CREATE CONTROLFILE lets you specify Automatic Storage Management files as well as file system files

  • CREATE TABLESPACE lets you create a tablespace within an Automatic Storage Management disk group using the "DATAFILE | TEMPFILE Clause"

• CREATE DATABASE has new syntax that lets you create a default permanent tablespace for the database.

• ALTER DATABASE has new syntax that lets you:

  • Specify multiple temporary tablespaces (a tablespace group) as the database default temporary tablespaces

  • Assign or reassign a tablespace as the database default permanent tablespace (using the DEFAULT TABLESPACE clause)

  • Reset the target recovery incarnation for the database from the current incarnation to the prior incarnation

  • Begin backup of all the datafiles in the database

  • Enable block change tracking for incremental backups of the database

  • Update both global and local partitioned indexes as part of table partition maintenance operations

  • Revert the entire database, or some tablespaces of the database, to an earlier version

  • Control the relationship between primary databases and logical and physical standby databases

  • Assign or reassign a tablespace as the default permanent tablespace for the database

  • Add a logfile or enable a redo log thread by specifying an instance name rather than a thread number

• ALTER MATERIALIZED VIEW LOG:

  • Has a new FORCE clause that lets you specify the addition of attributes that the materialized view log already has without causing Oracle to return an error

  • Lets you instruct Oracle Database to record a sequence value in the materialized view log

• ALTER SYSTEM has new syntax that lets you flush the buffer cache of the system global area (SGA).

• ALTER TABLE has new syntax that lets you manually compact the table segment, adjust the high water mark, and free the recuperated space.

• ALTER TYPE has new syntax that lets you modify varrays and nested tables of scalar types.

• ALTER TABLESPACE has new syntax that lets you:

  • Rename the tablespace

  • Guarantee that unexpired undo data will be preserved, even at the expense of ongoing transactions that require undo segment space

• CREATE DATABASE has new syntax that lets you:

  • Specify datafiles for the new SYSAUX system tablespace

  • Specify a bigfile tablespace as the default for the database and override the default for undo and default temporary tablespaces as well. A bigfile tablespace contains a single datafile that can be up to 4GB in size.

  • Create a default permanent tablespace for the database.

• CREATE DIMENSION and ALTER DIMENSION have new syntax that lets you assign a name to a dimension attribute that is different from the level name.

• CREATE INDEX and ALTER INDEX have new syntax that lets you create and maintain global hash-partitioned indexes.

• CREATE INDEXTYPE and ALTER INDEXTYPE have new syntax that supports array inserts using the ODCIIndexInsert method.

• CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW have new syntax that enhances refresh operations.

• CREATE OPERATOR and ALTER OPERATOR have new syntax that lets you pass column information to the functional implementation of the operator.

• CREATE TABLESPACE has new syntax that lets you create a bigfile tablespace. Such a tablespace contains a single datafile that can contain up to 2³² or 4G blocks, resulting in a datafile of up to 128 terabytes (TB). CREATE DATABASE has related syntax that lets you specify a bigfile tablespace as the default, undo, and default temporary tablespace for the database.

• CREATE TABLESPACE and ALTER TABLESPACE have new syntax that lets you assign or reassign a temporary tablespace to a tablespace group.

• CREATE USER and ALTER USER have new syntax that lets you specify multiple temporary tablespaces (a tablespace group) to a user.

• DROP TABLE has a new PURGE clause that lets you drop the table without moving it to the recycle bin.

• FLASHBACK DATABASE is a new statement that lets you revert the entire database to an earlier version.

• FLASHBACK TABLE is a new statement that lets you revert one or more tables to an earlier system change number (SCN) or timestamp or retrieve a table that was dropped.

• MERGE has new syntax that lets you:

  • Specify either the update operation or the insert operation, or both

  • Delete rows from the target table during the update operation

• PURGE is a new SQL statement that lets you permanently remove previously dropped objects from the recycle bin and release the space that was associated with them.

• SELECT has new syntax that lets you:

  • Issue a versions query, which returns all incarnations of the rows returned by the query within a specified SCN or time range.

  • Perform a query on a partitioned outer join. The new syntax supports data densification, the process of querying sparse data along a particular dimension of data and returning rows that otherwise would have been omitted from the data returned by the query.

  • View the results of a query as a multidimensional array and perform associated calculations.

The following clauses are modified in this release:

• In the physical_attributes_clause, the MAXTRANS parameter has been deprecated.

• The name of the data_segment_compression clause has been changed to table_compression for semantic clarity. The functionality has not changed. This clause appears in a number of SQL statements. For example, see CREATE TABLE table_compression.

The following built-in functions are new in this release:

• A new aggregate function COLLECT.

• A new category of collection functions lets you manipulate nested tables and varrays. The collection functions are:

  • CARDINALITY

  • POWERMULTISET

  • POWERMULTISET_BY_CARDINALITY

  • SET

• A new category of model functions are for use in specialized calculations and are valid only in the model_clause of a query. The model functions are:

  • CV

  • PRESENTNNV

  • PRESENTV

  • PREVIOUS

• Functions to manipulate binary floating-point numbers:

  • TO_BINARY_DOUBLE

  • TO_BINARY_FLOAT

  • NANVL

  • REMAINDER

• ORA_HASH

• The regular expression functions REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR. The Oracle Database implementation of regular expression support is discussed in Appendix C, "Oracle Regular Expression Support".

• A new set of aggregate functions to support statistical analysis of data:

  • Correlation functions CORR_*

  • MEDIAN

  • STATS_BINOMIAL_TEST

  • STATS_CROSSTAB

  • STATS_F_TEST

  • STATS_KS_TEST

  • STATS_MODE

  • STATS_MW_TEST

  • STATS_ONE_WAY_ANOVA

  • T-test functions STATS_T_TEST_*

  • STATS_WSR_TEST

The following SQL operators are new or enhanced in this release:

• Equality and inequality operators (= and <>) can be used to compare nested tables and varrays.

• The hierarchical operator: CONNECT_BY_ROOT

• The multiset operators: MULTISET EXCEPT, MULTISET INTERSECT, and MULTISET UNION

The following pseudocolumns are new in this release:

• The hierarchical pseudo columns: CONNECT_BY_ISLEAF Pseudocolumn and CONNECT_BY_ISCYCLE Pseudocolumn

• The "Version Query Pseudocolumns" let you extract information about the rows returned by a version query.

• The pseudocolumn ORA_ROWSCN Pseudocolumn lets you obtain the system change number of the most recent operation on a table.

The following conditions are new in this release:

• The [NOT] IN conditions, formerly referred to as "membership condition", are now documented as "IN conditions" to distinguish them from the new MEMBER conditions (see IN Condition)

• The "Floating-Point Conditions" (IS [NOT] NAN and IS [NOT] INFINITE)

• IS A SET Condition

• IS ANY Condition

• IS EMPTY Condition

• IS PRESENT Condition

• MEMBER Condition

• REGEXP_LIKE Condition

• SUBMULTISET Condition

The following miscellaneous features are added:

• New locale-independent format elements have been added to the tables in "Format Models".

• Oracle Database now performs implicit conversion between CLOB and NCLOB data.

• You can now specify a LOB column in the UPDATE OF clause when creating an update DML trigger.