Application of SQL Loader in Oracle

1. Simulate generating data source

--Create a script Emp.sql "many of these formats will change, and then remember to execute another script to restore the original On.sql"

[Oracle@work sqlldr]$ more Emp.sql

Set Heading off

Set Feedback off

Set Time off

Set Linesize 120

Set pagesize 0

Set echo off

Set Trimspool off

Spool/export/home/oracle/sqlldr/emp.dat

Select Empno| | ', ' | | ename| | ', ' | | job| | ', ' | | mgr| | ', ' | | hiredate| | ', ' | | sal| | ', ' | | comm| | ', ' | | Deptno from Scott.emp;

Spool off;

[Oracle@solaris10 Sqlplus] $vi on.sql

Set heading on

Set feedback on

Set time on

Set Linesize 250

Set PageSize 24

Set echo on

Set Trimspool on

Sql> @/export/home/oracle/sqlldr/emp.sql

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20

7499,allen,salesman,7698,1981-02-20 00:00:00,1600,300,30

7521,ward,salesman,7698,1981-02-22 00:00:00,1250,500,30

7566,jones,manager,7839,1981-04-02 00:00:00,2975,,20

7654,martin,salesman,7698,1981-09-28 00:00:00,1250,1400,30

7698,blake,manager,7839,1981-05-01 00:00:00,2850,,30

7782,clark,manager,7839,1981-06-09 00:00:00,2450,,10

7788,scott,analyst,7566,1987-04-19 00:00:00,3000,,20

7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10

7844,turner,salesman,7698,1981-09-08 00:00:00,1500,0,30

7876,adams,clerk,7788,1987-05-23 00:00:00,1100,,20

7900,james,clerk,7698,1981-12-03 00:00:00,950,,30

7902,ford,analyst,7566,1981-12-03 00:00:00,3000,,20

7934,miller,clerk,7782,1982-01-23 00:00:00,1300,,10

Sql>

--View the data source

[Oracle@work sqlldr]$ more Emp.dat

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20

7499,allen,salesman,7698,1981-02-20 00:00:00,1600,300,30

7521,ward,salesman,7698,1981-02-22 00:00:00,1250,500,30

7566,jones,manager,7839,1981-04-02 00:00:00,2975,,20

7654,martin,salesman,7698,1981-09-28 00:00:00,1250,1400,30

7698,blake,manager,7839,1981-05-01 00:00:00,2850,,30

7782,clark,manager,7839,1981-06-09 00:00:00,2450,,10

7788,scott,analyst,7566,1987-04-19 00:00:00,3000,,20

7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10

7844,turner,salesman,7698,1981-09-08 00:00:00,1500,0,30

7876,adams,clerk,7788,1987-05-23 00:00:00,1100,,20

7900,james,clerk,7698,1981-12-03 00:00:00,950,,30

7902,ford,analyst,7566,1981-12-03 00:00:00,3000,,20

7934,miller,clerk,7782,1982-01-23 00:00:00,1300,,10

2, Import data to table EMP1

20:14:51 sql> CREATE TABLE EMP1 as SELECT * from EMP where 1=2;--creates an empty table with only a table structure

Table created.

20:14:56 sql> analyze table EMP1 Compute first analyze tables to see the difference between Normal mode import and direct import

Table analyzed.

20:15:03 sql> Col table_name for A15

20:16:48 sql> Select Table_name,num_rows,blocks,empty_blocks from user_tables where table_name= ' EMP1 ';

TABLE_NAME Num_rows BLOCKS Empty_blocks

--------------- ---------- ---------- ------------

EMP1 0 0 128

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/