SQLServer导数据到Oracle

从SQLServer导数据到Oracle差不离有以下两种办法:

前言

外表表概述

表面表只可以在Oracle
9i之后来利用。轻便地说,外界表,是指不设有于数据库中的表。通过向Oracle提供描述外界表的元数据,大家能够把三个操作系统文件正是多少个只读的多少库表,就如那么些数量存款和储蓄在叁个无独有偶数据库表中同样来扩充探问。外界表是对数据库表的延长。

干活中有段时日时临时提到到不一致版本的数据库间导出导入数据的难点,索性整理一下,并简要相比下品质,有所遗漏的方法也接待钻探、补充。

  1. 应用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle
    Provider for OLE DB连接受Oracle
  2. 导出到平面文件
  3. 导出富含数据的SQL脚本。
  4. 使用ETL工具。
  5. 温馨开辟软件。

SQL
SE酷路泽VE中华V提供两种分化的数码导出导入的工具,也足以编写制定SQL脚本,使用存款和储蓄进程,生成所需的数据文件,乃至足以生成包蕴SQL语句和数据的剧本文件。各有利害,以适用不一样的须求。上面介绍大容积数据导出导入的利器——BCP实用工具。同期在末端也介绍BULK
INSERT导入大体量数据,以至BCP结合BULK
INSERT做多少接口的实施(在SQL2009途睿欧2上推行)。

外表表的本性 

位于文件系统之中,按自然格式分割,如文本文件也许此外品类的表能够看作外界表。
对外部表的探问能够因而SQL语句来成功,而没有需求先将表面表中的多寡装载进数据库中。
表面数据表都是只读的,由此在外表表不可以看到实行DML操作,也不可能成立索引。
ANALYZE语句不扶植采撷外界表的总括数据,应该利用DMBS_STATS包来访问外界表的总结数据。

00.创建测量试验处境

以下使用第2种办法来进展数据迁移的。

 

创办外界表的小心事项 

01.使用SQL Server Import and Export Tool

采纳BCP合适导出大容积数据。这里导出千万品级的数额,也是高效就会不负职务。

1. BCP的用法

1.须要先成立目录对象

在确立指标的时候,须求小心,Oracle数据库系统不会去确认那个目录是或不是确实存在。借使在输入那些目录对象的时候,非常的大心把门路写错了,那大概那个外界表如故能够健康构建,可是却不能查询到数码。由于创立目录对象时,贫乏这种自己检讨的编写制定,为此在将路线赋予给这一个目录对象时,需求特意的小心。此外要求专一的是路径的深浅写。在Windows操作系统中,其路线是不区分轻重缓急写的。而在Linux操作系统,那几个路子须求区分轻重缓急写。故在差异的操作系统
中,创立目录对象时索要小心这些分寸写的歧异

02.使用Generate Scripts

假使导出时还索要做一些多少的管理,比如多表关联,字符管理等,相比较复杂的逻辑,最棒是做成存款和储蓄进程,BCP直接调用存储进程就能够。

BCP 实用工具能够在 Microsoft SQL Server
实例和客户钦命格式的数据文件间大容积复制数据。使用
BCP实用工具能够将大批量新行导入 SQL Server
表,或将表数据导入数据文件。除非与 queryout
选项一同利用,否则使用该实用工具没有要求精通 Transact-SQL
知识。BCP不仅可以够在CMD提醒符下运转,也足以在SSMS下施行。

2.对于操作系统文件的渴求

创设外界表时,必得钦命操作系统文件所选用的相间符号。况且该分隔符有且唯有三个。成立外界表时,不可能含有标题列。如若这一个标题音信与外表表的字段类型不一致样(如字段内容是number数据类型,而标题消息则是字符型数据,则在询问时就能出错)。若是数据类型碰巧一致的话,那些标题音信Oracle数据库也会作为普通记录来对待。

当Oracle数据库系统访谈那几个操作系统文件的时候,会在这里个文件所在的目录自动创立贰个日志文件。无论最后是或不是采访成功,那些日志文件都会如期建设构造。查看那几个日志文件,可以理解数据库访谈外界表的频率、是还是不是中标访谈等等。暗中认可意况下,该日志在与外表表的同等directory下发出。

03.使用BCP

BCP "exec TestDB.dbo.export_t1 " queryout d:\export\t1.txt -c -t"||" -S"192.168.1.100" -Urpt -Prpt123
pause

USE TestDB
GO

CREATE PROC [dbo].[export_usercar]
AS
    SELECT  [carId]
           ,CONVERT(NVARCHAR(30), [addTime], 120)
           ,CONVERT(NVARCHAR(30), [lastSearchTime], 120)
           ,CONVERT(NVARCHAR(30), [updateTime], 120)
           ,[carType]
           ,[userTelephone]
           ,[isCorrect]
           ,[userId]
           ,[validFlag]
           ,[Channel]
           ,[carCode]
           ,[engineNumber]
           ,[carNumber]
    FROM    [TestDB].[dbo].[t1] WITH ( NOLOCK )
    WHERE   validFlag = 1
            AND isCorrect = 1;

图片 1

3.在确立临时表时的连带限制

对表中字段的称号存在特殊字符的景况下,必需使用匈牙利(Hungary)语状态的下的双引号将该表列名称连接起来。如应用”SalseID#”。
对于列名字中特殊符号未利用双引号括起来时,会导致力不胜任平常查询数据。
提出不要选择特殊的列标题字符
在开创外界表的时候,并从未在数据库中创立表,也不会为外界表分配任何的蕴藏空间。
始建外界表只是在多少字典中开创了表面表的元数据,以便对应访谈外界表中的数额,而不在数据库中蕴藏外界表的多寡。
回顾地说,数据仓库储存储的只是与外部文件的一种对应提到,如字段与字段的对应关系。而未有存款和储蓄实际的多少。
是因为存款和储蓄实际数据,故不可能为外界表创设索引,相同的时候在数量运用DML时也不帮忙对外界表的插入、更新、删除等操作。

04.使用SqlBulkCopy

把导出文件上传到Oracle所在的主机上,如CentOS下。

figure-1

4.剔除了那几个之外界表或然目录对象

平常情状下,先删除了那些之外部表,然后再删除目录对象,假设目录对象中有几个表,应除去全数表之后再删除目录对象。
假定在未删减外界表的气象下,强制删除了目录,在询问到被剔除的表面表时,将摄取”对象不设有”的错误消息。
查询dba_external_locations来得到当前具备的目录对象以至相关的表面表,同期会交到那些外界表所对应的操作系统文件的名字。 假若只是在数据库层面上剔除此之外界表,并不会自行删除操作系统上的表面表文件。

05.运用Linked Server进行数量迁移

使用Oracle的SQL*LOADEXC60导入平面文件。若是Oracle中有一度创办好的表,与导入文本对应。

 

 5.对于操作系统平台的限量

不一样的操作系统对于外界表有例外的演说和展现格局
如在Linux操作系统中开创的文件是分号分隔且每行一条记下,但该公文在Windows操作系统上张开则其实不然。
提出幸免不一样操作系统以致差别字符集所推动的熏陶

06.使用RedGate的SQL Data Compare

把以下的原委用vi,写到import-t1.ctl

语法:

创造外部表 

运用CREATE TABLE语句的OSportageGANIZATION
EXTENERAL子句来创建国门外界表。外界表不分红任何盘区,因为唯有是在数量字典中开创元数据。

07.结出相比较

load data
CHARACTERSET 'ZHS16GBK'
infile '/data/import/t1.txt' "str '\r\n'"
into table SCOTT.T1
fields terminated by '||' TRAILING NULLCOLS
(
carId, 
addTime DATE "YYYY-MM-DD HH24:MI:SS",
lastSearchTime DATE "YYYY-MM-DD HH24:MI:SS",
updateTime DATE "YYYY-MM-DD HH24:MI:SS",
carType ,
userTelephone  ,
isCorrect  ,
userId  ,
validFlag ,
Channel ,
carCode  ,
engineNumber ,
carNumber  
)
bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-ddatabase_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )] 
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] 
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-S [server_name[\instance_name]]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

 

1.外部表的创设语法

createtabletable_name
           (col1 datatype1,col2 datatype2,col3 datatype3)
            organization exteneral
           (…..)
详尽语法可参见小编的另两篇作品

Oracle外部表ORACLE_DATAPUMP类型的创造语法详解:

Oracle外部表ORACLE_LOADEGL450类型的始建语法详解:

能够先看下测量检验的结果

使用SQL*LOADE景逸SUV注意多少个难点:

 

2.由询问结果集,使用Oracle_datapump来填充数据来扭转外界表

图片 2 

  • 字符编码
  • 字段分隔符
  • 行终止符
  • 日期或时刻格式
  • 特殊字符
  • 导入字段的次第
  • 导文件文件的表字段类型和尺寸是不是适当

归纳的导出例子1:

a.创立系统目录乃至Oracle数据目录名来营造对应提到,同一时候授予权限

$ mkdir -p /home/oracle/external_tb/data

create or replace directory data_dir as '/home/oracle/external_tb/data/';
grant read,write on directory data_dir to scott;

 

采纳sqlldr命令把多少导入到Oracle中。

图片 3

b.创立外界表

create table ex_tb1
            (ename,job,sal,dname)
            organization external
            (type oracle_datapump default directory data_dir location('ex_tb1'))
            parallel 1
            as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;

 

sqlldr user/"user_password" control=import-t1.ctl

figure-2

c.验证外界表

select * from ex_tb1;

ENAME                       JOB           SAL  DNAME
------------------------- -------------------- ---- -------------------------
CLARK                  MANAGER              2450 ACCOUNTING
KING                     PRESIDENT             5000 ACCOUNTING
MILLER                   CLERK                 1300 ACCOUNTING
JONES                    MANAGER               2975 RESEARCH
FORD                     ANALYST               3000 RESEARCH
ADAMS                    CLERK                 1100 RESEARCH
SMITH                    CLERK                  800 RESEARCH
SCOTT                    ANALYST               3000 RESEARCH
WARD                     SALESMAN              1250 SALES
TURNER                   SALESMAN              1500 SALES
ALLEN                    SALESMAN              1600 SALES
JAMES                    CLERK                  950 SALES
BLAKE                    MANAGER               2850 SALES
MARTIN                   SALESMAN              1250 SALES

14 rows selected.

对于使用上述方法创建的外部表能够将其复制到其余路径作为外部表的固有数据来生成新的表面表,用于转移数据。

00.起家测量试验情状

树立二个测量检验的条件,四个数据源数据库,版本为SQL Server
2010,八个指标数据库,版本为SQL Server 两千。

实施情形如下图所示,源数据库使用语句生成了100万的测量试验数据。

图片 4

 

图片 5成立测量检验表并转移100万的测验数据图片 6

  IF OBJECT_ID('DEMOTABLE') IS NOT NULL 
      DROP TABLE DEMOTABLE
  GO
  CREATE TABLE DEMOTABLE
      (
        COL1 VARCHAR(50) ,
        COL2 VARCHAR(50) ,
        COL3 VARCHAR(50)
      )
  INSERT  INTO DEMOTABLE
         SELECT TOP 1000000
                 NEWID() ,
                 NEWID() ,
                 NEWID()
         FROM    MASTER..SPT_VALUES T1
                 INNER JOIN MASTER..SPT_VALUES T2 ON 1 = 1
                 INNER JOIN MASTER..SPT_VALUES T3 ON 1 = 1

 

暗中认可下,生成的日记文件在当前目录下。无论成功与否,绝对要翻看日志。看看是否导入成功或失利,或是部分成功。导入的标题常常从日记文件就可以找到。

 

d.将表面表文件复制三个新的文本名,用以模拟到别的服务器上

$ cp /home/oracle/external_tb/data/ex_tb1 /home/oracle/external_tb/data/in_tb1

 01.使用SQL Server Import and Export Tool

应用SQL Server Import and Export
Tool进行数据的导出,也能够在指标数据库端使用Import实行导入,那有些套件也是SSIS的一部分。

在源数据库上右键,选用Task -> Export Data

图片 7

分别填写源数据库和指标数据库的连日音信。

图片 8

 

图片 9

 

选择“copy data from one or more tables or views”

慎选供给导数据的表,况且能够编写制定列的Mapping关系。

图片 10

能够采取及时实行只怕存款和储蓄为SSIS的包,用于实践安顿等其余用途。

这里我们挑选立时施行。

图片 11

留意导入的时候假若遇上如下的失实

Error
0xc02020f4: Data Flow Task: The column “Tel” cannot be processed because
more than one code page (936 and 1252) are specified for it.
(SQL
Server Import and Export Wizard)

是因为两侧的数据库的Collation设置分裂样导致的,须要设置同样的Collation。

  • 用时约1分30秒

例如有不当,还有或者会变动与导入文本同名的t1.bad文件。

归纳的导出例子2:

e. 新建表,将上述外界表的数额导入到新表中

create table in_tb1
            (ename varchar2(10),job varchar2(9),sal number(7,2),dname varchar(14))
            organization external
            (type oracle_datapump default directory data_dir location('in_tb1'));

02.使用Generate Scripts生成脚本

在源数据库上右键,选拔Task
-> Geneate Scripts…

图片 12

计划相关消息,注意接纳数据库的版本并将Script
Data设置成True。

图片 13

这里要求专心,因为有100万的多寡,所以导出的SQL文件就有400多M,所以用SQL
Server Management Studio是打不开的。

为此不得不选取sqlcmd实行。

图片 14sqlcmd语句 

C:\>sqlcmd -i export.sql -d ExportDataDemo_Destination -s 192.168.21.165 -U sa -P 1234567890

用时约28分钟

 

以下是日记文件,展现数据导入的一对音信。成功导入了18495032行记录,未有导入失利的笔录。

图片 15

f.验证新外界表的多寡

select * from in_tb1;

ENAME                       JOB           SAL  DNAME
------------------------- -------------------- ---- -------------------------
CLARK                  MANAGER              2450 ACCOUNTING
KING                     PRESIDENT             5000 ACCOUNTING
MILLER                   CLERK                 1300 ACCOUNTING
JONES                    MANAGER               2975 RESEARCH
FORD                     ANALYST               3000 RESEARCH
ADAMS                    CLERK                 1100 RESEARCH
SMITH                    CLERK                  800 RESEARCH
SCOTT                    ANALYST               3000 RESEARCH
WARD                     SALESMAN              1250 SALES
TURNER                   SALESMAN              1500 SALES
ALLEN                    SALESMAN              1600 SALES
JAMES                    CLERK                  950 SALES
BLAKE                    MANAGER               2850 SALES
MARTIN                   SALESMAN              1250 SALES

14 rows selected.

 03.使用BCP进行导出导入

在品味了近期四个效用低下的工具之后,大家到底开头尝试下SQL
Server中特别用于导数据的工具:BCP。

至于BCP的详细用法能够参见MSDN的扶植文书档案。

大家先利用BCP导出多少。

图片 16

-U和-P前边分别为数据库的客商名和密码。

图片 17

咱俩能够看出100万的数额导出仅用了1.8秒。

于今大家再利用BCP实行导入。

图片 18

推行后发觉,导入数据应用了20.8秒,依然异常快的。

图片 19

  • 用时1.872秒+20.810秒=22.682秒
  •  
[oracle@ttoracle /data/import]$ cat import-t1.log 

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 12:46:09 2018

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

Control File:   import-t1.ctl
Character Set ZHS16GBK specified for all input.

Data File:      /data/import/t1.txt
  File processing option string: "str '
'"
  Bad File:     t1.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 SCOTT.T1, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CARID                               FIRST     *           CHARACTER            
    Terminator string : '||'
ADDTIME                              NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
LASTSEARCHTIME                       NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
UPDATETIME                           NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
CARTYPE                              NEXT     *           CHARACTER            
    Terminator string : '||'
USERTELEPHONE                        NEXT     *           CHARACTER            
    Terminator string : '||'
ISCORRECT                            NEXT     *           CHARACTER            
    Terminator string : '||'
USERID                               NEXT     *           CHARACTER            
    Terminator string : '||'
VALIDFLAG                            NEXT     *           CHARACTER            
    Terminator string : '||'
CHANNEL                              NEXT     *           CHARACTER            
    Terminator string : '||'
CARCODE                              NEXT     *           CHARACTER            
    Terminator string : '||'
ENGINENUMBER                         NEXT     *           CHARACTER            
    Terminator string : '||'
CARNUMBER                            NEXT     *           CHARACTER            
    Terminator string : '||'


Table SCOTT.T1:
  18495032 Rows successfully loaded.
  0 Rows 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:                 214656 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:      18495032
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Jun 15 12:46:09 2018
Run ended on Fri Jun 15 12:55:58 2018

Elapsed time was:     00:09:48.90
CPU time was:         00:03:37.62

figure-3

g.创设健康的表,将表面表数据导入,那正是应用ORACLE_DATAPUMP类型的额外界表实现多少迁移

create table tb1 as select * from in_tb1;

 04.使用SqlBulkCopy

.NET Framework
2.0中加进的SqlBulkCopy类能够开展赶快的数目迁移动作,那也为代码实现多少迁移提供了接口。

与此同偶尔间SqlBulkCopy类提供了改造字段Mapping关系的诀窍ColumnMappings。

图片 20图片 21 使用SqlBulkCopy类举行数据迁移

  using System;
  using System.Data;
  using System.Data.SqlClient;

  namespace BulkInsert
  {
      static class Program
      {
          static void Main()
         {
             DateTime dateTimeStart = DateTime.Now;
             Console.WriteLine("Start Insert:" + dateTimeStart.ToString("HH:mm:ss fff"));
             //导入导出的数据库连接
             SqlConnection connectionDestination = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Destination;");
             SqlConnection connectionSource = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Source;");

             //实例化一个SqlBulkCopy
             var bulker = new SqlBulkCopy(connectionDestination) { DestinationTableName = "DEMOTABLE", BulkCopyTimeout = 600 };

             //获取源数据库的数据
             SqlCommand sqlcmd = new SqlCommand("SELECT * FROM DEMOTABLE", connectionSource);
             SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlcmd);
             DataTable dataTableSource = new DataTable();
             sqlDataAdapter.Fill(dataTableSource);

             //可以重新定义字段的Mapping关系
             //SqlBulkCopyColumnMapping sqlBulkCopyColumnMapping = new SqlBulkCopyColumnMapping("COL1", "NEW_COL1");
             //bulker.ColumnMappings.Add(sqlBulkCopyColumnMapping);
             connectionDestination.Open();
             bulker.WriteToServer(dataTableSource);
             bulker.Close();
             DateTime dateTimeEnd = DateTime.Now;
             Console.WriteLine("Insert Ending:" + dateTimeEnd.ToString("HH:mm:ss fff"));
         }
     }
 }

执行后

图片 22

  • 用时14.8秒

 

利用平面文件迁移数据,最大麻烦是就是特殊字符,或是有破烂数据。假设原数据包罗与字符分隔符一样的字符,如这里面的“||”,或是有一部分不可以预知的字符,如回车,换行符,等。那些字符会变成导入时,分割字段错位,导致导入错误,数据导不全,乃至导入战败。

 

3.选择外界文件数量,使用oracle_loader来填充数据来扭转外界表

05.行使Linked Server实行多少迁移

先在源数据库上对目的数据库建构Linked
Server,可能反过来也行。 

图片 23图片 24建立Linked Server

 EXEC sp_addlinkedserver @server = 'LinkedServerToDemo',
     @srvproduct = 'Export Data Testing', @provider = 'MSDASQL',
     @provstr = 'DRIVER={SQL Server};SERVER=192.168.21.165;UID=sa;PWD=password;'

图片 25图片 26是用INSERT INTO…SELECT…进行导入

  DECLARE @begin_date DATETIME
  DECLARE @end_date DATETIME
  SELECT  @begin_date = GETDATE()

  INSERT  INTO LinkedServerToDemo.ExportDataDemo_Destination.dbo.DEMOTABLE
          SELECT  *
          FROM    ExportDataDemo_Source.dbo.DEMOTABLE

  SELECT  @end_date = GETDATE()
 SELECT  DATEDIFF(ms, @begin_date, @end_date) AS '用时/毫秒' 

实行用时

图片 27

  • 用时7.97分钟

 

但从导出导入的速度来讲,是最快的,平面文件能够跨差别的数据库进行搬迁。要是数量不容忍遗失,只好通过工具来导了,但速度会相对异常慢。

在SSMS上同期也足以实行:

 a.策画外界数据源文件

cat /home/oracle/external_tb/data/1.txt
"7369","SMITH","CLERK","7902","17-DEC-80","100","0","20"
"7499","ALLEN","SALESMAN","7698","20-FEB-81","250","0","30"
"7521","WARD","SALESMAN","7698","22-FEB-81","450","0","30"
"7566","JONES","MANAGER","7839","02-APR-81","1150","0","20"

$ cat /home/oracle/external_tb/data/2.txt
"7654","MARTIN","SALESMAN","7698","28-SEP-81","1250","0","30"
"7698","BLAKE","MANAGER","7839","01-MAY-81","1550","0","30"
"7934","MILLER","CLERK","7782","23-JAN-82","3500","0","10"

06.用到RedGate的SQL Data Compare举办数量迁移

其三方的工具,有数据库结构相比较的工具SQL Compare和数量相比工具SQL Data
Compare。

图片 28

执行

图片 29

因为也是生成INSERT的SQL推行的,所以就不做过多相比了,上边已经测验过了。

 

 

EXEC [master]..xp_cmdshell
'BCP TestDB_2005.dbo.T1 out E:\T1_02.txt -c -T'
GO

b.成立外界表

create table emp_new(
                    emp_id number(4),
                    ename varchar2(15),
                    job varchar2(12),
                    mgr_id number(4),
                    hiredate date,
                    salary number(8),
                    comm number(8),
                    dept_id number(2)
                    )
            organization external
                    (
                    type oracle_loader
                    default directory data_dir
                    access parameters(
                                    records delimited by newline
                                    badfile 'emp_new%a_%p.bad'
                                    logfile 'emp_new%a_%p.log'
                                    fields terminated by ','
                                    optionally enclosed by '"'
                                    lrtrim missing field values are null
                                    reject rows with all null fields
                                    )
                    location ('1.txt','2.txt')
)
parallel 
reject limit unlimited;

07.结果比较

因为此处测验的蒙受有互连网和表结构的奇怪情状,不能印证全体情况下效果的间隔,可是也可视作参考之用。

下边给出相比结实。

 图片 30

code-1

c.验证外界表

select * from emp_new;

EMP_ID ENAME      JOB              MGR_ID    HIREDATE            SALARY     COMM       DEPT_ID
------ ---------- --------------- ---------- ------------------- ---------- ---------- ----------
  7654 MARTIN     SALESMAN        7698       1981-09-28 00:00:00 1250       0           30
  7698 BLAKE      MANAGER         7839       1981-05-01 00:00:00 1550       0           30
  7934 MILLER     CLERK           7782       1982-01-23 00:00:00 3500       0           10
  7369 SMITH      CLERK           7902       1980-12-17 00:00:00 100        0           20
  7499 ALLEN      SALESMAN        7698       1981-02-20 00:00:00 250        0           30
  7521 WARD       SALESMAN        7698       1981-02-22 00:00:00 450        0           30
  7566 JONES      MANAGER         7839       1981-04-02 00:00:00 1150       0           20

7 rows selected.

 

 4.外表表相关视图

图片 31

a.查看外表表消息

select TABLE_NAME,TYPE_NAME,DEFAULT_DIRECTORY_NAME,REJECT_LIMIT,ACCESS_PARAMETERS from user_external_tables;

 

figure-4

b.得到平面文件的职位

select * from user_external_locations order by table_name;

TABLE_NAME LOCATION   DIRECTORY DIRECTORY_NAME
---------- ---------- --------- --------------------
EMP_NEW    1.txt      SYS       DATA_DIR
EMP_NEW    2.txt      SYS       DATA_DIR
EX_TB1     ex_tb1     SYS       DATA_DIR
IN_TB1     in_tb1     SYS       DATA_DIR

 

 

外表表定义的多少个首要 

 

1.O凯雷德GANIZATION EXTE大切诺基NAL要害字,必供给有。以注脚定义的表为外界表。

EXEC [master]..xp_cmdshell
'BCP "SELECT * FROM TestDB_2005.dbo.T1" queryout E:\T1_03.txt -c -T'
GO

2..器重参数外界表的种类

ORACLE_LOADE卡宴:定义外部表的缺省格局,只能只读格局贯彻公文数据的装载。
ORACLE_DATAPUMP:援助对数据的装载与卸载,数据文件必需为二进制dump文件。可以从表面表提取数额装载到中间表,也得以从里头表卸载数据作为二进制文件填充到外部表。

code-2

3.DEFAULT DIRECTO哈弗Y:缺省的目录指明了表面文件所在的路子

 

 

4.LOCATION:定义了外界表的职位

图片 32

5.ACCESS PARAMETEEscortS:描述如何对表面表打开拜候

RECO普拉多DS关键字后定义怎么样分辨数据行  
DELIMITED BY
‘XXX’——换行符,常用newline定义换行,并指明字符集。对于特种的字符则要求单独定义,如特殊符号,能够使用OX’十三个人值’,举例tab(/t)的十五位是9,则DELIMITEDBY0X’09’;
cr(/r)的十四位是d,那么正是DELIMITEDBY0X’0D’。
SKIP X ——跳过X行数据,某些公文中首先行是列名,必要跳过第一行,则运用SKIP
1。
FIELDS关键字后定义如何辨别字段,常用的如下:
FIELDS:TERMINATED BY ‘x’——字段分割符。
ENCLOSED BY ‘x’——字段引用符,包涵在那标识内的数目都算作二个字段。
比方一行数据格式如:”abc”,”a””b,””c,”。使用参数TERMINATED BY ‘,’
ENCLOSED BY
‘”‘后,系统会读到五个字段,第三个字段的值是abc,首个字段值是a”b,”c,。
LRTLacrosseIM ——删除首尾空白字符。
MISSING FIELD VALUES ARE NULL——有个别字段空缺值都设为NULL。
对于字段长度和分割符不鲜明且策画作为外界表文件,可以运用UltraEdit、Editplus等来开展解析测量试验,即便文件相当大,则须要思虑将文件分割成小文件并从当中提取数额举行测量试验。

figure-5

表面表对错误的管理 

REJECT LIMIT UNLIMITED
在开立外界表时最终步入LIMIT子句,表示能够允许错误的产生个数。暗中同意值为零。设定为UNLIMITED则错误不受限制
BADFILE和NOBADFILE子句
用来钦点将捕获到的转变错误存放到哪些文件。倘使钦赐了NOBADFILE则意味着忽略调换期间的不当
万一未内定该参数,则系统活动在源目录下转移与表面表同名的.BAD文件BADFILE记录此番操作的结果,后一次将会被掩瞒LOGFILE和NOLOGFILE子句
同样在access parameters中加入LOGFILE
‘LOG_FILE.log’子句,则兼具Oracle的错误消息放入’LOG_FILE.log’中
而NOLOGFILE子句则代表不记录错误消息到log中,如忽视该子句,系统自动在源目录下转移与外表表同名的.LOG文件
专一以下多少个布满的标题
1.外界表平常蒙受BUFFEWrangler不足的景观,由此尽可能的增大READSIZE
2.换行符不对产生的难题。在分裂的操作系统中换行符的象征方法不平等,碰到错误日志提醒如是换行符难点,能够应用
UltraEdit张开,间接看十六进制
3.特定行报错开上下班时间,查看带有”BAD”的日记文件,在那之中保存了失误的多少,用记事本张开看看这里出错,是不是留存于外界表定义相矛盾

 

外表表的受制性 

1.SQLLDCR-V方可钦赐多少提交一遍,即ROWS=?,
外界表却未有,那对于大数据量的导入某些不方例。
2.sqlldr errors象征同意错误的行数,外界表用REJECT LIMIT
UNLIMITED,那些成效上基本同样。
3.外界表的列不能够钦点为not nullable,那样就很难拒绝某列为空值的记录。
4.外表表不能够使用continueif ,假设记录有换行的就相比难管理。

 

从个人来说,小编更欣赏使用第三种跟queryout挑选一同利用的写法,因为如此能够更灵敏决定要导出的数额。假若推行BCP命令碰着那样的一无所能提醒:

相关文章