數據的裝載:
•SQL*LOADER
•外部表
•導入/導出
說明：操作類型 可用以下中的一值：
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-15 SQL> 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-15 SQL> 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.log SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jan 23 23:11:08 2015 Copyright (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.log SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jan 23 23:11:08 2015 Copyright (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: Conventional Table 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: 1048576 Total logical records skipped: 4 Total logical records read: 4 Total logical records rejected: 1 Total logical records discarded: 0 Run began on Fri Jan 23 23:11:08 2015 Run ended on Fri Jan 23 23:11:08 2015 Elapsed time was: 00:00:00.10 CPU time was: 00:00:00.01 [oracle@localhost test_data_loader]$ sqlplus hr/hr SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 23 23:13:14 2015 Copyright (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 options SQL> 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-15 SQL> exit
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 10
Table 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-15
SQL>
SQL> spool student.txt
ID||','||NAME||','||AGE||','||INNER_DATE
--------------------------------------------------------------------------------
1,zhangsan,21,23-JAN-15
2,lisi,22,23-JAN-15
3,wangwu,23,23-JAN-15
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_impdp_expdp]$ vi student.txt
[oracle@localhost test_impdp_expdp]$ sqlplus hr/hr
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 24 00:05:18 2015
Copyright (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 options
SQL> 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 11
Table 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-15
SQL>
