les19_加载数据.pptVIP

Loading Data into a Database Objectives After completing this lesson, you should be able to do the following: Demonstrate usage of Direct Load operations Describe the usage of SQL*Loader Perform basic SQL*Loader operations List guidelines for using SQL*Loader and Direct Load Data Loading Methods Direct Load Direct Load insert can be performed in the following ways: Normal (serially), or in parallel Into partitioned tables, nonpartitioned tables, or single partitions of a table With or without logging of redo data Serial Direct Load Parallel Direct Load SQL*Loader Using SQL*Loader SQL*Loader Control File The loader control file tells SQL*Loader: Where to find the load data The data format Configuration details: Memory management Record rejection Interrupted load handling details How to manipulate the data Control File Syntax Considerations The syntax is free-format. Syntax is not case sensitive. Comments extend from the two hyphens (--) that mark the beginning of the comment to the end of the line. The CONSTANT keyword is reserved. EXAPLE OF CONTROLFILE(1) LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY (DEPTNO, DNAME, LOC) BEGINDATA 12,RESEARCH,SARATOGA 10,ACCOUNTING,CLEVELAND 11,ART,SALEM 13,FINANCE,BOSTON 21,SALES,PHILA. 22,SALES,ROCHESTER EXAPLE OF CONTROLFILE(2) LOAD DATA INFILE ulcase2.dat INTO TABLE EMP EMPNO POSITION(01:04) INTEGER EXTERNAL, ENAME POSITION(06:15) CHAR, JOB POSITION(17:25) CHAR, MGR POSITION(27:30) INTEGER EXTERNAL, SAL POSITION(32:39) DECIMAL EXTERNAL, COMM POSITION(41:48) DECIMAL EXTERNAL, DEPTNO POSITION(50:51) INTEGER EXTERNAL) Input Data and Data Files SQL*Loader reads data from one or more files specified in the control file. From SQL*Loader’s perspective, the data in the data file is organized as records. A data file can be in one of three formats: Fixed-record format Variable-record format Stream-record format Logical Records SQL*Loader can be instruct