Oracle8 Utilities
Release 8.0
A58244-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

The Oracle Utilities

How Oracle8 Utilities Is Organized

Part I: Export/Import

Part II: SQL*Loader

Part III: NLS Utilities

Part IV: Offline Database Verification Utility

Conventions Used in This Manual

Text of the Manual

We Welcome Your Comments

Part I Export/Import

1 Export

What is the Export Utility?

Reading the Contents of an Export File

Access Privileges

Understanding Table-Level and Partition-Level Export

Before Using Export

Invoking Export

Getting Online Help

The Parameter File

Export Parameters

POINT_IN_TIME_RECOVER

RECOVERY_TABLESPACES

Parameter Interactions

Example Export Sessions

Example Export Session in Full Database Mode

Example Export Session in User Mode

Example Export Sessions in Table Mode

Example Export Session Using Partition-Level Export

Using the Interactive Method

Warning, Error, and Completion Messages

Warning Messages

Fatal Error Messages

Completion Messages

Direct Path Export

Invoking a Direct Path Export

Character Set Conversion

Performance Issues

Incremental, Cumulative, and Complete Exports

Incremental Exports

Cumulative Exports

Complete Exports

Which Data Is Exported?

Example Incremental Export Session

Network Considerations

Transporting Export Files Across a Network

Exporting and Importing with Net8

Character Set and NLS Considerations

Character Set Conversion

NCHAR Conversion During Export and Import

Single-Byte Character Sets During Export and Import

Multi-Byte Character Sets and Export and Import

Considerations in Exporting Database Objects

Exporting Sequences

Exporting LONG Datatypes

Exporting Foreign Function Libraries

Exporting Directory Aliases

Exporting BFILE Columns and Attributes

Exporting Array Data

Exporting Object Type Definitions

Exporting Advanced Queue (AQ) Tables

Exporting Nested Tables

Using Different Versions of Export

Using a Previous Version of Export

Using a Higher Version Export

Creating Oracle Release 7 Export Files from an Oracle8 Server

Excluded Objects

Exporting to Version 6

2
Import

What is the Import Utility?

Table Objects: Order of Import

Understanding Table-Level and Partition-Level Import

Before Using Import

Invoking Import

Getting Online Help

The Parameter File

Privileges Required to Use Import

Access Privileges

Importing Objects into Your Own Schema

Importing Grants

Importing Objects into Other Schemas

Importing System Objects

User Privileges

Importing into Existing Tables

Manually Creating Tables before Importing Data

Disabling Referential Constraints

Manually Ordering the Import

Import Parameters

POINT_IN_TIME_RECOVER

SKIP_UNUSABLE_INDEXES

Using Table-Level and Partition-Level Export and Import

Guidelines for Using Partition-Level Import

Migrating Data Across Partitions and Tables

Combining Multiple Partitions into One

Reconfiguring Partitions

Example Import Sessions

Example Import of Selected Tables for a Specific User

Example Import of Tables Exported by Another User

Example Import of Tables from One User to Another

Example Import Session Using Partition-Level Import

Using the Interactive Method

Importing Incremental, Cumulative, and Complete Export Files

Restoring a Set of Objects

Importing Object Types and Foreign Function Libraries from an Incremental Export File

Controlling Index Creation and Maintenance

Index Creation and Maintenance Controls

Delaying Index Creation

Reducing Database Fragmentation

Warning, Error, and Completion Messages

Errors Importing Database Objects

Network Considerations

Transporting Export Files Across a Network

Exporting and Importing with Net8

Import and Snapshots

Storage Parameters

Read-Only Tablespaces

Rollback Segments

Dropping a Tablespace

Reorganizing Tablespaces

Character Set and NLS Considerations

Character Set Conversion

Import and Single-Byte Character Sets

Import and Multi-Byte Character Sets

Considerations for Importing Database Objects

Importing Object Identifiers

Importing Existing Object Tables and Tables That Contain Object Types

Importing Nested Tables

Importing REF Data

Importing Array Data

Importing BFILE Columns and Directory Aliases

Importing Foreign Function Libraries

Importing Stored Procedures, Functions, and Packages

Importing Advanced Queue (AQ) Tables

Importing LONG Columns

Importing Views

Generating Statistics on Imported Data

Using Oracle7 Export Files

Check Constraints on DATE Columns

Using Oracle Version 6 Export Files

Syntax of Integrity Constraints

Status of Integrity Constraints

Length of DEFAULT Column Values

Using Oracle Version 5 Export Files

Part II SQL*Loader

3 SQL*Loader Concepts

SQL*Loader Basics

SQL*Loader Control File

Control File Contents and Storage

Data Definition Language (DDL)

Input Data and Datafiles

Input Data Formats

Data Conversion and Datatype Specification

Discarded and Rejected Records

SQL*Loader Discards

Log File and Logging Information

Conventional Path Load versus Direct Path Load

Partitioned Object Support

4 SQL*Loader Case Studies

The Case Studies

Case Study Files

Tables Used in the Case Studies

Contents of Table EMP

Contents of Table DEPT

References and Notes

Running the Case Study SQL Scripts

Case 1: Loading Variable-Length Data

Invoking SQL*Loader

Case 2: Loading Fixed-Format Fields

Invoking SQL*Loader

Case 3: Loading a Delimited, Free-Format File

Invoking SQL*Loader

Case 4: Loading Combined Physical Records

Invoking SQL*Loader

Case 5: Loading Data into Multiple Tables

Invoking SQL*Loader

Case 6: Loading Using the Direct Path Load Method

Invoking SQL*Loader

Case 7: Extracting Data from a Formatted Report

Invoking SQL*Loader

Dropping the Insert Trigger and the Global-Variable Package

Case 8: Loading a Fixed Record Length Format File

Input Data File

Invoking SQL*Loader

5 SQL*Loader Control File Reference

Data Definition Language (DDL) Syntax

High-Level Syntax Diagrams

Expanded Clauses and Their Functionality

Position Specification
pos_spec

Field Condition
field_condition

Column Name
column_name

Datatype Specification
datatype_spec

Precision vs. Length
precision
length

Delimiter Specification
delimiter_spec

Specifying Command-Line Parameters in the Control File

Specifying RECOVERABLE and UNRECOVERABLE

Specifying Filenames and Database Objects

Database Object Names within Double Quotation Marks

SQL String within Double Quotation Marks

Filenames within Single Quotation Marks

Quotation Marks in Quoted Strings

Backslash Escape Character

Using a Backslash in Filenames

Including Data in the Control File with BEGINDATA

Identifying Datafiles

Naming the File

Specifying Multiple Datafiles

Examples of How to Specify a Datafile

Specifying READBUFFERS

Specifying Datafile Format and Buffering

File Processing Example

Specifying the Bad File

Examples of How to Specify a Bad File

Rejected Records

Integrity Constraints

Specifying the Discard File

Using a Control-File Definition

Examples of How to Specify a Discard File

Discarded Records

Limiting the Number of Discards

Handling Different Character Encoding Schemes

Multi-Byte (Asian) Character Sets

Input Character Conversion

Loading into Empty and Non-Empty Tables

How Non-Empty Tables are Affected

Specifying One Method for All Tables

Continuing an Interrupted Load

State of Tables and Indexes

Using the Log File

Dropping Indexes

Continuing Single Table Loads

Continuing Multiple Table Conventional Loads

Continuing Multiple Table Direct Loads

Assembling Logical Records from Physical Records

Examples of How to Specify CONTINUEIF

Loading Logical Records into Tables

Specifying Table Names

Table-Specific Loading Method

Table-Specific OPTIONS keyword

Choosing which Rows to Load

Specifying Default Data Delimiters

Handling Short Records with Missing Data

SORTED INDEXES Option

SINGLEROW Option

Specifying Field Conditions

Comparing Fields to BLANKS

Comparing Fields to Literals

Specifying Columns and Fields

Specifying the Datatype of a Data Field

Specifying the Position of a Data Field

Using POSITION with Data Containing TABs

Using POSITION with Multiple Table Loads

Using Multiple INTO TABLE Statements

Extracting Multiple Logical Records

Distinguishing Different Input Record Formats

Loading Data into Multiple Tables

Generating Data

Loading Data Without Files

Setting a Column to a Constant Value

Setting a Column to the Datafile Record Number

Setting a Column to the Current Date

Setting a Column to a Unique Sequence Number

Generating Sequence Numbers for Multiple Tables

Specifying Datatypes

Datatype Conversions

Native Datatypes

Character Datatypes

Numeric External Datatypes

Specifying Delimiters

Conflicting Character Datatype Field Lengths

Loading Data Across Different Operating Systems

Determining the Size of the Bind Array

Minimum Requirements

Performance Implications

Specifying Number of Rows vs. Size of Bind Array

Minimizing Memory Requirements for the Bind Array

Multiple INTO TABLE Statements

Setting a Column to Null or Zero

DEFAULTIF Clause

Null Columns at the End of a Record

Loading All-Blank Fields

Trimming Blanks and Tabs

Field Length Specifications

Relative Positioning of Fields

Leading Whitespace

Trailing Whitespace

Enclosed Fields

Trimming Whitespace: Summary

Preserving Whitespace

PRESERVE BLANKS Keyword

Applying SQL Operators to Fields

Referencing Fields

Referencing Fields That Are SQL*Loader Keywords

Combinations of Operators

Use with Date Mask

Interpreting Formatted Fields

6 SQL*Loader Command-Line Reference

SQL*Loader Command Line

Using Command-Line Keywords

Specifying Keywords in the Control File

Command-Line Keywords

BINDSIZE (maximum size)

CONTROL (control file)

DATA (data file)

DIRECT (data path)

DISCARD (discard file)

DISCARDMAX (discards to disallow)

ERRORS (errors to allow)

FILE (file to load into)

LOAD (records to load)

PARFILE (parameter file)

PARALLEL (parallel load)

ROWS (rows per commit)

SILENT (feedback mode)

SKIP (records to skip)

USERID (username/password)

Index Maintenance Options

SKIP_UNUSABLE_INDEXES

SKIP_INDEX_MAINTENANCE

Exit Codes for Inspection and Display

7 SQL*Loader: Log File Reference

Header Information

Global Information

Table Information

Datafile Information

Table Load Information

Summary Statistics

Oracle8 Statistics Reporting to the Log

8 SQL*Loader: Conventional and Direct Path Loads

Data Loading Methods

Conventional Path Load

Direct Path Load

Using Direct Path Load

Setting Up for Direct Path Loads

Specifying a Direct Path Load

Building Indexes

Indexes Left in Index Unusable State

Loading LONG Data Fields

Maximizing Performance of Direct Path Loads

Pre-allocating Storage for Faster Loading

Pre-sorting Data for Faster Indexing

Infrequent Data Saves

Minimizing Use of the Redo Log

Disable Archiving

Specifying UNRECOVERABLE

NOLOG Attribute

Avoiding Index Maintenance

Direct Loads, Integrity Constraints, and Triggers

Integrity Constraints

Database Insert Triggers

Permanently Disabled Triggers & Constraints

Alternative: Concurrent Conventional Path Loads

Parallel Data Loading Models

Concurrent Conventional Path Loads

Inter-Segment Concurrency with Direct Path

Intra-Segment Concurrency with Direct Path

Restrictions on Parallel Direct Path Loads

Initiating Multiple SQL*Loader Sessions

Options Keywords for Parallel Direct Path Loads

Enabling Constraints After a Parallel Direct Path Load

General Performance Improvement Hints

Part III NLS Utilities

9 National Language Support Utilities

NLS Data Installation Utility

NLS Data Object Files

NLS Configuration Utility

NLS Calendar Utility

Part IV Offline Database Verification Utility

10 Offline Database Verification Utility

Enterprise Manager

Sample DB_VERIFY Output

A SQL*Loader Reserved Words

B DB2/DXT User Notes

SQL*Loader Extensions to the DB2 Load Utility

Using the DB2 RESUME Option

Inclusions for Compatibility

WORKDDN Statement

SORTDEVT and SORTNUM Statements

DISCARD Specification

FORMAT Statement

DBCS Graphic Strings

SQL*Loader Syntax with DB2-compatible Statements

Copyright © 1997 Oracle Corporation.
All Rights Reserved.