Oracle8 Server Utilities

The Oracle Utilities

Prerequisites

Audience

How This Book Is Organized

Part I: Export/Import

Part II: SQL*Loader

Part III: NLS Utilities

Part IV: Appendixes

Conventions Used in This Manual

We Welcome Your Comments

Import/Export Overview

Export Basics

Reading the Contents of an Export File

Access Privileges

Export Modes

Understanding Table-Level and Partition-Level Export

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

Invoking a Direct Path Export

Character Set Conversion

Performance Issues

Restrictions

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

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 Oracle7 Export Files from an Oracle8 Server

Excluded Objects

Exporting to Version 6

Import/Export Overview

Import Basics

Table Objects: Order of Import

Compatibility

Import Modes

Understanding Table-Level and Partition-Level 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

POINT_IN_TIME_RECOVER

RECORDLENGTH

ROWS

SHOW

SKIP_UNUSABLE_INDEXES

TABLES

TOUSER

USERID

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

Error Handling

Row Errors

Errors Importing Database Objects

Fatal Errors

Network Considerations

Transporting Export Files Across a Network

Exporting and Importing with Net8

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

CHAR columns

LONG columns

Syntax of Integrity Constraints

Status of Integrity Constraints

Length of DEFAULT Column Values

Using Oracle Version 5 Export Files

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

The Bad File

SQL*Loader Discards

Log File and Logging Information

Conventional Path Load versus Direct Path Load

Partitioned Object Support

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 a Fixed Record Length Format File

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

Date Mask

Delimiter Specification, delimiter_spec

Comments

Specifying Command-Line Parameters in the Control File

OPTIONS

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

Index Options

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

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

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

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 of 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

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)

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

Table Load Information

Summary Statistics

Oracle8 Statistics Reporting to the Log

Data Loading Methods

Conventional Path Loads

Direct Path Loads

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

NLS Data Installation Utility

NLS Data Object Files

NLS Configuration Utility

Sample DB_VERIFY Output

SQL*Loader Extensions to the DB2 Load Utility

Using the DB2 RESUME Option

Inclusions for Compatibility

LOG Statement

WORKDDN Statement

SORTDEVT and SORTNUM Statements

DISCARD Specification

SQL*Loader Syntax with DB2-compatible Statements

Contents

Title and Copyright Information

Preface

Part I Export/Import

Chapter 1 Export

Chapter 2 Import

Part II SQL*Loader

Chapter 3 SQL*Loader Concepts

Chapter 4 SQL*Loader Case Studies

Chapter 5 SQL*Loader Control File Reference

Chapter 6 SQL*Loader Command-Line Reference

Chapter 7 SQL*Loader: Log File Reference

Chapter 8 SQL*Loader: Conventional and Direct Path Loads

Part III NLS Utilities

Chapter 9 National Language Support Utilities

Chapter 10 Offline Database Verification Utility

Part IV Appendixes

Appendix A SQL*Loader Reserved Words

Appendix B DB2/DXT User Notes

Index