SQL*Loader: 从文件导入数据库

最新推荐文章于 2024-01-31 16:39:57 发布

原创最新推荐文章于 2024-01-31 16:39:57 发布 · 699 阅读

0 ·

CC 4.0 BY-SA版权

Oracle Database 专栏收录该内容

26 篇文章

订阅专栏

本文详细介绍如何使用SQL*Loader工具高效迁移数据。包括创建控制文件的脚本、调整数据格式的方法以及通过SQL Developer生成insert/loader语句的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

之前写过一篇 SQL*Loader 的博客, 介绍了这个工具是什么, 怎么用, 最后说 metalink 里面的这篇Note: 1019523.6 可以自动产生控制文件. 但是根据我的测试, 这篇Note 里面的脚本产生的控制文件是错的. 这篇博客给出我修改过的脚本, 和详细步骤:
同一个数据库, 可以使用下面这种格式:

create table yu_mmt_01 as select * from mtl_material_transactions where 1=2;
select * from yu_mmt_01;
insert into yu_mmt_01 select * from mtl_material_transactions where rownum < 100;

如果不同数据库之间需要复制数据, 可以先 select 出数据, 然后用 sql developer 生成 insert/loader 语句, 直接在目标数据库里面跑这个脚本.
如果数据量很大, 就需要借助 SQL*Loader 这个工具了.
首先要确保 source table 和 destination table 两个表结构是一样的

create table yu_mmt_01 as select * from mtl_material_transactions where 1=2;

copy 下面的代码保存成文件generate_ctl.sql, 上传到/var/tmp 目录下, 用sqlplus @generate_ctl.sql 运行这个脚本, 可以在这个目录下获得 .ctl 控制文件.

set echo off
set heading off
set verify off
set feedback off
set show off
set trim off
set pages 0
set concat on
set lines 300
set trimspool on
set trimout on
ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 

spool mtl_material_transactions.ctl

select 'LOAD DATA'||chr (10)||
       'INFILE '''||lower (table_name)||'.dat'''||chr (10)||
       'INTO TABLE '||' YU_MMT_01 '||chr (10)||
       'APPEND '||chr (10)||
       'FIELDS TERMINATED BY '','''||chr (10)||
       'OPTIONALLY ENCLOSED BY  ''"'' '||chr (10)||
       'TRAILING NULLCOLS'||chr (10)||'('
from   all_tables
where  table_name = upper ('mtl_material_transactions');

select decode (column_id, 1, ' ', ' , ')||
       rpad (column_name, 33, ' ')||
       decode (data_type, 'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
                          'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
                          'NUMBER', decode (data_precision, 0, 'INTEGER EXTERNAL NULLIF ('||column_name||'=BLANKS)',
                                    decode (data_scale, 0, 'INTEGER EXTERNAL NULLIF ('||column_name||'=BLANKS)',
                           'DECIMAL EXTERNAL NULLIF ('||column_name||'=BLANKS)')),
                           'DATE', 'TIMESTAMP "DD-MON-YY HH.MI.SS.FF9 AM" NULLIF ('||column_name||'=BLANKS)', null)
from   all_tab_columns
where  table_name = upper ('mtl_material_transactions')
order  by column_id;

select ')'
from   sys.dual;
spool  off
.
/

设置 sql developer 数据库编码集: 首选项 -> 数据库 -> NLS, 全部设置 AMERICAN
从source db 里 select 出需要的数据, 保存成 .csv 格式,

select * from mtl_material_transactions where rownum < 100;

删掉第一行 column 名字. 命名为 mtl_material_transactions.dat, 上传到 /var/tmp 目录
在 destination db 运行 SQLLDR 命令: sqlldr apps/apps control=mtl_material_transactions.ctl data=mtl_material_transactions.dat
需要注意的是, sql developer 导出的 date 格式, 全部是 timestamp 格式的, 所以需要对 date 格式进行调整, 参考:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00212
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html