SQL*Loader之CASE8
CASE8
1. SQL脚本
[oracle@node3 ulcase]$ cat ulcase8.sql
set termout off rem host write sys$output "Building case 8 demonstration tables. Please wait" drop table lineitem; create table lineitem (l_orderkey number, l_partkey number, l_suppkey number, l_linenumber number, l_quantity number, l_extendedprice number, l_discount number, l_tax number, l_returnflag char, l_linestatus char, l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(17), l_shipmode char(7), l_comment char(43)) partition by range (l_shipdate) ( partition ship_q1 values less than (TO_DATE('01-APR-1996', 'DD-MON-YYYY')), partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DD-MON-YYYY')), partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DD-MON-YYYY')), partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DD-MON-YYYY')) ); exit
2. 控制文件
[oracle@node3 ulcase]$ cat ulcase8.ctl
-- Copyright (c) 1991, 2004 Oracle. All rights reserved. -- NAME -- ulcase8.ctl - SQL*Loader Case Study 8: Loading Partitioned Tables -- -- DESCRIPTION -- This case study demonstrates the following: -- Partitioning of data. -- -- Explicitly defined field positions and datatypes. -- -- Loading data using the fixed-record-length option. -- -- TO RUN THIS CASE STUDY: -- 1. Before executing this control file, log in to SQL*Plus as -- scott/tiger. Enter @ulcase8 to execute the SQL script for -- this case study. This prepares and populates tables and -- then returns you to the system prompt. -- -- 2. At the system prompt, invoke the case study as follows: -- sqlldr USERID=scott/tiger CONTROL=ulcase8.ctl LOG=ulcase8.log -- -- NOTES ABOUT THIS CONTROL FILE -- This control file loads the lineitem table with fixed-length -- records, partitioning the data according to shipment date. -- -- The INFILE clause specifies that each record in the datafile is -- of fixed length (129 bytes in this example). -- -- The PARTITION clause identifies the column name and location of the -- data in the datafile to be loaded into each column. -- LOAD DATA INFILE 'ulcase8.dat' "fix 129" BADFILE 'ulcase8.bad' TRUNCATE INTO TABLE lineitem PARTITION (ship_q1) (l_orderkey position (1:6) char, l_partkey position (7:11) char, l_suppkey position (12:15) char, l_linenumber position (16:16) char, l_quantity position (17:18) char, l_extendedprice position (19:26) char, l_discount position (27:29) char, l_tax position (30:32) char, l_returnflag position (33:33) char, l_linestatus position (34:34) char, l_shipdate position (35:43) char, l_commitdate position (44:52) char, l_receiptdate position (53:61) char, l_shipinstruct position (62:78) char, l_shipmode position (79:85) char, l_comment position (86:128) char)
3. 数据文件
[oracle@node3 ulcase]$ cat ulcase8.dat
1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN PERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP 1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN MAIL 5wM04SNyl0AnghCP2nx lAi 1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN REG AIRSQC2C 5PNCy4mM 1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE AIR Om0L65CSAwSj5k6k 1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN PERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh 1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE FOB C2gOQj OB6RLk1BS15 igN 2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD AIR O52M70MRgRNnmm476mNm 3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN FOB 6wQnO0Llg6y 3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN SHIP LhiA7wygz0k4g4zRhMLBAM 3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297
执行后结果：
[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase8.sql
[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase8.ctl
SQL> select L_ORDERKEY,L_PARTKEY,L_SHIPDATE from lineitem;
L_ORDERKEY L_PARTKEY L_SHIPDAT
---------- ---------- ---------
1 1519 09-SEP-64
1 2731 12-FEB-96
1 3370 29-MAR-96
--因为列数太多，在这里我们只选取三列，可见只有三条记录被插入。
查看日志文件：
[oracle@node3 ulcase]$ cat ulcase8.log
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 03:30:54 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Control File: ulcase8.ctl Data File: ulcase8.dat File processing option string: "fix 129" Bad File: ulcase8.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 LINEITEM, partition SHIP_Q1, loaded from every logical record. Insert option in effect for this partition: TRUNCATE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- L_ORDERKEY 1:6 6 CHARACTER L_PARTKEY 7:11 5 CHARACTER L_SUPPKEY 12:15 4 CHARACTER L_LINENUMBER 16:16 1 CHARACTER L_QUANTITY 17:18 2 CHARACTER L_EXTENDEDPRICE 19:26 8 CHARACTER L_DISCOUNT 27:29 3 CHARACTER L_TAX 30:32 3 CHARACTER L_RETURNFLAG 33:33 1 CHARACTER L_LINESTATUS 34:34 1 CHARACTER L_SHIPDATE 35:43 9 CHARACTER L_COMMITDATE 44:52 9 CHARACTER L_RECEIPTDATE 53:61 9 CHARACTER L_SHIPINSTRUCT 62:78 17 CHARACTER L_SHIPMODE 79:85 7 CHARACTER L_COMMENT 86:128 43 CHARACTER Record 4: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 5: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 6: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 7: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 8: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 9: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 10: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Table LINEITEM, partition SHIP_Q1: 3 Rows successfully loaded. 7 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: 11008 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 10 Total logical records rejected: 7 Total logical records discarded: 0 Run began on Fri Sep 19 03:30:54 2014 Run ended on Fri Sep 19 03:30:54 2014 Elapsed time was: 00:00:00.31 CPU time was: 00:00:00.03
总结： 在本例中
1> PARTITION (ship_q1)子句表明只有符合这个分区范围内的值才能被插入，即l_shipdate小于01-APR-1996的数据可被插入。
