Learn the use of Oracle SQL Loader

Oracle
Learn the use of Oracle SQL Loader

One: Features of SQL loader
Oracle has a number of tools that can be used to migrate, backup, and restore data. But each tool has its own characteristics.
For example, exp and IMP can export and export data in the database, which is a good tool for database backup and recovery, so it is mainly used in the hot backup and recovery of the database. Has a fast, easy to use, quick advantages, but also have some drawbacks, such as in the different versions of the database between the export, import process, there will always be such or such a problem, this may be the Oracle Company's own product compatibility problem.
The SQL Loader tool does not have this problem, it can be in the text format of data stored in the smooth import into the Oracle database, is a data migration between different databases is a very convenient and common tool. The disadvantage is slow, in addition to BLOB and other types of data is a bit cumbersome.

II: Help for SQL Loader

C:\>sqlldr

Sql*loader:release 9.2.0.1.0-production on Saturday October 9 14:48:12 2004

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


Usage: Sqlldr keyword=value [, Keyword=value,...]

Valid keywords:

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 (all defaults)
Skip--Number of logical records to skip (default 0)
Load--Number of logical records to load (all defaults)
Errors--Number of errors to allow (default 50)
Rows--number of rows in conventional path bind array or between direct p
Ath Data saves
(Default: General path 64, all direct paths)
Bindsize--Size of conventional path bind array in bytes (default 256000)
Silent--Suppress messages during run (Header,feedback,errors,discards,part
Itions)
Direct-use direct path (default false)
Parfile--Parameter file:name of file that contains parameter specification
S
Parallel--Do parallel load (default false)
FILE--file to allocate extents from
Skip_unusable_indexes--Disallow/allow unusable indexes or index partitions (default
Recognize false)
Skip_index_maintenance-does not maintain indexes, mark affected indexes as Unus
Able (default false)
ReadSize--Size of Read buffer (default 1048576)
External_table-Use external table for load; not_used, Generate_only, EXECUTE (
Default not_used)
Columnarrayrows--Number of rows for direct path column array (Default 5000)
Streamsize--Size of direct path stream buffer in bytes (default 256000)
Multithreading--use multithreading in direct path
Resumable--Enable or disable resumable for the current session (default false)
Resumable_name--text string to help identify resumable statement
Resumable_timeout--wait time (in seconds) for resumable (default 7200)
Date_cache--size (in entries) of date conversion cache (default 1000)

Please: Command-Line arguments can be specified by position or keyword
。 The former example is ' Sqlload
Scott/tiger foo '; An example of the latter situation is ' Sqlldr Control=foo
Userid=scott/tiger '. The time of the specified parameter must be earlier than
But not later than the parameters specified by the keyword. For example
Allow ' Sqlldr scott/tiger control=foo logfile=log ', but
Do not allow ' Sqlldr scott/tiger control=foo log ', even if
The parameter ' log ' is in the correct position.

C:\>

Three: SQL Loader use example
A) Sqlloader export Excel data to Oracle
1. Create Sql*loader input data required by the file, are saved to C:\, edited in Notepad:
Control file: Input.ctl, the contents are as follows:

Load data--1, control file identification
InFile ' Test.txt '--2, the data file to enter is named Test.txt
Append into table Test--3, append records to table test
Fields terminated by X ' 09 '--4, field terminated by X ' 09 ', is a tab character (tab)
(ID,USERNAME,PASSWORD,SJ)-----Define column Correspondence order

A, insert, by default, requires the table to be empty at the beginning of the data load
b, append, append new records to the table
c, replace, delete old records, replace the new Loaded records
D, Truncate, ditto

Using the Sql*loader command to implement data entry in a DOS window

C:\>sqlldr Userid=system/manager Control=input.ctl
The default log file name is: Input.log
Default bad record file is: Input.bad

2. There is another way
You can save the Excel file as a CSV (comma-delimited) (*.csv), and the control file is separated by a comma
LOAD DATA
INFILE ' D:\car.csv '
APPEND into TABLE t_car_temp
FIELDS terminated by ","
(Phoneno,vip_car)

(b) Direct import of data into the control file

1, the control document TEST.CTL content
--The format for executing this file and SQL Loader is:
--Sqlldr control=<filename> is sure to substitute your
--version of SQL LOADER and the filename for this file.
LOAD DATA
INFILE *
Badfile ' C:\Documents and settings\jackey\ desktop \wmcountry. Bad '
Discardfile ' C:\Documents and settings\jackey\ desktop \wmcountry. DSC '
INSERT into TABLE emccountry
Fields terminated by ";" Optionally enclosed by ' "'
(
Countryid Nullif (countryid= "NULL"),
CountryCode,
CountryName,
Continentid Nullif (continentid= "NULL"),
MAPID Nullif (mapid= "NULL"),
Createtime DATE "mm/dd/yyyy HH24:MI:SS" Nullif (createtime= "NULL"),
Lastmodifiedtime DATE "mm/dd/yyyy HH24:MI:SS" Nullif (lastmodifiedtime= "NULL")
)
Begindata
1; " JP ";" Japan "; 1;9;" 09/16/2004 16:31:32 "; Null
2; " CN ";" "1;10;" 09/16/2004 16:31:32 "; Null
3; " In ";" India "; 1;11;" 09/16/2004 16:31:32 "; Null
4; " AU ";" Australia "; 6;12;" 09/16/2004 16:31:32 "; Null
5; " CA ";" Canada "; 4;13;" 09/16/2004 16:31:32 "; Null
6; " US ";" United States "; 4;14;" 09/16/2004 16:31:32 "; Null
7; " MX ";" Mexico "; 4;15;" 09/16/2004 16:31:32 "; Null
8; " GB ";" United kingdom "; 3;16;" 09/16/2004 16:31:32 "; Null
9; " DE ";" Germany "; 3;17;" 09/16/2004 16:31:32 "; Null
10; " FR ";" France "; 3;18;" 09/16/2004 16:31:32 "; Null
11; " IT ";" Italy "; 3;19;" 09/16/2004 16:31:32 "; Null
12; " ES ";" Spain "; 3;20;" 09/16/2004 16:31:32 "; Null
13; " FI ";" Finland "; 3;21;" 09/16/2004 16:31:32 "; Null
14; " SE ";" Sweden "; 3;22;" 09/16/2004 16:31:32 "; Null
15; " IE ";" Ireland "; 3;23;" 09/16/2004 16:31:32 "; Null
16; " NL ";" Netherlands "; 3;24;" 09/16/2004 16:31:32 "; Null
17; " DK ";" Denmark "; 3;25;" 09/16/2004 16:31:32 "; Null
18; " BR ";" Brazil "; 5;85;" 09/30/2004 11:25:43 "; Null
19; " KR ";" Korea, Republic of "; 1;88;" 09/30/2004 11:25:43 "; Null
20; " NZ ";" New Zealand "; 6;89;" 09/30/2004 11:25:43 "; Null
21; " Be ";" Belgium "; 3;79;" 09/30/2004 11:25:43 "; Null
22; " At ";" Austria "; 3;78;" 09/30/2004 11:25:43 "; Null
23; " NO ";" Norway "; 3;82;" 09/30/2004 11:25:43 "; Null
24; " LU ";" Luxembourg "; 3;81;" 09/30/2004 11:25:43 "; Null
25; " PT ";" Portugal "; 3;83;" 09/30/2004 11:25:43 "; Null
26; " GR ";" Greece "; 3;80;" 09/30/2004 11:25:43 "; Null
27; " IL ";" Israel "; 1;86;" 09/30/2004 11:25:43 "; Null
28; " CH ";" Switzerland "; 3;84;" 09/30/2004 11:25:43 "; Null
29; " A1 ";" Anonymous Proxy "; 0;0;" 09/30/2004 11:25:43 "; Null
30; " A2 ";" Satellite Provider "; 0;0;" 09/30/2004 11:25:43 "; Null
31; " AD ";" Andorra "; 3;0;" 09/30/2004 11:25:43 "; Null
32; " AE ";" United Arab Emirates "; 1;0;" 09/30/2004 11:25:43 "; Null
33; " AF ";" Afghanistan "; 1;0;" 09/30/2004 11:25:43 "; Null
34; " AG ";" Antigua and Barbuda "; 7;0;" 09/30/2004 11:25:43 "; Null
35; " AI ";" Anguilla "; 7;0;" 09/30/2004 11:25:43 "; Null
36; " AL ";" Albania "; 3;0;" 09/30/2004 11:25:43 "; Null
37; " AM ";" Armenia "; 3;0;" 09/30/2004 11:25:43 "; Null
38; " an ";" Netherlands Antilles "; 3;0;" 09/30/2004 11:25:43 "; Null
39; " AO ";" Angola "; 2;0;" 09/30/2004 11:25:43 "; Null
40; " AP ";" Asia/pacific Region "; 2;0;" 09/30/2004 11:25:43 "; Null
41; " AQ ";" Antarctica "; 8;0;" 09/30/2004 11:25:43 "; Null
42; " AR ";" Argentina "; 5;0;" 09/30/2004 11:25:43 "; Null
43; " as ";" American Samoa "; 6;0;" 09/30/2004 11:25:43 "; Null
44; " AW ";" Aruba "; 5;0;" 09/30/2004 11:25:43 "; Null
45; " AZ ";" Azerbaijan "; 1;0;" 09/30/2004 11:25:43 "; Null
46; " BA ";" Bosnia and Herzegovina "; 3;0;" 09/30/2004 11:25:43 "; Null
47; " BB ";" Barbados "; 5;0;" 09/30/2004 11:25:43 "; Null
48; " BD ";" Bangladesh "; 1;0;" 09/30/2004 11:25:43 "; Null
49; " BF ";" Burkina Faso "; 2;0;" 09/30/2004 11:25:43 "; Null
50; " BG ";" Bulgaria "; 3;0;" 09/30/2004 11:25:43 "; Null
51; " BH ";" Bahrain "; 1;0;" 09/30/2004 11:25:43 "; Null
52; " BI ";" Burundi "; 2;0;" 09/30/2004 11:25:43 "; Null
53; " BJ ";" Benin "; 2;0;" 09/30/2004 11:25:43 "; Null
54; " BM ";" Bermuda "; 4;0;" 09/30/2004 11:25:43 "; Null
55; " BN ";" Brunei Darussalam "; 1;0;" 09/30/2004 11:25:43 "; Null
56; " BO ";" Bolivia "; 5;0;" 09/30/2004 11:25:43 "; Null
57; " BS ";" Bahamas "; 7;0;" 09/30/2004 11:25:43 "; Null
58; " BT ";" Bhutan "; 1;0;" 09/30/2004 11:25:43 "; Null
59; " BV ";" Bouvet Island "; 5;0;" 09/30/2004 11:25:43 "; Null
60; " BW ";" Botswana "; 2;0;" 09/30/2004 11:25:43 "; Null
61; " By ";" Belarus "; 3;0;" 09/30/2004 11:25:43 "; Null
2. Execute Import command
C:\>sqlldr Userid=system/manager Control=test.ctl

C. Import of complex formats