ITPub博客

首页 > 数据库 > Oracle > Using SQL-Loader to load data into Oracle

Using SQL-Loader to load data into Oracle

原创 Oracle 作者:YoungEric 时间:2007-09-19 12:46:18 0 删除 编辑
p.s http://www.phpchina.com/78/viewspace_14655.html[@more@]

Oracle Resources

Using SQL-Loader to load data into Oracle

The Oracle system in the labs has a software program called SQL-Loader that can be used to load large amounts of data into Oracle. This guide describes the command line version which is run from DOS. Oracle also has a Windows interface to this program. If you chose to use the Windows interface then the last step in this guide (Run SQL-Loader) will be different.

There are four stages to loading data using SQL-Loader:

  1. Create a data file The data file contains the data that you wish to load. There is one record per line and each attribute value is separated by a comma.
  2. Create the relation for the data
  3. Create a control file The control file tells Oracle how to load data from the data file.
  4. Run SQL-Loader SQL-Loader reads the control file and loads the data. SQL-Loader creates a number of files as it loads the data. A log file is produced that describes what happened and describes any errors that may have occurred.

Documentation

There is a lot of documentation for Oracle online. SQL-Loader is described in the Oracle8i Utilities User's Guide. The example used below is based on one of the case studies described in the guide book. There are a number of other examples and you should read them all. Before going any further, please read the documentation.

Example

1. Create a data file

The data file is a text file that contains the data. Create a text file by running the text editor and typing the following data:

12, Research, "Saratoga"
10, "Accounting", Cleveland
11, "Art", Salem
13, Finance, Boston
21, Sales, Phila
22, Sales, Rochester
42, "Int'l", "San Fran"

Save the file as test.dat in your h:.

2. Create the relation

Create a relation testdept in your Oracle account using the following command:

CREATE TABLE testdept
(deptno NUMBER(2) NOT NULL,
 dname VARCHAR2(14),
 loc VARCHAR2(13));

The data in h: est.dat will be loaded into the relation testdept using SQL-Loader. The relation testdept must exist in the database before SQL-Loader can load data into it.

3. Create a control file

The control file describes the structure of the data and indicates the relation into which the data should be loaded. Create a text file containing the following:

LOAD DATA
INFILE 'h:	est.dat'
INTO TABLE testdept
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(deptno, dname, loc)

Save the file as test.ctl in your h:.

This file instructs SQL-Loader to load the data from the data file h: est.dat.txt into the relation testdept. It also states that each line or record in the file contains three attributes corresponding to the attributes deptno, dname and loc in the relation testdept. The attribute names must be in the same order in the control file as the attribute values are in the data file. The attribute values in the data file are separated by commas and may be enclosed in double quotation marks.

This example puts the data in a separate file to the control information. The documentation shows examples of case studies where the control information and the data are held in the one file. It is easier to generate data when the data and control information are stored in separate files. The double quotes are necessary because some data may contain commas as part of the actual data. If data that contains commas is enclosed in double quotes then the commas will be ignored when processed by SQL-Loader. Although it is possible to use other delimiters and quotes, it is best to enclose all strings in double quotes and separate all attributes by commas.

4. Run SQL-Loader

Start a DOS window and change to the directory where you stored the control and data files (e.g. h:). The command to execute the SQL-Loader is:

sqlldr userid=u9700000@dec1 control=h:	est.ctl log=h:	est.log

Type this command on one line. It runs the SQL-Loader program, logs into dec1 as the given user (u9700000) and asks for a password. The command also uses the control file test.ctl and writes a log file called test.log. If sqldr fails to run in DOS you will have to find its location in the Windows file explorer and run the command using the full path name (e.g. c:oracle...insqldr.exe). You must replace the username u9700000 with your Oracle username.

When the SQL-Loader finishes it will have created a log file, for example, the file h: est.log contains:

SQL*Loader: Release 10.1.0.2.0

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Control File:   test.ctl
Data File:      test.dat
  Bad File:     test.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TESTDEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name    Position   Len  Term Encl Datatype
---------------- ---------- ----- ---- ---- ---------
DEPTNO              FIRST     *   ,    O(") CHARACTER            
DNAME                NEXT     *   ,    O(") CHARACTER            
LOC                  NEXT     *   ,    O(") CHARACTER            


Table TESTDEPT:
  7 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:   49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             7
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Mon Nov 28 15:29:43 2005
Run ended on Mon Nov 28 15:29:50 2005

Elapsed time was:     00:00:06.42
CPU time was:         00:00:00.04

This log file says that 7 records have been loaded into the testdept relation and there where no records rejected as incorrect. A record is incorrect if it is missing values or an integrity constraint is broken (e.g. non-unique primary keys, etc).

If some records are rejected they are normally placed in a file with a .bad extension (e.g. test.bad). If, after loading a lot of records, the log file indicates there are a few records with errors it is normally easier to ignore the errors than to try and find them.

Additional Control File Options

The example control file loads data into an empty relation. If data is to be appended to existing data then the control file must instruct SQL-Loader to append the new data to the existing relation:

LOAD DATA
INFILE 'h:	est.dat'
APPEND INTO TABLE testdept
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(deptno, dname, loc)

The documentation explains these options in detail.


一:sql loader 的特点
oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。
比如说exp和imp可以对数据库中的数据进行导出和导出的工作,是一种很好的数据库备份和恢复的工具,因此主要用在数据库的热备份和恢复方面。有着速度快,使用简单,快捷的优点;同时也有一些缺点,比如在不同版本数据库之间的导出、导入的过程之中,总会出现这样或者那样的问题,这个也许是oracle公司自己产品的兼容性的问题吧。
sql loader 工具却没有这方面的问题,它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中,是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢,另外对blob等类型的数据就有点麻烦了。

二:sql loader 的帮助

C:>sqlldr

SQL*Loader: Release 9.2.0.1.0 - Production on 星期六 10月 9 14:48:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


用法: SQLLDR keyword=value [,keyword=value,...]

有效的关键字:

userid -- ORACLE username/password
control -- Control file name
log -- Log file name
bad -- Bad file name
data -- Data file name
discard -- Discard file name
discardmax -- Number of discards to allow (全部默认)
skip -- Number of logical records to skip (默认0)
load -- Number of logical records to load (全部默认)
errors -- Number of errors to allow (默认50)
rows -- Number of rows in conventional path bind array or between direct p
ath data saves
(默认: 常规路径 64, 所有直接路径)
bindsize -- Size of conventional path bind array in bytes(默认256000)
silent -- Suppress messages during run (header,feedback,errors,discards,part
itions)
direct -- use direct path (默认FALSE)
parfile -- parameter file: name of file that contains parameter specification
s
parallel -- do parallel load (默认FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默
认FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus
able(默认FALSE)
readsize -- Size of Read buffer (默认1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(
默认NOT_USED)
columnarrayrows -- Number of rows for direct path column array(默认5000)
streamsize -- Size of direct path stream buffer in bytes(默认256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session(默认FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE(默认7200)
date_cache -- size (in entries) of date conversion cache(默认1000)

PLEASE NOTE: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlload
scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
userid=scott/tiger'.位置指定参数的时间必须早于
但不可迟于由关键字指定的参数。例如,
允许 'sqlldr scott/tiger control=foo logfile=log', 但是
不允许 'sqlldr scott/tiger control=foo log', 即使
参数 'log' 的位置正确。

C:>

三:sql loader使用例子
a)SQLLoader将 Excel 数据导出到 Oracle
1.创建SQL*Loader输入数据所需要的文件,均保存到C:,用记事本编辑:
控制文件:input.ctl,内容如下:

  load data           --1、控制文件标识
  infile 'test.txt'       --2、要输入的数据文件名为test.txt
  append into table test    --3、向表test中追加记录
  fields terminated by X'09'  --4、字段终止于X'09',是一个制表符(TAB)
  (id,username,password,sj)   -----定义列对应顺序

a、insert,为缺省方式,在数据装载开始时要求表为空
b、append,在表中追加新记录
c、replace

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10484922/viewspace-970082/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-18

  • 博文量
    79
  • 访问量
    211181