Preface

This manual describes how to use the Oracle8i utilities for data transfer, data maintenance, and database administration.

Oracle8i Utilities contains information that describes the features and functionality of the Oracle8i and the Oracle8i Enterprise Edition products. Oracle8i and Oracle8i Enterprise Edition have the same basic features. However, several advanced features are available only with the Enterprise Edition, and some of these are optional.

For information about the differences between Oracle8i and the Oracle8i Enterprise Edition and the features and options that are available to you, see Getting to Know Oracle8i.

The Oracle Utilities

This manual describes the basic concepts behind each utility and provides examples to show how the utilities are used.

Audience

This manual is for database administrators (DBAs), application programmers, security administrators, system operators, and other Oracle users who perform the following tasks:

Archive data, back up an Oracle database, or move data between Oracle databases using the Export/Import utilities
Load data into Oracle tables from operating system files using SQL*Loader
Create and maintain user-defined character sets (NLS utilities) and other Oracle NLS data

To use this manual, you need a working knowledge of SQL and Oracle fundamentals, information that is contained in Oracle8i Concepts. In addition, SQL*Loader requires that you know how to use the file management facilities of your operating system.

Note: This manual does not contain instructions for installing the utilities, which is operating system-specific. Installation instructions for the utilities can be found in your operating system-specific Oracle documentation.

How Oracle8i Utilities Is Organized

This manual is divided into the following parts:

Part I: Export/Import

Chapter 1, "Export"

This chapter describes how to use Export to write data from an Oracle database into transportable files. It discusses export guidelines, export modes, interactive and command-line methods, parameter specifications, and describes Export object support. It also provides example Export sessions.

Chapter 2, "Import"

This chapter describes how to use Import to read data from Export files into an Oracle database. It discusses import guidelines, interactive and command-line methods, parameter specifications, and describes Import object support. It also provides several examples of Import sessions.

Part II: SQL*Loader

Chapter 3, "SQL*Loader Concepts"

This chapter introduces SQL*Loader and describes its features. It also introduces data loading concepts (including object support). It discusses input to SQL*Loader, database preparation, and output from SQL*Loader.

Chapter 4, "SQL*Loader Case Studies"

This chapter presents case studies that illustrate some of the features of SQL*Loader. It demonstrates the loading of variable-length data, fixed-format records, a free-format file, multiple physical records as one logical record, multiple tables, direct path loads, and loading objects, collections, and REF columns.

Chapter 5, "SQL*Loader Control File Reference"

This chapter describes the control file syntax you use to configure SQL*Loader and to describe to SQL*Loader how to map your data to Oracle format. It provides detailed syntax diagrams and information about specifying data files, tables and columns, the location of data, the type and format of data to be loaded, and more.

Chapter 6, "SQL*Loader Command-Line Reference"

This chapter describes the command-line syntax used by SQL*Loader. It discusses command-line arguments, suppressing SQL*Loader messages, sizing the bind array, and more.

Chapter 7, "SQL*Loader: Log File Reference"

This chapter describes the information contained in SQL*Loader log file output.

Chapter 8, "SQL*Loader: Conventional and Direct Path Loads"

This chapter describes the differences between a conventional path load and a direct path load. A direct path load is a high performance option that significantly reduces the time required to load large quantities of data.

Part III: Offline Database Verification Utility

Chapter 9, "DBVERIFY: Offline Database Verification Utility"

This chapter describes how to use the offline database verification utility, DBVERIFY.

Part IV: Appendixes

Appendix A, "SQL*Loader Reserved Words"

This appendix lists the words reserved for use only by SQL*Loader.

Appendix B, "DB2/DXT User Notes"

This appendix describes differences between the data definition language syntax of SQL*Loader and DB2 Load Utility control files. It discusses SQL*Loader extensions to the DB2 Load Utility, the DB2 RESUME option, options (included for compatibility), and SQL*Loader restrictions.

What Is New in Oracle8i?

This section lists new and changed features for the Import, Export, and SQL*Loader utilities. Cross-references are provided that tell you where you can find more detailed information on each feature.

New in Release 8.1.5

The following sections describe features that were new or changed as of release 8.1.5.

New Export Features

The following are new Export features as of release 8.1.5:

Export of subpartitions. See Understanding 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 recomputing 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 Oracle8i Data Cartridge Developer's Guide for more information.
Export of procedural objects. The data definition language for procedural objects is now implemented as PL/SQL rather than SQL, for example, Advanced Queues and Resource Scheduler objects.
Export of transportable tablespace metadata. See TRANSPORT_TABLESPACE .

New Import Features

The following are new Import features as of release 8.1.5:

Import of subpartitions. See Using Table-Level and Partition-Level Export and 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 .
Fine-grained access support. See Fine-Grained Access Support .
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 RECALCULATE_STATISTICS .
Import of transportable tablespace metadata. See TRANSPORT_TABLESPACE .

New SQL*Loader DDL Behavior and Restrictions

In order to provide object support, the behavior of certain DDL clauses and certain restrictions has been changed from previous releases. These changes apply in all cases, not just when you are loading objects, collections, or LOBs. See SQL*Loader DDL Behavior and Restrictions for a description of these changes.

Conventions Used in This Manual

This manual follows textual and typographic conventions explained in the following sections.

Text of the Manual

The following conventions are used in the text of this manual:

UPPERCASE Words

Uppercase text is used to call attention to command keywords, object names, parameters, filenames, and so on, for example:

"If you create a private rollback segment, its name must be included in the ROLLBACK_SEGMENTS parameter in the PARAMETER file."

Italicized Words

Italicized words are used at the first occurrence and definition of a term, as in the following example:

"A database is a collection of data to be treated as a unit. The general purpose of a database is to store and retrieve related information, as needed."

Italicized words are used also to indicate emphasis, book titles, and to highlight names of performance statistics.

PL/SQL, SQL, and SQL*Plus commands and statements are displayed in a fixed-width font using the following conventions, separated from normal text as in the following example:

ALTER TABLESPACE users   ADD DATAFILE 'users2.ora' SIZE 50K;

Punctuation:
, ' "

Example statements may include punctuation such as commas or quotation marks. All punctuation given in example statements is required. Depending on the application in use, a semicolon or other terminator may or may not be required to end a statement.

UPPERCASE Words:
INSERT, SIZE

Uppercase words in example statements indicate the keywords in Oracle SQL. However, when you issue statements, keywords are not case-sensitive.

lowercase Words:
emp, users2.ora

Lowercase words in example statements indicate words supplied only for the context of the example. For example, lowercase words may indicate the name of a table, column, or file. Some operating systems are case-sensitive. Refer to your installation or user's manual to find whether you must pay attention to case.

UPPERCASE Words	Uppercase text is used to call attention to command keywords, object names, parameters, filenames, and so on, for example:
	"If you create a private rollback segment, its name must be included in the ROLLBACK_SEGMENTS parameter in the PARAMETER file."
Italicized Words	Italicized words are used at the first occurrence and definition of a term, as in the following example:
	"A database is a collection of data to be treated as a unit. The general purpose of a database is to store and retrieve related information, as needed."
	Italicized words are used also to indicate emphasis, book titles, and to highlight names of performance statistics.