Oracle8

Utilities
Release 8.0
December 1997
Part No. A58244-01
Oracle8 Utilities
Part No. A58244-01
Release 8.0
Copyright © 1990, 1997, Oracle Corporation. All rights reserved.
Primary Author: Jason Durbin
Contributors: Karleen Aghevli, Allen Brumm, Paul Lane, Visar Nimani, Joan Pearson, Mike
Sakayeda, James Stenois, Chao Wang , Gail Ymanaka, Hiro Yoshioka
The programs are not intended for use in any nuclear, aviation, mass transit, medical, or other
inherently dangerous applications. It shall be licensee's responsibility to take all appropriate fail-
safe, back up, redundancy and other measures to ensure the safe use of such applications if the
Programs are used for such purposes, and Oracle disclaims liability for any damages caused by
such use of the Programs.
This Program contains proprietary information of Oracle Corporation; it is provided under a license
agreement containing restrictions on use and disclosure and is also protected by copyright patent and
other intellectual property law. Reverse engineering of the software 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.
If this Program is delivered to a U.S. Government Agency of the Department of Defense, then it is deliv-
ered with Restricted Rights and the following legend is applicable:
Restricted Rights Legend Programs delivered subject to the DOD FAR Supplement are 'commercial
computer software' and use, duplication and disclosure of the Programs 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 disclo-
sure of the Programs shall be subject to the restrictions in FAR 52..227-14, Rights in Data -- General,

Getting Online Help ..................................................................................................................... 1-9
The Parameter File...................................................................................................................... 1-10
Export Parameters ............................................................................................................................. 1-11
BUFFER ........................................................................................................................................ 1-13
COMPRESS.................................................................................................................................. 1-13
CONSISTENT.............................................................................................................................. 1-14
CONSTRAINTS .......................................................................................................................... 1-15
DIRECT ........................................................................................................................................ 1-15
FEEDBACK.................................................................................................................................. 1-16
FILE............................................................................................................................................... 1-16
FULL............................................................................................................................................. 1-16
GRANTS....................................................................................................................................... 1-16
HELP............................................................................................................................................. 1-17
INCTYPE...................................................................................................................................... 1-17
INDEXES...................................................................................................................................... 1-17
LOG............................................................................................................................................... 1-17
OWNER........................................................................................................................................ 1-17
PARFILE....................................................................................................................................... 1-17
POINT_IN_TIME_RECOVER................................................................................................... 1-18
RECORD ...................................................................................................................................... 1-18
RECORDLENGTH ..................................................................................................................... 1-18
RECOVERY_TABLESPACES.................................................................................................... 1-19
ROWS ........................................................................................................................................... 1-19
STATISTICS................................................................................................................................. 1-19
TABLES ........................................................................................................................................ 1-19
USERID......................................................................................................................................... 1-21
Parameter Interactions ............................................................................................................... 1-21
Example Export Sessions................................................................................................................. 1-22
Example Export Session in Full Database Mode.................................................................... 1-22
Example Export Session in User Mode.................................................................................... 1-24

Character Set Conversion.......................................................................................................... 1-45
NCHAR Conversion During Export and Import .................................................................. 1-45
Single-Byte Character Sets During Export and Import......................................................... 1-46
Multi-Byte Character Sets and Export and Import................................................................ 1-46
Considerations in Exporting Database Objects.......................................................................... 1-46
Exporting Sequences .................................................................................................................. 1-46
Exporting LONG Datatypes ..................................................................................................... 1-47
Exporting Foreign Function Libraries ..................................................................................... 1-47
Exporting Directory Aliases...................................................................................................... 1-47
Exporting BFILE Columns and Attributes.............................................................................. 1-47
vi
Exporting Array Data................................................................................................................. 1-47
Exporting Object Type Definitions........................................................................................... 1-48
Exporting Advanced Queue (AQ) Tables ............................................................................... 1-49
Exporting Nested Tables............................................................................................................ 1-49
Using Different Versions of Export............................................................................................... 1-49
Using a Previous Version of Export......................................................................................... 1-49
Using a Higher Version Export................................................................................................. 1-50
Creating Oracle Release 7 Export Files from an Oracle8 Server.............................................. 1-50
Excluded Objects......................................................................................................................... 1-51
Exporting to Version 6 ............................................................................................................... 1-51
2 Import
What is the Import Utility? ............................................................................................................... 2-3
Table Objects: Order of Import................................................................................................... 2-4
Compatibility................................................................................................................................. 2-5
Import Modes ...................................................................................................................................... 2-5
Understanding Table-Level and Partition-Level Import........................................................ 2-6
Using Import........................................................................................................................................ 2-7
Before Using Import..................................................................................................................... 2-7
Invoking Import............................................................................................................................ 2-7

LOG .............................................................................................................................................. 2-26
PARFILE ...................................................................................................................................... 2-26
POINT_IN_TIME_RECOVER................................................................................................... 2-26
RECORDLENGTH ..................................................................................................................... 2-26
ROWS ........................................................................................................................................... 2-27
SHOW........................................................................................................................................... 2-27
SKIP_UNUSABLE_INDEXES................................................................................................... 2-27
TABLES ........................................................................................................................................ 2-27
TOUSER ....................................................................................................................................... 2-29
USERID ........................................................................................................................................ 2-29
Using Table-Level and Partition-Level Export and Import ...................................................... 2-30
Guidelines for Using Partition-Level Import ......................................................................... 2-30
Migrating Data Across Partitions and Tables......................................................................... 2-31
Combining Multiple Partitions into One ................................................................................ 2-31
Reconfiguring Partitions............................................................................................................ 2-32
Example Import Sessions ................................................................................................................ 2-33
Example Import of Selected Tables for a Specific User......................................................... 2-33
Example Import of Tables Exported by Another User ......................................................... 2-34
Example Import of Tables from One User to Another.......................................................... 2-35
Example Import Session Using Partition-Level Import........................................................ 2-35
viii
Using the Interactive Method......................................................................................................... 2-41
Importing Incremental, Cumulative, and Complete Export Files........................................... 2-43
Restoring a Set of Objects .......................................................................................................... 2-43
Importing Object Types and Foreign Function Libraries from an Incremental
Export File.................................................................................................................................... 2-44
Controlling Index Creation and Maintenance ............................................................................ 2-45
Index Creation and Maintenance Controls............................................................................. 2-45
Delaying Index Creation............................................................................................................ 2-46
Reducing Database Fragmentation ............................................................................................... 2-47

Importing Stored Procedures, Functions, and Packages ...................................................... 2-60
Importing Advanced Queue (AQ) Tables............................................................................... 2-61
Importing LONG Columns....................................................................................................... 2-61
Importing Views......................................................................................................................... 2-61
Generating Statistics on Imported Data....................................................................................... 2-62
Using Oracle7 Export Files.............................................................................................................. 2-62
Check Constraints on DATE Columns.................................................................................... 2-62
Using Oracle Version 6 Export Files.............................................................................................. 2-63
CHAR columns........................................................................................................................... 2-63
Syntax of Integrity Constraints................................................................................................. 2-63
Status of Integrity Constraints.................................................................................................. 2-63
Length of DEFAULT Column Values...................................................................................... 2-63
Using Oracle Version 5 Export Files.............................................................................................. 2-64
Part II: SQL Loader
3 SQL*Loader Concepts
SQL*Loader Basics ............................................................................................................................. 3-2
SQL*Loader Control File................................................................................................................... 3-3
Control File Contents and Storage............................................................................................. 3-4
Data Definition Language (DDL)............................................................................................... 3-5
Input Data and Datafiles ................................................................................................................... 3-6
Input Data Formats ...................................................................................................................... 3-6
Data Conversion and Datatype Specification ............................................................................. 3-10
Discarded and Rejected Records ................................................................................................... 3-13
The Bad File................................................................................................................................. 3-13
SQL*Loader Discards................................................................................................................. 3-15
Log File and Logging Information ................................................................................................ 3-15
Conventional Path Load versus Direct Path Load...................................................................... 3-16
Partitioned Object Support............................................................................................................. 3-17
x
4 SQL*Loader Case Studies

Log File......................................................................................................................................... 4-20
Loaded Tables ............................................................................................................................. 4-22
xi
Case 6: Loading Using the Direct Path Load Method................................................................ 4-24
Control File.................................................................................................................................. 4-24
Invoking SQL*Loader ................................................................................................................ 4-25
Log File......................................................................................................................................... 4-25
Case 7: Extracting Data from a Formatted Report....................................................................... 4-27
Data File ....................................................................................................................................... 4-27
Insert Trigger............................................................................................................................... 4-27
Control File.................................................................................................................................. 4-28
Invoking SQL*Loader ................................................................................................................ 4-30
Log File......................................................................................................................................... 4-30
Dropping the Insert Trigger and the Global-Variable Package........................................... 4-31
Case 8: Loading a Fixed Record Length Format File.................................................................. 4-32
Control File.................................................................................................................................. 4-32
Table Creation............................................................................................................................. 4-33
Input Data File ............................................................................................................................ 4-34
Invoking SQL*Loader ................................................................................................................ 4-34
Log File......................................................................................................................................... 4-34
5 SQL*Loader Control File Reference
Overview .............................................................................................................................................. 5-2
Data Definition Language (DDL) Syntax....................................................................................... 5-4
High-Level Syntax Diagrams...................................................................................................... 5-4
Expanded Clauses and Their Functionality................................................................................... 5-7
Position Specification...........................................................................................................5-7
Field Condition....................................................................................................................5-7
Column Name ....................................................................................................................5-8
Datatype Specification.........................................................................................................5-8
Precision vs. Length...........................................................................................................5-10

Limiting the Number of Discards ............................................................................................ 5-23
Handling Different Character Encoding Schemes..................................................................... 5-24
Multi-Byte (Asian) Character Sets............................................................................................ 5-24
Input Character Conversion...................................................................................................... 5-24
Loading into Empty and Non-Empty Tables ............................................................................... 5-25
How Non-Empty Tables are Affected ..................................................................................... 5-26
INSERT......................................................................................................................................... 5-26
APPEND ...................................................................................................................................... 5-26
REPLACE..................................................................................................................................... 5-26
TRUNCATE................................................................................................................................. 5-27
Specifying One Method for All Tables .................................................................................... 5-27
xiii
Continuing an Interrupted Load.................................................................................................... 5-27
State of Tables and Indexes....................................................................................................... 5-27
Using the Log File....................................................................................................................... 5-28
Dropping Indexes....................................................................................................................... 5-28
Continuing Single Table Loads ................................................................................................ 5-28
Continuing Multiple Table Conventional Loads................................................................... 5-28
Continuing Multiple Table Direct Loads ................................................................................ 5-28
Assembling Logical Records from Physical Records ................................................................ 5-29
Examples of How to Specify CONTINUEIF........................................................................... 5-32
Loading Logical Records into Tables ............................................................................................ 5-33
Specifying Table Names ............................................................................................................ 5-33
Table-Specific Loading Method................................................................................................ 5-34
Table-Specific OPTIONS keyword........................................................................................... 5-34
Choosing which Rows to Load................................................................................................. 5-34
Specifying Default Data Delimiters ......................................................................................... 5-35
Handling Short Records with Missing Data........................................................................... 5-35
Index Options.................................................................................................................................... 5-36
SORTED INDEXES Option ....................................................................................................... 5-36

Determining the Size of the Bind Array....................................................................................... 5-65
Minimum Requirements............................................................................................................ 5-65
Performance Implications.......................................................................................................... 5-66
Specifying Number of Rows vs. Size of Bind Array.............................................................. 5-66
Calculations ................................................................................................................................. 5-67
Minimizing Memory Requirements for the Bind Array....................................................... 5-70
Multiple INTO TABLE Statements .......................................................................................... 5-70
Generated Data ........................................................................................................................... 5-71
Setting a Column to Null or Zero .................................................................................................. 5-71
DEFAULTIF Clause.................................................................................................................... 5-71
NULLIF Keyword....................................................................................................................... 5-71
Null Columns at the End of a Record...................................................................................... 5-72
Loading All-Blank Fields ................................................................................................................ 5-72
Trimming Blanks and Tabs ............................................................................................................. 5-72
Datatypes ..................................................................................................................................... 5-73
Field Length Specifications........................................................................................................ 5-73
Relative Positioning of Fields.................................................................................................... 5-74
Leading Whitespace ................................................................................................................... 5-75
Trailing Whitespace.................................................................................................................... 5-76
Enclosed Fields............................................................................................................................ 5-77
Trimming Whitespace: Summary ............................................................................................ 5-77
Preserving Whitespace..................................................................................................................... 5-78
PRESERVE BLANKS Keyword ................................................................................................ 5-78
xv
Applying SQL Operators to Fields................................................................................................ 5-78
Referencing Fields ...................................................................................................................... 5-79
Referencing Fields That Are SQL*Loader Keywords............................................................ 5-80
Common Uses ............................................................................................................................. 5-80
Combinations of Operators....................................................................................................... 5-80
Use with Date Mask ................................................................................................................... 5-80

Global Information............................................................................................................................. 7-2
Table Information ............................................................................................................................... 7-3
Datafile Information........................................................................................................................... 7-4
Table Load Information ..................................................................................................................... 7-4
Summary Statistics ............................................................................................................................. 7-5
Oracle8 Statistics Reporting to the Log...................................................................................... 7-6
8 SQL*Loader: Conventional and Direct Path Loads
Data Loading Methods....................................................................................................................... 8-2
Conventional Path Load .............................................................................................................. 8-2
Direct Path Load ........................................................................................................................... 8-4
Using Direct Path Load...................................................................................................................... 8-9
Setting Up for Direct Path Loads................................................................................................ 8-9
Specifying a Direct Path Load..................................................................................................... 8-9
Building Indexes ........................................................................................................................... 8-9
Indexes Left in Index Unusable State....................................................................................... 8-11
Data Saves.................................................................................................................................... 8-12
Recovery....................................................................................................................................... 8-13
Loading LONG Data Fields....................................................................................................... 8-14
Maximizing Performance of Direct Path Loads.......................................................................... 8-15
Pre-allocating Storage for Faster Loading............................................................................... 8-15
Pre-sorting Data for Faster Indexing........................................................................................ 8-16
Infrequent Data Saves ................................................................................................................ 8-18
Minimizing Use of the Redo Log.............................................................................................. 8-18
Disable Archiving ....................................................................................................................... 8-18
Specifying UNRECOVERABLE................................................................................................ 8-18
NOLOG Attribute....................................................................................................................... 8-19
Avoiding Index Maintenance ......................................................................................................... 8-19
Direct Loads, Integrity Constraints, and Triggers ...................................................................... 8-20
Integrity Constraints .................................................................................................................. 8-20
Database Insert Triggers............................................................................................................ 8-21

DB_VERIFY ....................................................................................................................................... 10-2
Restrictions .................................................................................................................................. 10-2
Syntax........................................................................................................................................... 10-2
xviii
Enterprise Manager .................................................................................................................... 10-3
Sample DB_VERIFY Output ..................................................................................................... 10-4
SQL*Loader Extensions to the DB2 Load Utility.......................................................................... B-2
Using the DB2 RESUME Option ..................................................................................................... B-3
Inclusions for Compatibility ............................................................................................................ B-3
LOG Statement.............................................................................................................................. B-4
WORKDDN Statement ................................................................................................................ B-4
SORTDEVT and SORTNUM Statements .................................................................................. B-4
DISCARD Specification ............................................................................................................... B-4
Restrictions........................................................................................................................................... B-4
FORMAT Statement ..................................................................................................................... B-5
PART Statement............................................................................................................................ B-5
SQL/DS Option ............................................................................................................................ B-5
DBCS Graphic Strings .................................................................................................................. B-5
SQL*Loader Syntax with DB2-compatible Statements............................................................... B-5
Appendix A SQL*Loader Reserved Words
Appendix B DB2/DXT User Notes
xix
Send Us Your Comments
Oracle8 Utilities
, Release 8.0
Part No. A58244-01
Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of
this publication. Your input is an important part of the information used for revision.
■
Did you find any errors?

Phone Number:
Book Title:
Version Number:
■
If you like, you can use the following questionnaire to give us feedback. Edit the online
release notes file, extract a copy of this questionnaire, and send it to us.Did you find any
errors?
■
Is the information clearly presented?
■
Do you need more information? If so, where?
■
Are the examples correct? Do you need more examples?
■
What features did you like most about this manual?
If you find any errors or have any other suggestions for improvement, please indicate the chap-
ter, section, and page number (if available).
xxi
Preface
This manual describes how to use the Oracle8 Server utilities for data transfer,
maintenance, and database administration.
Oracle8 Utilities contains information that describes the features and functionality
of the Oracle8 and the Oracle8 Enterprise Edition products. Oracle8 and Oracle8
Enterprise Edition have the same basic features. However, several advanced fea-
tures are available only with the Enterprise Edition, and some of these are optional.
For information about the differences between Oracle8 and the Oracle8 Enterprise
Edition and the features and options that are available to you, see Getting to Know
Oracle8 and the Oracle8 Enterprise Edition.
xxii
The Oracle Utilities

Chapter 1, “Export”
This chapter describes how to use Export to write data from an Oracle database
into transportable files. It discusses guidelines, export modes, interactive and com-
mand-line methods, parameter specifications, and incremental exports. It also pro-
vides several examples of Export sessions.
Chapter 2, “Import”
This chapter shows you how to use Import to read data from Export files into an
Oracle database. It discusses guidelines, interactive and command-line methods,
parameter specifications, and incremental imports. It also provides several exam-
ples 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. 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, and direct file loads.
Chapter 5, “SQL*Loader Control File Reference”
This chapter describes the data definition language (DDL) used by SQL*Loader to
map data to Oracle format. It discusses creating the control file to hold DDL source,
using the LOAD DATA statement, specifying data files, specifying tables and col-
umns, and specifying the location of data.
Chapter 6, “SQL*Loader Command-Line Reference”
This chapter describes the command-line syntax used by SQL*Loader. It discusses
the SQLLOAD command, command-line arguments, suppressing SQL*Loader mes-
sages, and sizing the bind array.
xxiv

width font using the following conventions, separated from normal text as in the
following example:
ALTER TABLESPACE users ADD DATAFILE ’users2.ora’ SIZE 50K;
UPPERCASE
Words
Uppercase text is used to call attention to command key-
words, 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 defini-
tion 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.
Punctuation: , ’ ” Example statements may include punctuation such as
commas or quotation marks. All punctuation given in
example statements is required. All statement examples
end with a semicolon. 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 key-
words in Oracle SQL. However, when you issue state-
ments, keywords are not case-sensitive.