Oracle9
i
Database Utilities
Release 2 (9.2)
March 2002
Part No. A96652-01
Oracle9i Database Utilities, Release 2 (9.2)
Part No. A96652-01
Copyright © 1996, 2002, Oracle Corporation. All rights reserved.
Primary Author: Kathy Rich
Contributors: Lee Barton, Ellen Batbouta, Janet Blowney, George Claborn, Jay Davison, William Fisher,
Dean Gagne, John Galanes, John Kalogeropoulos, Jonathan Klein, Cindy Lim, Eric Magrath, Brian
McCarthy, Ray Pfau, Rich Phillips, Paul Reilly, Mike Sakayeda, Francisco Sanchez, Jim Stenoish
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent and other intellectual and industrial property
laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required
to obtain interoperability with other independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error-free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500

Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle8, Oracle8i, Oracle9i, Oracle Store, SQL*Plus, Oracle7, and
PL/SQL are trademarks or registered trademarks of Oracle Corporation. Other names may be
trademarks of their respective owners.
iii
Contents
List of Examples
List of Figures
List of Tables
Send Us Your Comments xxxiii
Preface xxxv
Audience xxxv
Documentation Accessibility xxxvi
Organization xxxvi
Related Documentation xxxix
Conventions xl
What’s New in Database Utilities? xliii
Oracle9i Utilities New Features for Release 9.2 xliii
Oracle9i Utilities New Features for Release 9.0.1 xlv
Oracle8i Utilities New Features xlviii
Part I Export and Import
1 Export
What Is the Export Utility? 1-2
Before Using Export 1-3
Running catexp.sql or catalog.sql 1-3

Ensuring Sufficient Disk Space 1-4
Verifying Access Privileges 1-4
Invoking Export 1-5
iv
Command-Line Entries 1-5
Interactive Export Prompts 1-6
Parameter Files 1-6
Invoking Export As SYSDBA 1-7
Export Modes 1-8
Table-Level and Partition-Level Export 1-12
Table-Level Export 1-12
Partition-Level Export 1-12
Processing Restrictions 1-13
Getting Online Help 1-13
Export Parameters 1-13
BUFFER 1-16
Example: Calculating Buffer Size 1-17
COMPRESS 1-17
CONSISTENT 1-18
CONSTRAINTS 1-20
DIRECT 1-20
FEEDBACK 1-20
FILE 1-20
FILESIZE 1-21
FLASHBACK_SCN 1-22
FLASHBACK_TIME 1-22
FULL 1-23
GRANTS 1-23
HELP 1-23
INDEXES 1-23

LOG 1-23
OBJECT_CONSISTENT 1-24
OWNER 1-24
PARFILE 1-24
QUERY 1-24
Restrictions 1-25
RECORDLENGTH 1-26
RESUMABLE 1-26
RESUMABLE_NAME 1-26
v
RESUMABLE_TIMEOUT 1-27
ROWS 1-27
STATISTICS 1-27
TABLES 1-28
Table Name Restrictions 1-29
TABLESPACES 1-30
TRANSPORT_TABLESPACE 1-31
TRIGGERS 1-31
TTS_FULL_CHECK 1-31
USERID (username/password) 1-31
VOLSIZE 1-32
Parameter Interactions 1-32
Example Export Sessions 1-32
Example Export Session in Full Database Mode 1-33
Example Export Session in User Mode 1-36
Example Export Sessions in Table Mode 1-38
Example 1: DBA Exporting Tables for Two Users 1-38
Example 2: User Exports Tables That He Owns 1-39
Example 3: Using Pattern Matching to Export Various Tables 1-40
Example Export Session Using Partition-Level Export 1-41

Example 1: Exporting a Table Without Specifying a Partition 1-41
Example 2: Exporting a Table with a Specified Partition 1-41
Example 3: Exporting a Composite Partition 1-42
Using the Interactive Method 1-43
Restrictions 1-48
Warning, Error, and Completion Messages 1-48
Log File 1-48
Warning Messages 1-48
Nonrecoverable Error Messages 1-49
Completion Messages 1-49
Exit Codes for Inspection and Display 1-49
Conventional Path Export Versus Direct Path Export 1-50
Invoking a Direct Path Export 1-52
Security Considerations for Direct Path Exports 1-52
Performance Issues for Direct Path Exports 1-53
vi
Network Considerations 1-53
Transporting Export Files Across a Network 1-53
Exporting and Importing with Oracle Net 1-53
Character Set and Globalization Support Considerations 1-54
Character Set Conversion 1-54
Effect of Character Set Sorting Order on Conversions 1-54
Multibyte Character Sets and Export and Import 1-55
Instance Affinity and Export 1-55
Considerations When Exporting Database Objects 1-56
Exporting Sequences 1-56
Exporting LONG and LOB Datatypes 1-56
Exporting Foreign Function Libraries 1-56
Exporting Offline Bitmapped Tablespaces 1-56
Exporting Directory Aliases 1-57

Exporting BFILE Columns and Attributes 1-57
External Tables 1-57
Exporting Object Type Definitions 1-57
Exporting Nested Tables 1-58
Exporting Advanced Queue (AQ) Tables 1-58
Exporting Synonyms 1-58
Possible Export Errors Related to Java Synonyms 1-59
Support for Fine-Grained Access Control 1-59
Transportable Tablespaces 1-59
Exporting from a Read-Only Database 1-60
Using Export and Import to Partition a Database Migration 1-60
Advantages of Partitioning a Migration 1-61
Disadvantages of Partitioning a Migration 1-61
How to Use Export and Import to Partition a Database Migration 1-61
Using Different Releases and Versions of Export 1-61
Restrictions When Using Different Releases and Versions of Export and Import 1-62
Examples of Using Different Releases of Export and Import 1-63
Creating Oracle Release 8.0 Export Files from an Oracle9i Database 1-64
Possible Errors When Using Different Releases and Versions 1-65
EXP-24 1-65
EXP-23 1-65
vii
EXP-37 1-65
2 Import
What Is the Import Utility? 2-2
Table Objects: Order of Import 2-3
Before Using Import 2-4
Running catexp.sql or catalog.sql 2-5
Verifying Access Privileges 2-5
Importing Objects into Your Own Schema 2-6

Importing Grants 2-7
Importing Objects into Other Schemas 2-7
Importing System Objects 2-7
Importing into Existing Tables 2-8
Manually Creating Tables Before Importing Data 2-8
Disabling Referential Constraints 2-8
Manually Ordering the Import 2-9
Effect of Schema and Database Triggers on Import Operations 2-9
Invoking Import 2-10
Command-Line Entries 2-10
Interactive Import Prompts 2-10
Parameter Files 2-11
Invoking Import As SYSDBA 2-12
Import Modes 2-13
Getting Online Help 2-14
Import Parameters 2-14
BUFFER 2-18
CHARSET 2-18
COMMIT 2-19
COMPILE 2-19
CONSTRAINTS 2-20
DATAFILES 2-20
DESTROY 2-20
FEEDBACK 2-20
FILE 2-21
FILESIZE 2-21
viii
FROMUSER 2-22
FULL 2-22
GRANTS 2-22

HELP 2-23
IGNORE 2-23
INDEXES 2-24
INDEXFILE 2-24
LOG 2-25
PARFILE 2-25
RECORDLENGTH 2-25
RESUMABLE 2-26
RESUMABLE_NAME 2-26
RESUMABLE_TIMEOUT 2-26
ROWS 2-27
SHOW 2-27
SKIP_UNUSABLE_INDEXES 2-27
STATISTICS 2-27
STREAMS_CONFIGURATION 2-28
STREAMS_INSTANTIATION 2-28
TABLES 2-29
Table Name Restrictions 2-30
TABLESPACES 2-31
TOID_NOVALIDATE 2-31
TOUSER 2-32
TRANSPORT_TABLESPACE 2-33
TTS_OWNERS 2-33
USERID (username/password) 2-33
VOLSIZE 2-34
Example Import Sessions 2-34
Example Import of Selected Tables for a Specific User 2-35
Example Import of Tables Exported by Another User 2-36
Example Import of Tables from One User to Another 2-37
Example Import Session Using Partition-Level Import 2-38

Example 1: A Partition-Level Import 2-38
Example 2: A Partition-Level Import of a Composite Partitioned Table 2-39
ix
Example 3: Repartitioning a Table on a Different Column 2-40
Example Import of Using Pattern Matching to Import Various Tables 2-43
Using the Interactive Method 2-44
Warning, Error, and Completion Messages 2-45
Log File 2-46
Warning Messages 2-46
Nonrecoverable Error Messages 2-46
Completion Messages 2-46
Exit Codes for Inspection and Display 2-47
Error Handling During an Import 2-47
Row Errors 2-47
Failed Integrity Constraints 2-48
Invalid Data 2-48
Errors Importing Database Objects 2-48
Object Already Exists 2-48
Sequences 2-49
Resource Errors 2-49
Domain Index Metadata 2-49
Table-Level and Partition-Level Import 2-49
Guidelines for Using Table-Level Import 2-50
Guidelines for Using Partition-Level Import 2-50
Migrating Data Across Partitions and Tables 2-51
Controlling Index Creation and Maintenance 2-52
Delaying Index Creation 2-52
Index Creation and Maintenance Controls 2-52
Example of Postponing Index Maintenance 2-53
Reducing Database Fragmentation 2-53

Network Considerations 2-54
Transporting Export Files Across a Network 2-54
Exporting and Importing with Oracle Net 2-54
Character Set and Globalization Support Considerations 2-54
Character Set Conversion 2-55
User Data 2-55
Data Definition Language (DDL) 2-55
Import and Single-Byte Character Sets 2-55
x
Import and Multibyte Character Sets 2-56
Considerations When Importing Database Objects 2-56
Importing Object Identifiers 2-56
Importing Existing Object Tables and Tables That Contain Object Types 2-58
Importing Nested Tables 2-58
Importing REF Data 2-59
Importing BFILE Columns and Directory Aliases 2-59
Importing Foreign Function Libraries 2-60
Importing Stored Procedures, Functions, and Packages 2-60
Importing Java Objects 2-60
Importing External Tables 2-60
Importing Advanced Queue (AQ) Tables 2-61
Importing LONG Columns 2-61
Importing Views 2-61
Importing Partitioned Tables 2-62
Support for Fine-Grained Access Control 2-62
Materialized Views and Snapshots 2-63
Snapshot Log 2-63
Snapshots 2-64
Importing a Snapshot 2-64
Importing a Snapshot into a Different Schema 2-64

Transportable Tablespaces 2-64
Storage Parameters 2-65
The OPTIMAL Parameter 2-66
Storage Parameters for OID Indexes and LOB Columns 2-66
Overriding Storage Parameters 2-66
The Export COMPRESS Parameter 2-66
Read-Only Tablespaces 2-66
Dropping a Tablespace 2-67
Reorganizing Tablespaces 2-67
Importing Statistics 2-68
Using Export and Import to Partition a Database Migration 2-69
Advantages of Partitioning a Migration 2-69
Disadvantages of Partitioning a Migration 2-69
How to Use Export and Import to Partition a Database Migration 2-69
xi
Using Export Files from a Previous Oracle Release 2-70
Using Oracle Version 7 Export Files 2-70
Check Constraints on DATE Columns 2-70
Using Oracle Version 6 Export Files 2-71
User Privileges 2-71
CHAR Columns 2-71
Status of Integrity Constraints 2-71
Length of Default Column Values 2-71
Using Oracle Version 5 Export Files 2-72
Restrictions When Using Different Releases and Versions of Export and Import 2-72
The CHARSET Parameter 2-73
Part II SQL*Loader
3 SQL*Loader Concepts
SQL*Loader Features 3-1
SQL*Loader Control File 3-3

Input Data and Datafiles 3-4
Fixed Record Format 3-4
Variable Record Format 3-5
Stream Record Format 3-6
Logical Records 3-7
Data Fields 3-8
LOBFILEs and Secondary Datafiles (SDFs) 3-9
Data Conversion and Datatype Specification 3-9
Discarded and Rejected Records 3-10
The Bad File 3-10
SQL*Loader Rejects 3-10
Oracle Rejects 3-10
The Discard File 3-11
Log File and Logging Information 3-11
Conventional Path Loads, Direct Path Loads, and External Table Loads 3-11
Conventional Path Loads 3-12
Direct Path Loads 3-12
Parallel Direct Path 3-12
xii
External Table Loads 3-13
Loading Objects, Collections, and LOBs 3-13
Supported Object Types 3-13
column-objects 3-13
row objects 3-14
Supported Collection Types 3-14
Nested Tables 3-14
VARRAYs 3-14
Supported LOB Types 3-14
Partitioned Object Support 3-15
Application Development: Direct Path Load API 3-15

4 SQL*Loader Command-Line Reference
Invoking SQL*Loader 4-1
Specifying Parameters in the Control File 4-3
Command-Line Parameters 4-3
BAD (bad file) 4-3
BINDSIZE (maximum size) 4-4
COLUMNARRAYROWS 4-4
CONTROL (control file) 4-4
DATA (datafile) 4-5
DATE_CACHE 4-5
DIRECT (data path) 4-6
DISCARD (filename) 4-6
DISCARDMAX (integer) 4-6
ERRORS (errors to allow) 4-6
EXTERNAL_TABLE 4-7
Restrictions When Using EXTERNAL_TABLE 4-8
FILE (file to load into) 4-9
LOAD (records to load) 4-9
LOG (log file) 4-9
MULTITHREADING 4-9
PARALLEL (parallel load) 4-10
PARFILE (parameter file) 4-10
READSIZE (read buffer size) 4-10
xiii
RESUMABLE 4-11
RESUMABLE_NAME 4-12
RESUMABLE_TIMEOUT 4-12
ROWS (rows per commit) 4-12
SILENT (feedback mode) 4-12
SKIP (records to skip) 4-13

SKIP_INDEX_MAINTENANCE 4-14
SKIP_UNUSABLE_INDEXES 4-14
STREAMSIZE 4-15
USERID (username/password) 4-15
Exit Codes for Inspection and Display 4-16
5 SQL*Loader Control File Reference
Control File Contents 5-2
Comments in the Control File 5-4
Specifying Command-Line Parameters in the Control File 5-4
OPTIONS Clause 5-4
Specifying Filenames and Object Names 5-5
Filenames That Conflict with SQL and SQL*Loader Reserved Words 5-5
Specifying SQL Strings 5-5
Operating System Considerations 5-5
Specifying a Complete Path 5-6
Backslash Escape Character 5-6
Nonportable Strings 5-6
Escaping the Backslash 5-7
Escape Character Is Sometimes Disallowed 5-7
Specifying Datafiles 5-7
Examples of INFILE Syntax 5-9
Specifying Multiple Datafiles 5-9
Identifying Data in the Control File with BEGINDATA 5-10
Specifying Datafile Format and Buffering 5-11
Specifying the Bad File 5-11
Examples of Specifying a Bad File Name 5-13
How Bad Files Are Handled with LOBFILEs and SDFs 5-13
Criteria for Rejected Records 5-13
xiv
Specifying the Discard File 5-14

Specifying the Discard File in the Control File 5-14
Specifying the Discard File from the Command Line 5-15
Examples of Specifying a Discard File Name 5-15
Criteria for Discarded Records 5-15
How Discard Files Are Handled with LOBFILEs and SDFs 5-16
Limiting the Number of Discarded Records 5-16
Handling Different Character Encoding Schemes 5-16
Multibyte (Asian) Character Sets 5-17
Unicode Character Sets 5-17
Database Character Sets 5-18
Datafile Character Sets 5-18
Input Character Conversion 5-19
CHARACTERSET Parameter 5-19
Control File Character Set 5-21
Character-Length Semantics 5-22
Interrupted Loads 5-24
Discontinued Conventional Path Loads 5-24
Discontinued Direct Path Loads 5-24
Load Discontinued Because of Space Errors 5-24
Load Discontinued Because Maximum Number of Errors Exceeded 5-25
Load Discontinued Because of Fatal Errors 5-25
Load Discontinued Because a Control+C Was Issued 5-25
Status of Tables and Indexes After an Interrupted Load 5-26
Using the Log File to Determine Load Status 5-26
Continuing Single-Table Loads 5-26
Assembling Logical Records from Physical Records 5-27
Using CONCATENATE to Assemble Logical Records 5-27
Using CONTINUEIF to Assemble Logical Records 5-27
Loading Logical Records into Tables 5-31
Specifying Table Names 5-32

INTO TABLE Clause 5-32
Table-Specific Loading Method 5-32
Loading Data into Empty Tables 5-33
Loading Data into Nonempty Tables 5-33
xv
Table-Specific OPTIONS Parameter 5-34
Loading Records Based on a Condition 5-35
Using the WHEN Clause with LOBFILEs and SDFs 5-35
Specifying Default Data Delimiters 5-36
fields_spec 5-36
termination_spec 5-36
enclosure_spec 5-36
Handling Short Records with Missing Data 5-37
TRAILING NULLCOLS Clause 5-37
Index Options 5-38
SORTED INDEXES Clause 5-38
SINGLEROW Option 5-38
Benefits of Using Multiple INTO TABLE Clauses 5-39
Extracting Multiple Logical Records 5-39
Relative Positioning Based on Delimiters 5-40
Distinguishing Different Input Record Formats 5-40
Relative Positioning Based on the POSITION Parameter 5-41
Distinguishing Different Input Row Object Subtypes 5-41
Loading Data into Multiple Tables 5-43
Summary 5-43
Bind Arrays and Conventional Path Loads 5-44
Size Requirements for Bind Arrays 5-44
Performance Implications of Bind Arrays 5-44
Specifying Number of Rows Versus Size of Bind Array 5-45
Calculations to Determine Bind Array Size 5-45

Determining the Size of the Length Indicator 5-47
Calculating the Size of Field Buffers 5-47
Minimizing Memory Requirements for Bind Arrays 5-49
Calculating Bind Array Size for Multiple INTO TABLE Clauses 5-50
6 Field List Reference
Field List Contents 6-1
Specifying the Position of a Data Field 6-3
Using POSITION with Data Containing Tabs 6-4
Using POSITION with Multiple Table Loads 6-4
xvi
Examples of Using POSITION 6-4
Specifying Columns and Fields 6-5
Specifying Filler Fields 6-6
Specifying the Datatype of a Data Field 6-7
SQL*Loader Datatypes 6-7
Nonportable Datatypes 6-8
INTEGER(n) 6-8
SMALLINT 6-9
FLOAT 6-9
DOUBLE 6-10
BYTEINT 6-10
ZONED 6-10
DECIMAL 6-11
VARGRAPHIC 6-12
VARCHAR 6-13
VARRAW 6-14
LONG VARRAW 6-14
Portable Datatypes 6-14
CHAR 6-15
Datetime and Interval Datatypes 6-16

GRAPHIC 6-18
GRAPHIC EXTERNAL 6-19
Numeric EXTERNAL 6-19
RAW 6-20
VARCHARC 6-20
VARRAWC 6-21
Conflicting Native Datatype Field Lengths 6-21
Field Lengths for Length-Value Datatypes 6-22
Datatype Conversions 6-22
Datatype Conversions for Datetime and Interval Datatypes 6-23
Specifying Delimiters 6-24
TERMINATED Fields 6-25
ENCLOSED Fields 6-25
Delimiter Marks in the Data 6-26
Maximum Length of Delimited Data 6-27
xvii
Loading Trailing Blanks with Delimiters 6-27
Conflicting Field Lengths for Character Datatypes 6-28
Predetermined Size Fields 6-28
Delimited Fields 6-28
Date Field Masks 6-29
Specifying Field Conditions 6-29
Comparing Fields to BLANKS 6-31
Comparing Fields to Literals 6-32
Using the WHEN, NULLIF, and DEFAULTIF Clauses 6-32
Loading Data Across Different Platforms 6-36
Byte Ordering 6-37
Specifying Byte Order 6-38
Using Byte Order Marks (BOMs) 6-39
Suppressing Checks for BOMs 6-41

Loading All-Blank Fields 6-41
Trimming Whitespace 6-42
Datatypes for Which Whitespace Can Be Trimmed 6-44
Field Length Specifications for Datatypes for Which Whitespace Can Be Trimmed 6-45
Predetermined Size Fields 6-45
Delimited Fields 6-45
Relative Positioning of Fields 6-46
No Start Position Specified for a Field 6-46
Previous Field Terminated by a Delimiter 6-46
Previous Field Has Both Enclosure and Termination Delimiters 6-47
Leading Whitespace 6-47
Previous Field Terminated by Whitespace 6-47
Optional Enclosure Delimiters 6-48
Trailing Whitespace 6-49
Enclosed Fields 6-49
Preserving Whitespace 6-49
PRESERVE BLANKS Option 6-49
Terminated by Whitespace 6-50
Applying SQL Operators to Fields 6-50
Referencing Fields 6-52
Common Uses of SQL Operators in Field Specifications 6-53
xviii
Combinations of SQL Operators 6-53
Using SQL Strings with a Date Mask 6-53
Interpreting Formatted Fields 6-54
Using SQL*Loader to Generate Data for Input 6-54
Loading Data Without Files 6-55
Setting a Column to a Constant Value 6-55
CONSTANT Parameter 6-55
Setting a Column to an Expression Value 6-56

EXPRESSION Parameter 6-56
Setting a Column to the Datafile Record Number 6-56
RECNUM Parameter 6-56
Setting a Column to the Current Date 6-56
SYSDATE Parameter 6-56
Setting a Column to a Unique Sequence Number 6-57
SEQUENCE Parameter 6-57
Generating Sequence Numbers for Multiple Tables 6-58
Example: Generating Different Sequence Numbers for Each Insert 6-58
7 Loading Objects, LOBs, and Collections
Loading Column Objects 7-1
Loading Column Objects in Stream Record Format 7-2
Loading Column Objects in Variable Record Format 7-3
Loading Nested Column Objects 7-4
Loading Column Objects with a Derived Subtype 7-4
Specifying Null Values for Objects 7-6
Specifying Attribute Nulls 7-6
Specifying Atomic Nulls 7-7
Loading Column Objects with User-Defined Constructors 7-8
Loading Object Tables 7-12
Loading Object Tables with a Subtype 7-13
Loading REF Columns 7-15
Real REF Columns 7-15
Primary Key REF Columns 7-16
Unscoped REF Columns That Allow Primary Keys 7-16
Loading LOBs 7-18
xix
Loading LOB Data from a Primary Datafile 7-19
LOB Data in Predetermined Size Fields 7-19
LOB Data in Delimited Fields 7-20

LOB Data in Length-Value Pair Fields 7-21
Loading LOB Data from an External LOBFILE (BFILE) 7-22
Loading LOB Data from LOBFILEs 7-23
Dynamic Versus Static LOBFILE Specifications 7-24
Examples of Loading LOB Data from LOBFILEs 7-24
Considerations When Loading LOBs from LOBFILEs 7-28
Loading Collections (Nested Tables and VARRAYs) 7-29
Restrictions in Nested Tables and VARRAYs 7-30
Secondary Datafiles (SDFs) 7-31
Dynamic Versus Static SDF Specifications 7-33
Loading a Parent Table Separately from Its Child Table 7-33
Memory Issues When Loading VARRAY Columns 7-34
8 SQL*Loader Log File Reference
Header Information 8-1
Global Information 8-2
Table Information 8-2
Column Information 8-3
Position 8-3
Length 8-4
Delimiter 8-4
Datatype 8-4
Datafile Information 8-4
Table Load Information 8-5
Summary Statistics 8-5
Oracle Statistics That Are Logged 8-6
Information About Single-Partition Loads 8-7
Statistics for Loading a Table 8-7
Additional Summary Statistics for Direct Path Loads and Multithreading 8-7
Log File Created When EXTERNAL_TABLE=GENERATE_ONLY 8-8
xx

9 Conventional and Direct Path Loads
Data Loading Methods 9-1
Conventional Path Load 9-4
Conventional Path Load of a Single Partition 9-4
When to Use a Conventional Path Load 9-4
Direct Path Load 9-5
Data Conversion During Direct Path Loads 9-6
Direct Path Load of a Partitioned or Subpartitioned Table 9-6
Direct Path Load of a Single Partition or Subpartition 9-7
Advantages of a Direct Path Load 9-8
Restrictions on Using Direct Path Loads 9-8
Restrictions on a Direct Path Load of a Single Partition 9-9
When to Use a Direct Path Load 9-9
Integrity Constraints 9-10
Field Defaults on the Direct Path 9-10
Loading into Synonyms 9-10
Using Direct Path Load 9-10
Setting Up for Direct Path Loads 9-10
Specifying a Direct Path Load 9-11
Building Indexes 9-11
Improving Performance 9-11
Temporary Segment Storage Requirements 9-12
Indexes Left in an Unusable State 9-12
Using Data Saves to Protect Against Data Loss 9-13
Using the ROWS Parameter 9-14
Data Save Versus Commit 9-14
Data Recovery During Direct Path Loads 9-15
Media Recovery and Direct Path Loads 9-15
Instance Recovery and Direct Path Loads 9-15
Loading LONG Data Fields 9-16

Loading Data As PIECED 9-16
Optimizing Performance of Direct Path Loads 9-17
Preallocating Storage for Faster Loading 9-17
Presorting Data for Faster Indexing 9-18
SORTED INDEXES Clause 9-18
xxi
Unsorted Data 9-18
Multiple-Column Indexes 9-19
Choosing the Best Sort Order 9-19
Infrequent Data Saves 9-20
Minimizing Use of the Redo Log 9-20
Disabling Archiving 9-20
Specifying the UNRECOVERABLE Parameter 9-20
Setting the NOLOG Attribute 9-21
Specifying the Number of Column Array Rows and Size of Stream Buffers 9-21
Specifying a Value for the Date Cache 9-22
Optimizing Direct Path Loads on Multiple-CPU Systems 9-23
Avoiding Index Maintenance 9-24
Direct Loads, Integrity Constraints, and Triggers 9-25
Integrity Constraints 9-25
Enabled Constraints 9-25
Disabled Constraints 9-25
Reenable Constraints 9-26
Database Insert Triggers 9-27
Replacing Insert Triggers with Integrity Constraints 9-27
When Automatic Constraints Cannot Be Used 9-27
Preparation 9-28
Using an Update Trigger 9-28
Duplicating the Effects of Exception Conditions 9-28
Using a Stored Procedure 9-29

Permanently Disabled Triggers and Constraints 9-30
Increasing Performance with Concurrent Conventional Path Loads 9-30
Parallel Data Loading Models 9-30
Concurrent Conventional Path Loads 9-31
Intersegment Concurrency with Direct Path 9-31
Intrasegment Concurrency with Direct Path 9-31
Restrictions on Parallel Direct Path Loads 9-32
Initiating Multiple SQL*Loader Sessions 9-32
Parameters for Parallel Direct Path Loads 9-33
Specifying Temporary Segments 9-33
Enabling Constraints After a Parallel Direct Path Load 9-34
xxii
PRIMARY KEY and UNIQUE KEY Constraints 9-35
General Performance Improvement Hints 9-35
10 SQL*Loader Case Studies
The Case Studies 10-2
Case Study Files 10-3
Tables Used in the Case Studies 10-4
Contents of Table emp 10-4
Contents of Table dept 10-4
Checking the Results of a Load 10-4
References and Notes 10-5
Case Study 1: Loading Variable-Length Data 10-5
Control File for Case Study 1 10-5
Running Case Study 1 10-6
Log File for Case Study 1 10-7
Case Study 2: Loading Fixed-Format Fields 10-8
Control File for Case Study 2 10-8
Datafile for Case Study 2 10-9
Running Case Study 2 10-9

Log File for Case Study 2 10-10
Case Study 3: Loading a Delimited, Free-Format File 10-11
Control File for Case Study 3 10-11
Running Case Study 3 10-13
Log File for Case Study 3 10-13
Case Study 4: Loading Combined Physical Records 10-14
Control File for Case Study 4 10-15
Datafile for Case Study 4 10-16
Rejected Records 10-16
Running Case Study 4 10-16
Log File for Case Study 4 10-17
Bad File for Case Study 4 10-18
Case Study 5: Loading Data into Multiple Tables 10-18
Control File for Case Study 5 10-19
Datafile for Case Study 5 10-20
Running Case Study 5 10-20
xxiii
Log File for Case Study 5 10-21
Loaded Tables for Case Study 5 10-23
Case Study 6: Loading Data Using the Direct Path Load Method 10-24
Control File for Case Study 6 10-25
Datafile for Case Study 6 10-25
Running Case Study 6 10-26
Log File for Case Study 6 10-26
Case Study 7: Extracting Data from a Formatted Report 10-28
Creating a BEFORE INSERT Trigger 10-28
Control File for Case Study 7 10-29
Datafile for Case Study 7 10-31
Running Case Study 7 10-31
Log File for Case Study 7 10-32

Case Study 8: Loading Partitioned Tables 10-34
Control File for Case Study 8 10-34
Table Creation 10-35
Datafile for Case Study 8 10-35
Running Case Study 8 10-36
Log File for Case Study 8 10-37
Case Study 9: Loading LOBFILEs (CLOBs) 10-38
Control File for Case Study 9 10-39
Datafiles for Case Study 9 10-40
Running Case Study 9 10-41
Log File for Case Study 9 10-42
Case Study 10: Loading REF Fields and VARRAYs 10-43
Control File for Case Study 10 10-43
Running Case Study 10 10-45
Log File for Case Study 10 10-45
Case Study 11: Loading Data in the Unicode Character Set 10-47
Control File for Case Study 11 10-48
Datafile for Case Study 11 10-49
Running Case Study 11 10-49
Log File for Case Study 11 10-50
Loaded Tables for Case Study 11 10-52
xxiv
Part III External Tables
11 External Tables Concepts
The Access Driver 11-2
External Table Restrictions 11-3
Location of Datafiles and Output Files 11-3
Using External Tables to Load Data 11-5
Parallel Access to External Tables 11-6
Performance Hints When Using External Tables 11-6

Behavior Differences Between SQL*Loader and External Tables 11-7
Multiple Primary Input Datafiles 11-7
Syntax and Datatypes 11-8
Rejected Rows 11-8
Byte-Order Marks 11-8
Default Character Sets and Date Masks 11-8
12 External Tables Access Parameters
access_parameters Clause 12-2
record_format_info Clause 12-3
FIXED length 12-4
VARIABLE size 12-5
DELIMITED BY 12-6
CHARACTERSET 12-7
DATA IS ENDIAN 12-7
BYTE ORDER MARK (CHECK | NOCHECK) 12-8
STRING SIZES ARE IN 12-8
LOAD WHEN 12-8
BADFILE | NOBADFILE 12-9
DISCARDFILE | NODISCARDFILE 12-9
LOG FILE | NOLOGFILE 12-10
SKIP 12-10
READSIZE 12-10
DATE_CACHE 12-11
string 12-11
xxv
condition_spec 12-12
[directory object name:] filename 12-12
condition 12-13
range start : range end 12-14
field_definitions Clause 12-15

delim_spec 12-16
Example: External Table with Terminating Delimiters 12-18
Example: External Table with Enclosure and Terminator Delimiters 12-18
Example: External Table with Optional Enclosure Delimiters 12-19
trim_spec 12-19
MISSING FIELD VALUES ARE NULL 12-20
field_list 12-21
pos_spec Clause 12-22
start 12-23
* 12-23
increment 12-23
end 12-23
length 12-23
datatype_spec Clause 12-24
[UNSIGNED] INTEGER [EXTERNAL] [(len)] 12-26
DECIMAL [EXTERNAL] and ZONED [EXTERNAL] 12-26
ORACLE_DATE 12-26
ORACLE_NUMBER 12-26
DOUBLE [EXTERNAL] 12-27
FLOAT [EXTERNAL] 12-27
RAW 12-27
CHAR 12-27
date_format_spec 12-28
VARCHAR and VARRAW 12-29
VARCHARC and VARRAWC 12-30
init_spec Clause 12-31
Part IV Other Utilities