What's New in Database Utilities?

This section describes new features of the Oracle9i database utilities and provides pointers to additional information. To help those who are upgrading to the current release, this section also describes features that were introduced in Oracle8i.

The information is divided into the following sections:

Oracle9i Utilities New Features for Release 9.2

The following sections describe new and enhanced features that were introduced in Oracle9i, release 2.

Export and Import Utilities

The following is a list of new and enhanced features for the Export and Import utilities:

New OBJECT_CONSISTENT parameter for Export that lets you export each object in its own read-only transaction, even if it is partitioned. See OBJECT_CONSISTENT .
New STREAMS_CONFIGURATION parameter for Import that allows you to import any general streams metadata that may be present in the export dump file. See STREAMS_CONFIGURATION .
New STREAMS_INSTANTIATION parameter for Import that allows you to import streams instantiation metadata that may be present in the export dump file. See STREAMS_INSTANTIATION .

SQL*Loader Utility

The following is a list of new and enhanced features for SQL*Loader:

A new date cache feature reduces the actual number of date conversions done when many duplicate date values are present in the input data. This can increase performance during direct path loads. See Specifying a Value for the Date Cache .
Overriding the default attribute-value constructor by creating one or more user-defined constructors. See Loading Column Objects with User-Defined Constructors .

External Tables

The following is a list of new and enhanced features for external tables:

A new date cache feature reduces the actual number of date conversions done when many duplicate date values are present in the input data. This can increase performance during direct path loads. See Performance Hints When Using External Tables .

DBNEWID Utility

DBNEWID is a new database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. See Chapter 14, "DBNEWID Utility" for more information.

Metadata API

The Metadata API provides a centralized, simple, and flexible means for performing the following tasks:

Extracting complete definitions of database objects (metadata) as either XML or creation DDL
Transforming the metadata through industry-standard XSLT (Extensible Stylesheet Language Transformation).
Generating SQL DDL to re-create the database objects

The Metadata API was available as of Oracle9i, release 9.0.1; however, it was documented in a different book. As of release 9.2, it is documented in this manual. See Chapter 15, "Using the Metadata API" for more information.

Oracle9i Utilities New Features for Release 9.0.1

The following sections describe new and enhanced features that were introduced for Oracle9i utilities.

Export and Import Utilities

The following is a list of new and enhanced features for the Export and Import utilities:

Enhanced export/import functions for precalculated optimizer statistics. For more information, see:
- STATISTICS for information about use of this parameter with the Export utility
- STATISTICS for information about use of this parameter with the Import utility
- Importing Statistics
Addition of new parameters, RESUMABLE, RESUMABLE_NAME, RESUMABLE_TIMEOUT, FLASHBACK_SCN, and FLASHBACK_TIME. For more information, see the descriptions of these parameters beginning in Export Parameters and in Import Parameters .
Export mode can be used to dump out all tables in a tablespace. See TABLESPACES .
Pattern matching of table names during export. See TABLES .
Pattern matching of table names during import. See TABLES .
Reduced character set translations on Import. See Character Set Conversion .

SQL*Loader Utility

The following is a list of new and enhanced features for SQL*Loader:

SQL*Loader enhancements that allow for correctly loading integer and zoned/packed decimal datatypes across platforms. SQL*Loader can now do the following:
- Load binary integer data created on a platform whose byte ordering is different than that of the target platform
- Load binary floating-point data created on a platform whose byte ordering is different than that of the target platform (if the floating-point format used by source and target systems is the same)
- Specify the size, in bytes, of a binary integer and load it regardless of the target platform's native integer size
- Specify that integer values are to be treated as signed or unsigned quantities
- Accept EBCDIC-based zoned or packed decimal data encoded in IBM format
For more information on these enhancements, see the following:
Support for loading XML columns. See Loading LOBs .
Support for loading object tables with a subtype. See Loading Object Tables with a Subtype .
Support for loading column objects with a derived subtype. See Loading Column Objects with a Derived Subtype .
SQL*Loader support for Unicode. This support includes the following:
- Use of the UTF16 character set in the SQL*Loader datafile
- Support of character-length semantics in the SQL*Loader control file
- Use of SQL*Loader to load data into columns of datatype NCHAR, NVARCHAR2, and NCLOB when the national character set is AL16UTF16
- Specifying byte order (big endian or little endian) for SQL*Loader datafile
  See Also:
  
  Handling Different Character Encoding Schemes
  
  SQL*Loader Datatypes
  
  Byte Ordering
SQL*Loader extensions for support of datetime and interval datatypes as specified in the ANSI SQL 92 standards document. This support includes the ability to:
- Load datetime and interval datatypes for both conventional and direct path modes of SQL*Loader
- Perform datetime and interval datatype conversions between SQL*Loader client and database server
- Load datetime and interval datatypes using the direct path API
For more information, see Datetime and Interval Datatypes .
New functionality that allows users to specify the UNSIGNED parameter for the binary integers, SMALLINT and INTEGER(n). For more information, see SMALLINT and INTEGER(n).
New functionality that allows a length specification to be applied to the INTEGER parameter; for example, INTEGER(n). See INTEGER(n).
New multithreaded loading functionality for direct path loads that, when possible, converts column arrays to stream buffers and performs stream buffer loading in parallel. For more information, see Optimizing Direct Path Loads on Multiple-CPU Systems .
New COLUMNARRAYROWS parameter that lets you specify a value for the number of column array rows in direct path loads. And a new STREAMSIZE parameter that lets you specify the size of direct path stream buffers. For more information, see Specifying the Number of Column Array Rows and Size of Stream Buffers .
Addition of RESUMABLE, RESUMABLE_NAME, and RESUMABLE_TIMEOUT parameters to enable and disable resumable space allocation. See Command-Line Parameters .

External Tables

The Oracle9i external tables feature is a complement to existing SQL*Loader functionality. It allows you to access data in external sources as if it were in a table in the database.

See Also:

DBVERIFY Utility

The DBVERIFY utility now has an additional command-line interface that allows you to specify a table segment or index segment for verification. It checks to make sure that a row chain pointer is within the segment being verified. See Using DBVERIFY to Validate a Segment .

Oracle8i Utilities New Features

The Oracle8i new features described in this section comprise the overall effort to optimize data transfer, maintenance, and administration. The features described in this section were added for releases 8.1.5, 8.1.6, and 8.1.7.

Export Utility

The following are new or enhanced Export features:

Export of subpartitions. See Table-Level and Partition-Level Export .
The ability to specify multiple dump files for an export command. See the parameters FILE and FILESIZE .
The ability to specify a query for the SELECT statements that Export uses to unload tables. See QUERY .
The maximum number of bytes in an export file on each volume of tape has been increased. See VOLSIZE .
The ability to export tables containing LOBs and objects, even if direct path is specified on the command line. See Invoking a Direct Path Export .
The ability to export and import precalculated optimizer statistics instead of recalculating the statistics at import time. (This feature is only applicable to certain exports and tables.) See STATISTICS .
Developers of domain indexes can export application-specific metadata associated with an index using the new ODCIIndexGetMetadata method on the ODCIIndex interface. See the Oracle9i Data Cartridge Developer's Guide for more information.
Export of transportable tablespace metadata. See TRANSPORT_TABLESPACE .

Import Utility

The following are new or enhanced Import features:

Import of subpartitions. See Table-Level and Partition-Level Import .
The ability to specify multiple dump files for an Import command. See the parameters FILE and FILESIZE .
The Import parameter TOID_NOVALIDATE, which allows you to cause Import to omit validation of object types (used typically when the types were created by a cartridge installation). See TOID_NOVALIDATE .
The maximum number of bytes in an export file on each volume of tape has been increased. See VOLSIZE .
Support for fine-grained access control. See Considerations When Importing Database Objects .
The ability to export and import precalculated optimizer statistics instead of recomputing the statistics at import time. (This feature is only applicable to certain exports and tables.) See STATISTICS .
Import of transportable tablespace metadata. See TRANSPORT_TABLESPACE .

SQL*Loader Utility

The following are new or enhanced SQL*Loader features:

There is now a PRESERVE parameter for use with CONTINUEIF THIS and CONTINUEIF NEXT.

If the PRESERVE parameter is not used, the continuation field is removed from all physical records when the logical record is assembled. That is, data values are allowed to span the records with no extra characters (continuation characters) in the middle.

If the PRESERVE parameter is used, the continuation field is kept in all physical records when the logical record is assembled.

See Using CONTINUEIF to Assemble Logical Records .
DATE fields that contain only whitespace are loaded as NULL fields and, therefore, no longer cause an error. See Datetime and Interval Datatypes .
As of release 8.1.5, the behavior of certain DDL clauses and restrictions has been changed to provide object support. Be sure to read Chapter 7, "Loading Objects, LOBs, and Collections" for a complete description of how this now works. Additionally, you should be sure to read the information in the following sections: