sql*loader

最新推荐文章于 2021-04-03 16:11:38 发布

claymore2015

最新推荐文章于 2021-04-03 16:11:38 发布

阅读量140

点赞数

oracle sql*loader是oracle数据加载的重要工具，可以实现高速批量数据加载。分为常规加载和直接加载：

1）normal ：将记录插入到segment的HWM(高水位线）以下的块，要首先访问bitmap ，来确定那些block 有free space

2）direct：将记录插入到segment的HWM(高水位线）以上的从未使用过的块，加快插入速度
下面演示常规加载和直接加载：
常规加载
1 创建数据文件：
[oracle@bertie ~]$ vim emp.sql

set heading off
set feedback off
set time off
set linesize 120
set pagesize 0
set echo off
set trimspool off
spool /home/oracle/sqlldr/emp.dat
select empno || ',' || ename || ',' ||job ||','||mgr||','||hiredate||','
||comm||','||deptno from scott.emp;
spool off;

SQL> @/home/oracle/sqlldr/emp.sql
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

2 创建表
SQL> create table emp1 as select * from emp where 1=2;
SQL> select table_name , num_rows,blocks,empty_blocks from user_tables where
2 table_name='EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------- ---------- ---------- ------------
EMP1 0 0 8

3 写控制文件
[oracle@bertie sqlldr]$ vi emp.ctl

load data
infile '/home/oracle/sqlldr/emp.dat'
insert
into table emp1
fields terminated by ','
optionally enclosed by '"'
(empno,
ename,
job,
mgr,
hiredate,
comm,
sal,
deptno)

4 导入
[oracle@bertie sqlldr]$ sqlldr scott/tiger control=emp.ctl log=emp.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 13 12:19:17 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 14

5 查看数据
12:15:42 SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 300 1600 30
7521 WARD SALESMAN 7698 22-FEB-81 500 1250 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1400 1250 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 0 1500 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.
6 常规导入就是在高水位线以上的块插入数据
验证：
继续在表emp1中插入数据
修改控制文件
[oracle@bertie sqlldr]$ vim emp1.ctl

load data
infile '/home/oracle/sqlldr/emp.dat'
append ——非空的表就用append,空表就用insert
into table emp1
fields terminated by ','
optionally enclosed by '"'
(empno,
ename,
job,
mgr,
hiredate,
comm,
sal,
deptno)
[oracle@bertie sqlldr]$ sqlldr scott/tiger control=emp1.ctl log=emp1.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 13 14:15:53 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 14
14:16:04 SQL> analyze table emp1 compute statistics;

Table analyzed.

14:16:12 SQL> select table_name , num_rows ,blocks ,empty_blocks from user_tables where table_name='EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------- ---------- ---------- ------------
EMP1 28 5 3 ——从这里就可以看出，常规导入就是把数据加载到高水位线以上的块中，没有占用新块

1 row selected.

直接导入

[oracle@bertie sqlldr]$ sqlldr scott/tiger control=emp1.ctl log=emp1.log direct=y

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 13 14:23:08 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 14.
14:16:18 SQL> analyze table emp1 compute statistics;

Table analyzed.

14:23:18 SQL> select table_name , num_rows ,blocks ,empty_blocks from user_tables where table_name='EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------- ---------- ---------- ------------
EMP1 42 9 7 ——由此看出直接加载是将数据插入到段中没使用的新块中，所以重新分配了8个块

1 row selected.

每个库的db_block_size大小是不一一样的，默认的都是8k
14:53:42 SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
14:54:48 SQL>
每个区包含8个块

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29598413/viewspace-1218096/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29598413/viewspace-1218096/