笔记系列------sqlloader的使用

最新推荐文章于 2023-08-30 08:28:53 发布

柠檬

最新推荐文章于 2023-08-30 08:28:53 发布

阅读量3.1k

点赞数

分类专栏： oracle 文章标签： sql linq 数据库

本文链接：https://blog.csdn.net/weixin_43475880/article/details/121830112

版权

oracle 专栏收录该内容

99 篇文章 1 订阅

订阅专栏

1. sql*loader

将外部数据（比如文本型）数据导入 oracle database。（用于数据导入、 不同类型数据库数据迁移）

2. sql*loader 导入数据原理：在段（segment 表）insert 记录

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

2) direct path：将记录插入到 segment 的 HWM(高水位线）以上的从未使用过的块，绕过 db_buffer,不检查约束。还可以关闭 redo, 也支持并行操作，加快插入速度。

例：

SQL> create table emp1 as select * from emp where 1=2;

SQL> insert into emp1 select * from emp; ///conventional 方式插入数据

SQL> insert /*+ APPEND */ into emp1 select * from emp; ///direct path方式插入数据, 必须 commit 后才能查看数据

3 sql*loader 用法

SQLLDR keyword=value [,keyword=value,...]

看帮助信息

$/u01/oracle/bin/sqlldr(回车）。如果要使用 direct path 方式, 在命令行中使用关键字 direct=TRUE

sql*loader 与 data dump 的一个区别

data dump 只能读取由它导出的文件, 而 sql*loader 可以读取任何它能解析的第三方文件格式

4 例子

1）模拟生成数据源

SQL> select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','|| deptno from scott.emp;

EMPNO||','||ENAME||','||JOB||','||MGR||','||HIREDATE||','||SAL||','||COMM||','|| DEPTNO

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)建个目录

[oracle@timran]$mkdir -p /home/oracle/sqlload

[oracle@timran]$cd /home/oracle/sqlload

[oracle@timran sqlload]$vi emp.dat ----生成平面表

--------查看数据源

[oracle@timran sqlload]$ more emp.dat

3）conventional 方式导入

建立控制文件

[oracle@work sqlldr]$ vi emp.ctl
load data
infile '/home/oracle/sqlload/emp.dat'
insert     --insert 插入表必须是空表，非空表用 append
into table emp1
fields terminated by ','
optionally enclosed by '"'
( empno, ename, job, mgr, hiredate, comm, sal, deptno)

[oracle@single06 sqlload]$ ll
total 8
-rw-r--r-- 1 oracle oinstall 178 Dec 15 13:50 emp.ctl
-rw-r--r-- 1 oracle oinstall 606 Dec 15 13:43 emp.dat

4)在 scott 下建立 emp1 表（内部表），只要结构不要数据

SQL> conn scott/scott

SQL> create table emp1 as select * from emp where 1=2; ///不需要数据，只需要表结构

5)执行导入（normal）

[oracle@single06 sqlload]$ sqlldr scott/scott control=emp.ctl log=emp.log

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Dec 15 14:00:32 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 15

6)验证： 11:07:12 SQL> 11:07:12 SQL> select count(*) from scott.emp1;

上例的另一种形式是将数据源和控制文件合并在.ctl 里描述

[oracle@work sqlldr]$ vi emp02.ctl
load data
infile *
append
into table emp1
fields terminated by ','
optionally enclosed by '"'
(
empno,
ename,
job,
mgr,
hiredate,
comm,
sal,
deptno)
begindata
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

[oracle@single06 sqlload]$ sqlldr scott/scott control=emp02.ctl log=emp02.log

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Dec 15 14:27:49 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 14

验证：

SQL> select count(*) from scott.emp1;

COUNT(*)
----------
28

五、sqloader相关文件说明

1.日志：

[oracle@single06 sqlload]$ cat emp.log

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Dec 15 15:11:37 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   emp.ctl
Data File:      /home/oracle/sqlload/emp.dat
  Bad File:     emp.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table EMP1, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO                               FIRST     *   ,  O(") CHARACTER
ENAME                                NEXT     *   ,  O(") CHARACTER
JOB                                  NEXT     *   ,  O(") CHARACTER
MGR                                  NEXT     *   ,  O(") CHARACTER
HIREDATE                             NEXT     *   ,  O(") CHARACTER
COMM                                 NEXT     *   ,  O(") CHARACTER
SAL                                  NEXT     *   ,  O(") CHARACTER
DEPTNO                               NEXT     *   ,  O(") CHARACTER

Record 15: Rejected - Error on table EMP1, column EMPNO.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table EMP1:
  14 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 132096 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            15
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Wed Dec 15 15:11:37 2021
Run ended on Wed Dec 15 15:11:37 2021

Elapsed time was:     00:00:00.11
CPU time was:         00:00:00.01

[oracle@single06 sqlload]$ ll
-rw-r--r-- 1 oracle oinstall    1 Dec 15 15:11 emp.bad      ///为坏块，未成功输入数据库的数据
-rw-r--r-- 1 oracle oinstall 178 Dec 15 13:50 emp.ctl         ///控制文件
-rw-r--r-- 1 oracle oinstall 606 Dec 15 13:43 emp.dat      ///原始数据
-rw-r--r-- 1 oracle oinstall 2087 Dec 15 15:11 emp.log     ///加载日志

柠檬

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
2
评论
笔记系列------sqlloader的使用

1. sql*loader将外部数据（比如文本型）数据导入 oracle database。（用于数据导入、不同类型数据库数据迁移）2. sql*loader 导入数据原理：在段（segment 表）insert 记录1) ）conventional：将记录插入到 segment 的 HWM(高水位线）以下的块, 要首先访问 bitmap，来确定那些 block 有 free space2) direct path：：将记录插入到 segment 的 HWM(高水位线）以上的从...
复制链接

扫一扫