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 migrating 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
The following sections describe new and enhanced features that were introduced in Oracle9i for the Export, Import, SQL*Loader, and DBVERIFY 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:
- 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.
- 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:
- 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 Feature
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 Chapter 11, "External Tables Concepts" and Chapter 12, "External Tables Access Parameters".
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:
