Oracle 11g 数据装载的几种方式

数据的装载:

SQL*LOADER
外部表
导入/导出

SQL*LOADER:SQL*LOADER是一个Oracle工具，能够将数据从外部数据文件装载到数据库中。运行SQL*LOADER的命令是sqlldr。Sqlldr的两种使用方式：1. 只使用一个控制文件，在这个控制文件中包含数据2. 使用一个控制文件（作为模板）和一个数据文件一般采用第二种方式，数据文件可以是 CSV 文件、txt文件或者以其他分割符分隔的。说明：操作类型可用以下中的一值：1） insert --为缺省方式，在数据装载开始时要求表为空2） append --在表中追加新记录3） replace --删除旧记录（用 delete from table 语句），替换成新装载的记录4） truncate --删除旧记录（用 truncate table 语句），替换成新装载的记录通过spool来制作数据文件：--可以查询帮助文档的示例代码SQL> spool /u01/app/oracle/test_data_loader/student.txt--开启spool导出数据文件
SQL> select id ||"," || name ||"," || age ||"," || inner_date from student;--导出数据ID||","||NAME||","||AGE||","||INNER_DATE
--------------------------------------------------------------------------------
1,zhangsan,21,23-JAN-15
2,lisi,22,23-JAN-15
3,wangwu,23,23-JAN-15SQL> spool off;--关闭
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost test_data_loader]$ cat student.txt--可以查看到导出的数据记录
SQL> select id ||"," || name ||"," || age ||"," || inner_date from student;ID||","||NAME||","||AGE||","||INNER_DATE
--------------------------------------------------------------------------------
1,zhangsan,21,23-JAN-15
2,lisi,22,23-JAN-15
3,wangwu,23,23-JAN-15SQL> spool off;写配置文件：
[oracle@localhost test_data_loader]$ vi student.ctl
[oracle@localhost test_data_loader]$ cat student.ctl
options（skip=4）--表示前面的四行
load data--导入数据
infile "student.txt"--通过该文件导入数据
into table student--导入的表
insert--执行的是插入操作
fields terminated by ","--记录中的分割符
（
id char,--注意虽然表中是number类型，但是要写char类型
name char,
age char,
inner_date date nullif （inner_date = "null"））
[oracle@localhost test_data_loader]$既然是insert操作所以：
SQL> truncate table student;--清空表，由于执行的是插入操作Table truncated.SQL> select * from student;no rows selected执行sqlldr操作：
[oracle@localhost test_data_loader]$ sqlldr hr/hr control= student.ctl log = student.logSQL*Loader: Release 11.2.0.1.0 - Production on Fri Jan 23 23:11:08 2015Copyright （c） 1982, 2009, Oracle and/or its affiliates. All rights reserved.Commit point reached - logical record count 4
[oracle@localhost test_data_loader]$ cat student.logSQL*Loader: Release 11.2.0.1.0 - Production on Fri Jan 23 23:11:08 2015Copyright （c） 1982, 2009, Oracle and/or its affiliates. All rights reserved.Control File: student.ctl
Data File: student.txt
Bad File: student.bad
Discard File: none specified（Allow all discards）Number to load: ALL
Number to skip: 4
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: ConventionalTable STUDENT, loaded from every logical record.
Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
AGE NEXT * , CHARACTER
INNER_DATE NEXT * , DATE DD-MON-RR
NULL if INNER_DATE = 0X6e756c6c（character "null"）Record 4: Rejected - Error on table STUDENT, column ID.
Column not found before end of logical record （use TRAILING NULLCOLS）Table STUDENT:
3 Rows successfully loaded.
1 Row 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: 66048 bytes（64 rows）
Read buffer bytes: 1048576Total logical records skipped: 4
Total logical records read: 4
Total logical records rejected: 1
Total logical records discarded: 0Run began on Fri Jan 23 23:11:08 2015
Run ended on Fri Jan 23 23:11:08 2015Elapsed time was: 00:00:00.10
CPU time was: 00:00:00.01
[oracle@localhost test_data_loader]$ sqlplus hr/hrSQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 23 23:13:14 2015Copyright （c） 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from student; ID NAME AGE INNER_DATE
---------- -------------------- ---------- ------------
1 zhangsan 21 23-JAN-15
2 lisi 22 23-JAN-15
3 wangwu 23 23-JAN-15SQL> exit 外部表：
外部表中的数据不装入数据库中，数据库中只存储外部表的定义信息，实际的数据位于操作系统的平面文件中，
但是，可以在数据库中，像访问正常表那样，通过select语句来访问操作系统中的平面文件中所含有的数据。
外部表是只读的。
可以使用SQL，PL/SQL和JAVA访问外部表。
外表表分为2种：使用数据泵引擎生成的外部表、SQL> create table student_re（re_id,re_name,re_age,re_inner_date）
organization external
（
type oracle_datapump--外部表生成方式是数据泵
default directory test_impdp_expdp--默认的路径
location（"student.dmp"）--通过路径和文件名生成外部表在操作系统的平面数据文件
）
parallel--并行
as
select * from student; 2 3 4 5 6 7 8 9 10Table created.SQL> ！ls
student.dmp STUENT_17109.log
SQL> select * from stuent; RE_ID RE_NAME RE_AGE RE_INNER_DAT
---------- -------------------- ---------- ------------
1 zhangsan 21 23-JAN-15
2 lisi 22 23-JAN-15
3 wangwu 23 23-JAN-15SQL>根据文本文件创建的外部表。
SQL> spool student.txtID||","||NAME||","||AGE||","||INNER_DATE
--------------------------------------------------------------------------------
1,zhangsan,21,23-JAN-15
2,lisi,22,23-JAN-15
3,wangwu,23,23-JAN-15SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost test_impdp_expdp]$ vi student.txt
[oracle@localhost test_impdp_expdp]$ sqlplus hr/hrSQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 24 00:05:18 2015Copyright （c） 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> create table student_re_2--该外部表的名称下面列是外部表的建表语句，而上面通过数据泵的表是通过as select from tab来建立的。
2 （id number,name varchar2（20）,age number（10）,inner_date date）
3 organization external
4 （ type oracle_loader--外部文件方式
5 default directory test_impdp_expdp--默认的目录
6 access parameters--访问外部文件所遵守的格式
7 （
records delimited by newline--记录分隔符
fields terminated by ","--字段分隔符
8 ） location （"student.txt"）--位置
）; 9 10 11Table created.SQL> select * from student_re_2; ID NAME AGE INNER_DATE
---------- -------------------- ---------- ------------
1 zhangsan 21 23-JAN-15
2 lisi 22 23-JAN-15
3 wangwu 23 23-JAN-15SQL>更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址