<i>Oracle8i Utilities</i>

What is the Export Utility?

New in this Release

Reading the Contents of an Export File

Access Privileges

Export Modes

Understanding Table-Level and Partition-Level Export

Using Export

Before Using Export

Invoking Export

Getting Online Help

Specifying Export Parameters on the Command Line or in the Parameter File

Export Parameters

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

Restrictions

Warning, Error, and Completion Messages

Direct Path Export

Invoking a Direct Path Export

Character Set Conversion

Performance Issues

Incremental, Cumulative, and Complete Exports

Which Data is Exported?

Example Incremental Export Session

System Tables

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

Multi-Byte Character Sets and Export and Import

Instance Affinity and Export

Fine-Grained Access Support

Considerations in Exporting Database Objects

Exporting Sequences

Exporting LONG and LOB Datatypes

Exporting Foreign Function Libraries

Exporting Offline Bitmapped Tablespaces

Exporting Directory Aliases

Exporting BFILE Columns and Attributes

Exporting Object Type Definitions

Exporting Nested Tables

Exporting Advanced Queue (AQ) Tables

Transportable Tablespaces

Using Different Versions of Export

Using a Previous Version of Export

Using a Higher Version Export

Creating Oracle Release 8.0 Export Files from an Oracle8i Database

Creating Oracle Release 7 Export Files from an Oracle8i Database

Excluded Objects

What is the Import Utility?

New in this Release

Table Objects: Order of Import

Compatibility

Import Modes

Understanding Table-Level and Partition-Level Import

Using Import

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

Using Table-Level and Partition-Level Export and Import

Guidelines for Using Partition-Level Import

Migrating Data Across Partitions and Tables

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

Error Handling

Row Errors

Errors Importing Database Objects

Fatal Errors

Network Considerations

Transporting Export Files Across a Network

Exporting and Importing with Net8

Import and Snapshots

Master Table

Snapshot Log

Snapshots and Materialized Views

Import and Instance Affinity

Fine-Grained Access Support

Storage Parameters

Read-Only Tablespaces

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 when Importing Database Objects

Importing Object Identifiers

Importing Existing Object Tables and Tables That Contain Object Types

Importing Nested Tables

Importing REF Data

Importing BFILE Columns and Directory Aliases

Importing Foreign Function Libraries

Importing Stored Procedures, Functions, and Packages

Importing Java Objects

Importing Advanced Queue (AQ) Tables

Importing LONG Columns

Importing Views

Importing Tables

Transportable Tablespaces

Importing Statistics

Using Export Files from a Previous Oracle Release

Using Oracle Version 7 Export Files

Using Oracle Version 6 Export Files

Using Oracle Version 5 Export Files

The CHARSET Parameter

SQL*Loader Basics

SQL*Loader Control File

Input Data and Datafiles

Logical Records

Datafields

Data Conversion and Datatype Specification

Discarded and Rejected Records

The Bad File

SQL*Loader Discards

Log File and Logging Information

Conventional Path Load versus Direct Path Load

Loading Objects, Collections, and LOBs

Supported Object Types

Supported Collection Types

Supported LOB Types

New SQL*Loader DDL Behavior and Restrictions

New SQL*Loader DDL Support for Objects, Collections, and LOBs

Partitioned and Sub-Partitioned Object Support

Application Development: Direct Path Load API

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

Control File

Invoking SQL*Loader

Log File

Case 2: Loading Fixed-Format Fields

Case 3: Loading a Delimited, Free-Format File

Control File

Invoking SQL*Loader

Log File

Case 4: Loading Combined Physical Records

Case 5: Loading Data into Multiple Tables

Case 6: Loading Using the Direct Path Load Method

Control File

Invoking SQL*Loader

Log File

Case 7: Extracting Data from a Formatted Report

Dropping the Insert Trigger and the Global-Variable Package

Case 8: Loading Partitioned Tables

Case 9: Loading LOBFILEs (CLOBs)

Case 10: Loading REF Fields and VARRAYs

Control File

Invoking SQL*Loader

Log File

SQL*Loader's Data Definition Language (DDL) Syntax Diagrams

The SQL*Loader Control File

SQL*Loader DDL Syntax Diagram Notation

High-Level Syntax Diagrams

Expanded DDL Syntax

Position Specification

Delimiter Specification

Control File Basics

Comments in the Control File

Specifying Command-Line Parameters in the Control File

OPTIONS

Specifying Filenames and Objects Names

Filenames that Conflict with SQL and SQL*Loader Reserved Words

Specifying SQL Strings

Operating System Considerations

Identifying Data in the Control File with BEGINDATA

INFILE: Specifying Datafiles

Naming the File

Specifying Multiple Datafiles

Specifying READBUFFERS

Specifying Datafile Format and Buffering

File Processing Example

BADFILE: Specifying the Bad File

Rejected Records

Specifying the Discard File

Handling Different Character Encoding Schemes

Multi-Byte (Asian) Character Sets

Input Character Conversion

Loading into Empty and Non-Empty Tables

Loading into Empty Tables

Loading into Non-Empty Tables

APPEND

REPLACE

TRUNCATE

Continuing an Interrupted Load

Assembling Logical Records from Physical Records

Using 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

Index Options

SORTED INDEXES Option

SINGLEROW Option

Specifying Field Conditions

Comparing Fields to BLANKS

Comparing Fields to Literals

Specifying Columns and Fields

Specifying Filler 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

Summary

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

SQL*Loader Datatypes

Non-Portable Datatypes

Portable Datatypes

Numeric External Datatypes

Datatype Conversions

Specifying Delimiters

Conflicting Character Datatype Field Lengths

Loading Data Across Different Platforms

Determining the Size of the Bind Array

Minimum Requirements

Performance Implications

Specifying Number of Rows vs. Size of Bind Array

Calculations

Minimizing Memory Requirements for the Bind Array

Multiple INTO TABLE Statements

Generated Data

Setting a Column to Null or Zero

DEFAULTIF Clause

NULLIF Keyword

Null Columns at the End of a Record

Loading All-Blank Fields

Trimming Blanks and Tabs

Datatypes

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

Common Uses

Combinations of Operators

Use with Date Mask

Interpreting Formatted Fields

Loading Column Objects

Loading Column Objects in Stream Record Format

Loading Column Objects in Variable Record Format

Loading Nested Column Objects

Specifying NULL Values for Objects

Loading Object Tables

Loading REF Columns

Loading LOBs

Internal LOBs (BLOB, CLOB, NCLOB)

External LOB (BFILE)

Loading Collections (Nested Tables and VARRAYs)

Memory Issues when Loading VARRAY Columns

SQL*Loader Command Line

Using Command-Line Keywords

Specifying Keywords in the Control File

Command-Line Keywords

BAD (bad file)

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)

LOG (log file)

PARFILE (parameter file)

PARALLEL (parallel load)

READSIZE (read buffer)

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

Header Information

Global Information

Table Information

Datafile Information

Table Load Information

Summary Statistics

Oracle Statistics Reporting to the Log

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

Data Saves

Recovery

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

DBVERIFY

Restrictions

Syntax

Sample DBVERIFY Output

Reserved Word List and Information

Using the DB2 RESUME Option

Inclusions for Compatibility

LOG Statement

WORKDDN Statement

SORTDEVT and SORTNUM Statements

DISCARD Specification

Restrictions

SQL*Loader Syntax with DB2-compatible Statements

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Export

2
Import

3 SQL*Loader Concepts

4 SQL*Loader Case Studies

5 SQL*Loader Control File Reference

6 SQL*Loader Command-Line Reference

7 SQL*Loader: Log File Reference

8 SQL*Loader: Conventional and Direct Path Loads

9 Offline Database Verification Utility

A SQL*Loader Reserved Words

B DB2/DXT User Notes

Index