|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
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.
The following datatypes are new in this release:
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:
In addition, the following statements have added syntax in support of Automatic Storage Management:
CREATE CONTROLFILE lets you specify Automatic Storage Management files as well as file system files
CREATE DATABASE has new syntax that let 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
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
Instruct Oracle Database to compress archivelog files prior to transmission to a remote site or storage to disk
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 4
GB in size.
Create a default permanent tablespace for the database.
CREATE TABLESPACE has new syntax that lets you create a bigfile tablespace. Such a tablespace contains a single datafile that can contain up to 232 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.
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 interrow 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
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:
A new category of model functions are for use in interrow calculations and are valid only in the
model_clause of a query. The model functions are:
Functions to manipulate binary floating-point numbers:
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:
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 following pseudocolumns are new in this release:
The "Version Query Pseudocolumns " let you extract information about the rows returned by a version query.
The pseudocolumn ORA_ROWSCN lets you obtain the system change number of the most recent operation on a table.
The following conditions are new in this release:
IN conditions, formerly referred to as "membership condition", are now documented as "
IN conditions" to distinguish them from the new
MEMBER conditions (see IN )
The "Floating-Point Conditions " (
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
You can now specify a LOB column in the
OF clause when creating an update DML trigger.