SQL*Loader

优势：

批量数据装载，比传统的数据插入效率更高
能处理多种格式的平面文件
适合OLAP类型数据库

Sql*loader的相关文件：

控制文件：用于控制数据导入的行为方式的文件
参数文件
数据文件
丢弃文件：因为条件不满足而被丢弃的数据存储在该文件
坏数据文件：因为数据本身格式或约束等问题报错的数据存储在该文件
日志文件

一个简单的ctl控制文件：

--test.t
load data  -- 加载数据
infile 'd:\temp\mydata.txt'  -- 数据文件位置
into table t  -- 要插入的表
fields teminated by ","   -- 数据文件中字段的分隔符
optionally enclosed by ""  -- 字段内容在双引号内
(
    id,
    name,
    birth date "yyyy-mm-dd"  -- 日期类型
)

CTL文件中，可以指定装入表的方式：

APPEND：原有的表有数据，在原有数据后面追加
INSERT（默认值）：装载空表。如果原有的表有数据，sqlldr会停止。
REPLACE：表中有数据，原先的数据会被删除
TRUNCATE：指定的内容和replace相同，会用truncate语句删除现存数据

按照定长字符串装载的控制文件：

load data
infile *  -- *表示数据是直接放在ctl控制文件中的
replace
into table departments
(
    dept position(02:05) char(4),  -- 指出该字段在数据文件中的字符串位置，前四个字符为dept
    deptname position(08:27) char(20)
)
begindata  -- 声明下面内容为要装载的数据
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE

控制文件中对字符处理：

load data
infile 'mail_orders.txt'
badfile 'bad_orders.txtt'  -- 指定坏数据文件
append  -- 追加方式加载
into table mailing_list
fields terminated by ","
(
    addr,
    city,
    state,
    zipcode,
    mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",  -- 对mailing_addr字段做处理
    mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
    mailing_state
)

跳过前几行不导入：

load data
infile *
into table load_positional_data
skip 1  -- 跳过前1行不导入
(
    data1 position(1:5),
    data2 position(6:15)
)
begindata
id name  -- 这一行会被跳过
11111AAAAAAAAA
22222BBBBBBBBB

在控制文件中按条件加载：

load data
infile *
discardfile 'discardfile.txt'  -- discard文件
replace
into table emp
when empno != ''  -- 条件判断。当条件不满足时，不会被导入该表中，这部分数据会被放入discard file（丢弃的数据文件）中
(
    empno position(1:4) integer external,
    ename position(6:15) char,
    deptno position(17:18) char,
    mgr position(20:23) integer external
)
into table proj
when projno != ''  -- 条件判断
(
    projno position(25:27) integer external,
    empno position(1:4) integer external
)

discard数据和bad数据：discard的数据是正常可用的数据，只是因为条件不满足加载条件而放入discard文件中。

sqlldr的direct条件：

# 命令行方式添加direct条件
sqlldr userid=me/pass control=test.ctl direct=true

控制文件中添加direct选项：

options(direct=true, errors=50, rows=500000)  -- 在控制文件中添加direct选项。errors为超过50个错误就自动退出，rows为500000条数据提交一次
load data
infile *
into table dept

direct参数可以将sqlldr设置为直接加载的方式。

和direct相对的是conventional（传统加载方式），conventional方式是将数据写入SGA中（类似sql中的insert语句）。

direct方式会绕过SGA区，直接加载到PGA中。

conventional方式是插入到SGA区，最终写入数据文件时，会插入到表的高水位以下的空间中，这时就要产生一系列的redo、undo，所以就会比direct方式慢。

direct方式不去考虑表高水位以下的部分是否有空闲空间，而是直接加载数据到表的高水位以上部分。

direct方式在数据加载的过程中，表的高水位指针不会向后移，所以其他用户依然可以正常的对表进行操作，不会产生锁。等数据加载完成后，oracle再将该表的高水位移到新的高水位位置，此时用户便可以访问新插入的数据。这种方式不需要考虑一致性读、可用数据块查找、索引维护、加锁等耗时操作，也不会记录redo、undo，所以direct方式加载速度很快。

对于OLAP系统，因为表空间中间不会有很多空洞，所以适合direct方式。OLTP系统对空间比较敏感，讲究内存效率，不太适合OLTP。

sql loader直接加载方式的OCI（Oracle Call Interface）：程序如果不想通过sqlldr命令来调用sql loader，也可以通过sql loader的OCI接口来完成。

数据泵

数据泵（expdp/impdp）：

用来替代 exp/imp
速度更快，机制上不同于老的 exp/imp，提供更多的功能
始于Oracle 10g
从Oracle 11g开始，不再提供老的imp/exp支持（用户依然可以继续使用exp/imp命令，但是当出现问题时，oracle的支持人员不再对exp/imp相关问题进行处理）

因为expdp导出时，需要比exp做更多的前置处理，所以导出的数据量很少时使用exp会更快一些。但是当数据量大的时候（大约超过4Mb之后），使用expdp要比exp快得多。

expdp导出的文件大小也比exp要小。

使用impdp导入也会比imp更快。

数据泵速度快的原因：

更好的利用并行技术，具有对大量数据处理的优势
全部操作在server端完成，避免了数据在网络上的传输
导出文件的格式更接近于数据库本身文件的格式，避免了数据写入文件时的转换
直接路径的加载，使得效率更高
元数据和数据在导出过程中可以重叠进行，提高导出效率

使用expdp、impdp、grid control等调用数据泵时，都是由dbms_datapump包提供的引擎。

用法示例：

-- 创建一个路径对象
create directory exp as 'd:\temp';

使用expdp导出：

expdp test/test directory=exp dumpfile=test.dmp

导入示例：

impdp test/test directory=exp dumpfile=test.dmp

数据泵导出模式：

Full：全库导出
Schema：根据用户导出
Table：根据表导出
Tablespace：根据表空间导出
Transportable tablespace：传递表空间

数据泵和exp的命令参数比较：

数据泵	exp
schemas	owner
remap_schema	touser
content=METADATA_ONLY	rows=N
exclude=TRIGGER	triggers=N

数据泵remap：

remap_datafile：更改数据文件位置
remap_tablespace：变更表空间名
remap_schema：变更用户

Data Pump API：dbms_datapump包里面的函数和存储过程可以直接在程序里面进行调用。

外部表

外部表（External table）：

使用数据库之外的数据
提供一种直接读取外部数据的机制

示例：

在操作系统中创建一个文件：D:\temp\mydata.txt

1,"Scott","1990-01-01"
2,"Frank","2021-12-11"

创建一个路径对象

create or replace directory test as 'd:\temp';

创建外部表：

create table ext_test
(
    id int,
    name varchar2(20),
    birth varchar2(20)
)
organization external  -- 声明是一个外部表
(
    type oracle_loader  -- 数据库读取驱动方式为sql*loader
    default directory test  -- 访问的目录
    access parameters   -- 控制参数
    (
        records delimited by newline  -- 数据行以换行分隔
        fields terminated by ','  -- 字段以逗号分隔
        (id, name, birth)  -- 对应字段名
    )
    location('mydata.txt')  -- 文件名
);

ext_test表本身不存在，但是它会映射到mydata.txt文件。这样就可以像访问oracle中的表一样去访问数据文件中的数据：

select * from ext_test where id='1'

外部表不可以创建索引，创建索引会报错：

ORA-30657：操作在外部组织表上不受支持

平时直接查询alert日志时比较麻烦，可以将alert日志做成外部表：

-- 创建路径对象
create or replace directory alert as 'D:\PC_Software\Program_Software\DataBase_Software\Database_Server\oracle\app\Administrator\diag\rdbms\orcl\orcl\trace'

创建外部表：

create table ext_alert(
    content varchar2(2000)
)
organization external
(
    type oracle_loader
    default directory alert
    location('alert_orcl.log')
);

然后就可以使用sql进行查询：

select count(*) from ext_alert;
select * from ext_alert where content like 'ORA-%';

数据库学习笔记

12-常用工具

SQL*Loader

数据泵

外部表