Oracle SQL * Loader User Guide (Part 2)

This article is followed by Oracle SQL * Loader User Guide (Part 1)
5. Set the row end flag (STR), which is useful when importing large objects!
LOAD DATA
INFILE 'C: \ data \ mydata.csv '"str '*****'"
Replace into table book
Fields terminated by "," optionally enclosed '"'
(
Book_title,
Book_price,
Book_pages,
Book_id
)
Mydata.csv file content:
Oracle Essentials bbbbbbbbbbbbbbb
, 3495,355, 1 *****
SQL * Plus: The Definitive
Guide, 3995,502, 2 *****
Oracle PL/SQL
Programming, 4495, 87, 3 *****
Oracle8 Design Tips, 1495,115, 4 *****
Example:
SQL> truncate table book;

The table is truncated.

SQL> host sqlldr hr/hr control = c: \ data \ loader. ctl log = c: \ data \ log. log
SQL * Loader: Release 10.2.0.1.0-Production on Thursday August 14 10:16:49 2008
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Submission point reached-logic record count 4
SQL> select * from book;
BOOK_ID BOOK_TITLE BOOK_PRICE BOOK_PAGES
-----------------------------------------------------------------
1 Oracle essenessenbbbbbbbbbbbbbbb 3495 355
2 SQL * Plus: The Definitive 3995 502
3 Oracle PL/SQL 4495 87
4 Oracle8 Design Tips 1495 115

6. load multiple flat files to the database at the same time.
LOAD DATA
INFILE 'C: \ data \ mydata.csv '"str '*****'"
INFILE 'C: \ data \ mydata2.csv '"str '*****'"
Replace into table book
Fields terminated by "," optionally enclosed '"'
(
Book_title,
Book_price,
Book_pages,
Book_id
)
The contents of Mydata.csv are as follows:
Oracle Essentials bbbbbbbbbbbbbbb
, 3495,355, 1 *****
SQL * Plus: The Definitive
Guide, 3995,502, 2 *****
Oracle PL/SQL
Programming, 4495, 87, 3 *****
Oracle8 Design Tips, 1495,115, 4 *****
The content of Mydata2.csv data is as follows:
New Concept 1
, 3495,355, 1 *****
New Concept 2
, 3995,502, 2 *****
New Concept 3
, 4495, 87, 3 *****
New Concept 4, 1495,115, 4 *****
Example:
SQL> truncate table book;
The table is truncated.
SQL> host sqlldr hr/hr control = c: \ data \ loader. ctl log = c: \ data \ log. log
SQL * Loader: Release 10.2.0.1.0-Production on Thursday August 14 10:32:55 2008
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Submission point reached-logic record count 4
Submission point reached-logic record count 8
SQL> select * from book;
BOOK_ID BOOK_TITLE BOOK_PRICE BOOK_PAGES
-----------------------------------------------------------------
1 Oracle essenessenbbbbbbbbbbbbbbb 3495 355
2 SQL * Plus: The Definitive 3995 502
3 Oracle PL/SQL 4495 87
4 Oracle8 Design Tips 1495 115
1 New Concept 1 3495 355
2 new concept 2 3995 502
3 New Concept 3 4495 87
4 New Concept 4 1495 115
 
Eight rows have been selected.
7. If the file name contains special characters, you can use the Escape Character "\".
Example:
INFILE 'mi _ \ 'desc' actual file name: mi _ 'deci
INFILE "mi _ \" deci 'actual file name: mi _ "deci
INFILE "mi _ \ deci 'actual file name: mi _ \ deci
8. load data to table partitions:
LOAD DATA
INFILE 'C: \ data \ mydata.csv '"str '*****'"
Replace into table book PARTITION (p1)
Fields terminated by "," optionally enclosed '"'
(
Book_title,
Book_price,
Book_pages,
Book_id
)
Example:
Create table BOOK
(
BOOK_ID NUMBER,
BOOK_TITLE VARCHAR2 (35 ),
BOOK_PRICE NUMBER,
BOOK_PAGES NUMBER
)
Partition by range (book_id )(
Partition p1 values less than (10 ),
Partition p2 values less than (20 ))
 
SQL> host sqlldr hr/hr control = c: \ data \ loader. ctl log = c: \ data \ log. log
SQL * Loader: Release 10.2.0.1.0-Production on Thursday August 14 11:01:13 2008
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Submission point reached-logic record count 4
SQL> select * from book partition (p1 );
BOOK_ID BOOK_TITLE BOOK_PRICE BOOK_PAGES
-----------------------------------------------------------------
1 Oracle essenessenbbbbbbbbbbbbbbb 3495 355
2 SQL * Plus: The Definitive 3995 502
3 Oracle PL/SQL 4495 87
4 Oracle8 Design Tips 1495 115
 
SQL> select * from book partition (p2 );
Unselected row
SQL>
9, null if null substitution:
LOAD DATA
INFILE *
Replace into table michigan_features
Fields terminated by "," optionally enclosed '"'
(
Feature_name,
Short_feature_name,
Elevation
NULLIF elevation = '0 ',
Feature_type
NULLIF feature_type = 'ppl ',
County
)
Begindata
MI, Chatham, 0, dl, Alger
MI, Shingleton, 821, al, Alger
MI, Rumely, 0, ppl, Alger
MI, Sundell, 1049 ,,,
 
Example:
Drop table michigan_features;
 
Create table michigan_features (
Feature_name varchar2 (100 ),
Short_feature_name varchar2 (100 ),
Elevation number,
Feature_type varchar2 (100 ),
County varchar2 (100 ))
SQL> select feature_type from michigan_features;
FEATURE_TYPE
-----------------------------------------------------------
Dl
Al
SQL * Loader data submission:
Generally, it is submitted after the data file is imported.
You can also specify the number of records submitted each time by specifying the ROWS = parameter.
Improve the Performance of SQL * Loader:
1) A simple and easy-to-ignore problem is that no indexes and/or constraints (primary keys) are used for the imported tables ). If this is done, the Database Import performance will be significantly reduced even when the ROWS = parameter is used.
2) You can add DIRECT = TRUE to Improve the Performance of imported data. Of course, this parameter cannot be used in many cases.
3) you can disable database logs by specifying the UNRECOVERABLE option. This option can only be used with direct.
4) You can run multiple import tasks at the same time.
5) You can use the readsize parameter to increase the memory size in the read area. The default value is 64 KB. Generally, the system supports a maximum of 20 mb.
6) SKIP_INDEX_MAINTENANCE = {TRUE | FALSE}
This parameter can be used to set whether to maintain indexes when loading data in DIRECT mode.
7) SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
When loading data in DIRECT mode, if the index is unusable, whether to continue to load data
Differences between conventional import and direct import:
You can use the INSERT statement to import data for regular import. Direct import can skip the related database logic (DIRECT = TRUE) and directly import the data to the data file.
General Import Process
STEP 1
Generate SQL commands for the data to be loaded.
STEP 2
Process the SQL commands (parse, fetch, execute ).
STEP 3
Find partial data blocks, or get new extents.
STEP 4
If required blocks are not in the buffer cache, read
The data blocks from disk.
STEP 5
Fill the appropriate data blocks in the buffer cache.
STEP 6
Write data blocks to the disk.
Direct import process
STEP 1
Format the input data into Oracle data blocks.
STEP 2
Get new extents for the table to be loaded.
STEP 3
Write the formatted data blocks to the disk.
Restrictions on direct import:
Restrictions on Direct Path Loads
• Clustered tables
• Object columns (Oracle9i can handle these)
• LOB columns (Oracle9i can handle these)
• VARRAY columns
• Nested tables (Oracle9i can handle these when they are loaded separately)
• REF columns (Oracle9i can handle these)
• BFILE columns
Reposted this article, hoping to help you, and thanks to the original author.

This article is from the "Dog's nest"