oracle dat文件导入

今天在执行dat文件导入的时候，由于很久没执行过这种操作了，因此又变得生疏了，花了比较长的时间才弄好。然后看到网上关于oracle dat文件的导入介绍的都是一部分，所以特在这里总结一下。

一、dmp文件和dat文件的区别
1、dmp文件包含的信息有数据库的版本信息、数据文件的字符集、数据文件导出路径信息、数据库用户信息、数据库导出的表的信息、索引信息、表空间信息等数据库的详细信息。dat文件只是导入的表的数据信息。
2、oracle可以一次性导入多张表到dmp文件中，但是只能导一张表到dat文件中。
3、dmp文件可以包括表空间、索引、用户、表、序列、存储过程、视图等数据库的所有信息，并可以用这些信息还原整个数据库，但是dat文件只是用来存放表的数据信息，只能还原表的数据信息。
4、对于单张表的数据导入（即不需要创建表、创建表索引等信息）时，dat文件的导入要比dmp文件导入快很多，由其是对于数据量非常大的表。
5、dmp文件主要用于数据库备份、数据迁移等需要多张表操作或需要创建表信息的操作。dat文件主要用于纯数据的迁移和备份。
二、dat文件转换为控制文件（.ctl文件）
1、control文件
------------------------------------------------------------------------
--SQL
--程序功能： 在命令行用sqlplue自动生成sql*load控制文件
--输入参数：
--&1   表名
--&2   数据文件名
--&3   目录名
--返回值结果：生成文件为：目录名＋表名＋.ctl  
--调用: sqlplus PAS/pas123 @d:\control.sql jksj_ll 20091231_005_LL.dat d:\
--created by hqx  2010-03-26
--修改记录：


------------------------------------------------------------------------ 
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 
spool &3&1..ctl


select 'LOAD DATA'||chr (10)||        
       'INFILE ''&3&2'''||chr (10)||    --说明导入文件的路径
       'APPEND INTO TABLE '||table_name||chr (10)||   --说明导入至的数据库表名
       'FIELDS TERMINATED BY '','''||chr (10)||   --说明dat文件列与列之间的分隔符
       'OPTIONALLY ENCLOSED BY ''"'''||chr (10)|| --说明包装数据类型的符号，即当在dat文件中用“”包装数据，这时需要配置“，数据库才不会读”.
       'TRAILING NULLCOLS'||chr (10)||'('
from   all_tables
where  table_name = upper ('&1');


select decode (column_id, 1, '   ', ' , ')||
       rpad (column_name, 33, ' ')||
       decode (data_type,
               'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',  --定义各datatype的为空时的处理方式
               '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',     'DATE "MM/DD/YY"  NULLIF ('||column_name||'=BLANKS)',
               null)
from   user_tab_columns
where  table_name = upper ('&1')
order  by column_id;




select ')' 
from sys.dual;
spool off
exit
2、根据control.sql文件生成对应的控制文件
执行sqlplus student/869160 @C:\Users\Administrator\Desktop\control.sql JKSJ_DKBJLXSH DKSX.dat C:\Users\Administrator\Desktop\
这里的JKSJ_DKBJLXSH为control.sql里面的&1，DKSX.dat为control.sql里面的&2，C:\Users\Administrator\Desktop\为control.sql里面的&3
这样就生成了以表名JKSJ_DKBJLXSH为文件名的控制文件JKSJ_DKBJLXSH.ctl。如下：
LOAD DATA
INFILE 'C:\Users\Administrator\Desktop\DKSX.dat'
APPEND INTO TABLE JKSJ_DKBJLXSH
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(


   ZHDH                             CHAR NULLIF (ZHDH=BLANKS)
 , JYRQ                             INTEGER EXTERNAL NULLIF (JYRQ=BLANKS)
 , ZJLSH                            CHAR NULLIF (ZJLSH=BLANKS)
 , QXRQ                             INTEGER EXTERNAL NULLIF (QXRQ=BLANKS)
 , JXRQ                             INTEGER EXTERNAL NULLIF (JXRQ=BLANKS)
 , BS                               CHAR NULLIF (BS=BLANKS)
 , JGDH                             CHAR NULLIF (JGDH=BLANKS)
 , KMH                              CHAR NULLIF (KMH=BLANKS)
 , JE                               DECIMAL EXTERNAL NULLIF (JE=BLANKS)
 , FL                               DECIMAL EXTERNAL NULLIF (FL=BLANKS)
 , WSFL                             DECIMAL EXTERNAL NULLIF (WSFL=BLANKS)
 , JXBJ                             DECIMAL EXTERNAL NULLIF (JXBJ=BLANKS)
 , JXLX                             DECIMAL EXTERNAL NULLIF (JXLX=BLANKS)
 , SQLX                             DECIMAL EXTERNAL NULLIF (SQLX=BLANKS)
 , JYM                              CHAR NULLIF (JYM=BLANKS)
 , BQYHBJ                           DECIMAL EXTERNAL NULLIF (BQYHBJ=BLANKS)
 , BQYHLX                           DECIMAL EXTERNAL NULLIF (BQYHLX=BLANKS)
)


3、根据生成的控制文件将数据文件导入数据库
执行：
sqlldr userid=student/869160 control=C:\Users\Administrator\Desktop\JKSJ_DKBJLXSH.ctl log=C:\Users\Administrator\Desktop\JKSJ_DKBJLXSH.log direct=true；
即语句为sqlldr userid=数据库用户名/密码 control=控制文件的绝对路径 log=日志文件存放绝对路径 direct=参数
如果语句执行成功，会出现：

注：在windows环境下，在cmd窗口执行生成控制文件的语句时，由于设置了sqlplus，在.ctl控制文件中，可能会存在时间信息，这是需要手工删除这些信息，否则在执行数据文件导入时会报错。