Oracle 9i SQL Loader学习指南：英文文档与实战教程

需积分: 9 127 浏览量更新于2024-07-23 收藏 3.98MB PDF 举报

SQL Loader是Oracle数据库管理工具的一部分，它专用于高效地将数据从外部文件（如文本文件、固定宽度或CSV格式）导入到Oracle数据库中。在Oracle9i Database Utilities Release 2 (9.2) 的文档中，这份学习资料由Kathy Rich主笔，同时汇集了多位Oracle专家如Lee Barton、Ellen Batbouta等人的贡献。该文档发布日期为2002年3月，版权归属Oracle Corporation。 SQL Loader是一个实用程序，其主要功能包括但不限于： 1. **数据装载工具**：它允许用户通过命令行界面或批处理脚本执行数据导入任务，支持一次性装载大量数据，提高数据迁移和集成的效率。 2. **控制文件格式**：SQL Loader使用控制文件来定义数据源的结构、目标表的字段映射以及装载过程中的规则，这提供了灵活性和定制性。 3. **错误处理与验证**：它具备内置的错误检查机制，可以在装载过程中检测并处理数据质量问题，确保数据的准确性。 4. **性能优化**：SQL Loader设计有多种装载选项和参数，允许用户根据系统资源调整装载策略，如并发装载、缓冲区管理和内存管理。 5. **数据类型转换**：支持不同数据源的数据类型转换，使得非标准格式的数据可以无缝导入Oracle环境。 6. **安全性**：遵循Oracle的安全模型，SQL Loader在装载过程中可以进行权限控制，确保数据的保密性和完整性。 7. **版本兼容性**：文档强调，尽管提供给读者的是Oracle 9i的工具，但SQL Loader的概念和使用方法在后续版本中也得到了延续和改进，因此这份文档对于后续版本的学习也有参考价值。 8. **法律限制**：文档明确指出，Oracle Programs（软件和文档）包含专有信息，受许可证协议约束，禁止未经许可的反向工程、拆解或逆向编译，除非出于获得与其他独立软件互操作性的必要，或者法律规定。学习SQL Loader不仅是理解Oracle数据库数据迁移的重要环节，而且是掌握数据库管理工具链中关键一环。通过阅读这份英文版的学习文档，用户可以深入了解SQL Loader的工作原理，掌握如何高效、准确地处理大规模数据导入，并了解如何在实际项目中应用和优化SQL Loader的使用。

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 Speciﬁcations.................................................................................. 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

Dataﬁle 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

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

剩余599页未读，继续阅读

perferlife

粉丝: 11
资源: 7

Oracle 9i SQL Loader学习指南：英文文档与实战教程

SQL_LOADER教程

sql loader详解

SQL*Loader详细介绍

SQL loader and oracle sql study

如何使用SQLloader导入数据(word文档良心出品).doc

SQLLoader实现SQL语句的外部存储与开源应用

SQL＊Loader.zip

sqlload 文档

基于SQL＊Loader的海量数据装载方案优化.pdf

PyPI 官网下载 | pd_sql_loader-1.0.tar.gz

最新资源