因此在外部表不能够执行DML操作,可以先看下测试的结果

如果导出时还需要做一些数据的处理,使用BCP合适导出大容量数据,下面介绍大容量数据导出导入的利器——BCP实用工具,BCP实用工具可以将大量新行导入 SQL Server,如文本文件或者其他类型的表可以作为外部表,外部表,05.使用Linked Server进行数据迁移,可以先看下测试的结果,或将表数据导出到数据文件,bcp

图片 37

从SQLServer导数据到Oracle大概有以下几种方法:

前言

外部表概述

外部表只能在Oracle
9i之后来使用。简单地说,外部表,是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据,我们可以把一个操作系统文件当成一个只读的数据库表,就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。

工作中有段时间常常涉及到不同版本的数据库间导出导入数据的问题,索性整理一下,并简单比较下性能,有所遗漏的方法也欢迎讨论、补充。

本文转自:

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

SQL
SERVER提供多种不同的数据导出导入的工具,也可以编写SQL脚本,使用存储过程,生成所需的数据文件,甚至可以生成包含SQL语句和数据的脚本文件。各有优缺点,以适用不同的需求。下面介绍大容量数据导出导入的利器——BCP实用工具。同时在后面也介绍BULK
INSERT导入大容量数据,以及BCP结合BULK
INSERT做数据接口的实践(在SQL2008R2上实践)。

外部表的特性 

位于文件系统之中,按一定格式分割,如文本文件或者其他类型的表可以作为外部表。
对外部表的访问可以通过SQL语句来完成,而不需要先将外部表中的数据装载进数据库中。
外部数据表都是只读的,因此在外部表不能够执行DML操作,也不能创建索引。
ANALYZE语句不支持采集外部表的统计数据,应该使用DMBS_STATS包来采集外部表的统计数据。

00.建立测试环境

bcp 实用工具可以在 Microsoft SQL Server
实例和用户指定格式的数据文件间大容量复制数据。  使用
bcp 实用工具可以将大量新行导入 SQL Server
表,或将表数据导出到数据文件。 除非与
queryout 选项一起使用,否则使用该实用工具不需要了解 Transact-SQL
知识。 若要将数据导入表中,必须使用为该表创建的格式文件,或者必须了解表的结构以及对于该表中的列有效的数据类型。

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

 

创建外部表的注意事项 

01.使用SQL Server Import and Export Tool

BCP的所有参数:

使用BCP合适导出大容量数据。这里导出千万级别的数据,也是很快就能成功。

1. BCP的用法

1.需要先建立目录对象

在建立对象的时候,需要小心,Oracle数据库系统不会去确认这个目录是否真的存在。如果在输入这个目录对象的时候,不小心把路径写错了,那可能这个外
部表仍然可以正常建立,但是却无法查询到数据。由于建立目录对象时,缺乏这种自我检查的机制,为此在将路径赋予给这个目录对象时,需要特别的注意。另外需
要注意的是路径的大小写。在Windows操作系统中,其路径是不区分大小写的。而在Linux操作系统,这个路径需要区分大小写。故在不同的操作系统
中,建立目录对象时需要注意这个大小写的差异

02.使用Generate Scripts

图片 1

如果导出时还需要做一些数据的处理,比如多表关联,字符处理等,比较复杂的逻辑,最好是做成存储过程,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;

图片 2

3.在建立临时表时的相关限制

对表中字段的名称存在特殊字符的情况下,必须使用英文状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。
对于列名字中特殊符号未采用双引号括起来时,会导致无法正常查询数据。
建议不用使用特殊的列标题字符
在创建外部表的时候,并没有在数据库中创建表,也不会为外部表分配任何的存储空间。
创建外部表只是在数据字典中创建了外部表的元数据,以便对应访问外部表中的数据,而不在数据库中存储外部表的数据。
简单地说,数据库存储的只是与外部文件的一种对应关系,如字段与字段的对应关系。而没有存储实际的数据。
由于存储实际数据,故无法为外部表创建索引,同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。

04.使用SqlBulkCopy

BCP可以执行的4种操作

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

figure-1

4.删除外部表或者目录对象

一般情况下,先删除外部表,然后再删除目录对象,如果目录对象中有多个表,应删除所有表之后再删除目录对象。
如果在未删除外部表的情况下,强制删除了目录,在查询到被删除的外部表时,将收到”对象不存在”的错误信息。
查询dba_external_locations来获得当前所有的目录对象以及相关的外部表,同时会给出这些外部表所对应的操作系统文件的名字。 如果只是在数据库层面上删除外部表,并不会自动删除操作系统上的外部表文件。

05.使用Linked Server进行数据迁移

(1) 导入 这个动作使用in命令完成,后面跟需要导入的文件名。 (2)
导出
这个动作使用out命令完成,后面跟需要导出的文件名。 (3)
使用SQL语句导出

这个动作使用queryout命令完成,它跟out类似,只是数据源不是表或视图名,而是SQL语句。
(4) 导出格式文件 这个动作使用format命令完成,后而跟格式文件名。

使用Oracle的SQL*LOADER导入平面文件。假如Oracle中有已经创建好的表,与导入文件对应。

 

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

不同的操作系统对于外部表有不同的解释和显示方式
如在Linux操作系统中创建的文件是分号分隔且每行一条记录,但该文件在Windows操作系统上打开则并非如此。
建议避免不同操作系统以及不同字符集所带来的影响

06.使用RedGate的SQL Data Compare

常用操作:

把以下的内容用vi,写到import-t1.ctl

语法:

创建外部表 

使用CREATE TABLE语句的ORGANIZATION
EXTENERAL子句来创建外部表。外部表不分配任何盘区,因为仅仅是在数据字典中创建元数据。

07.结果对比

  1. BCP 可信任连接到本地表导出:
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_LOADER类型的创建语法详解:

可以先看下测试的结果

图片 3

使用SQL*LOADER注意几个问题:

 

2.由查询结果集,使用Oracle_datapump来填充数据来生成外部表

图片 4 

用windows认证登陆并把text.dbo.name的表导出到D盘的 t_001.txt

  • 字符编码
  • 字段分隔符
  • 行结束符
  • 日期或时间格式
  • 特殊字符
  • 导入字段的顺序
  • 导文件文件的表字段类型和长度是否合适

简单的导出例子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;

 

  1. BCP 可信任连接到导出查询

使用sqlldr命令把数据导入到Oracle中。

图片 5

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;

 

图片 6

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
2008,一个目标数据库,版本为SQL Server 2000。

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

图片 7

 

图片 8建立测试表并生成100万的测试数据图片 9

  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

 

  1. BCP 可信任连接连到远程服务器导出查询

默认下,生成的日志文件在当前目录下。无论成功与否,一定要查看日志。看看是否导入成功或失败,或是部分成功。导入的问题一般从日志文件即可找到。

 

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

图片 10

分别填写源数据库和目标数据库的连接信息。

图片 11

 

图片 12

 

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

选择需要导数据的表,并且可以编辑列的Mapping关系。

图片 13

可以选择立即执行或者存储为SSIS的包,用于执行计划等其他用途。

这里我们选择立即执行。

图片 14

注意导入的时候如果遇到如下的错误

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秒

图片 15

如果有错误,还会生成与导入文件同名的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…

图片 16

配置相关信息,注意选择数据库的版本并将Script
Data设置成True。

图片 17

这里需要注意,因为有100万的数据,所以导出的SQL文件就有400多M,所以用SQL
Server Management Studio是打不开的。

所以只能使用sqlcmd执行。

图片 18sqlcmd语句 

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

用时约28分钟

 

也可以使用下面的T-SQL命令:

以下是日志文件,显示数据导入的一些信息。成功导入了18495032行记录,没有导入失败的记录。

图片 19

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导出数据。

图片 20

-U和-P后面分别为数据库的用户名和密码。

图片 21

我们可以看到100万的数据导出仅用了1.8秒。

现在我们再使用BCP进行导入。

图片 22

执行后发现,导入数据使用了20.8秒,还是很快的。

图片 23

  • 用时1.872秒+20.810秒=22.682秒
  •  
EXEC master..xp_cmdshell 'BCP  test.dbo.name out d:\t_002.txt -c -t -T'

EXEC master..xp_cmdshell 'BCP  "select  name from test.dbo.name" queryout d:\t_004.txt -c -t -T'

EXEC master..xp_cmdshell 'BCP tran_test.dbo.uptrans out d:\t_006.txt -c -t -S HOUYAJUN\JHIDCDBS005 -T'
[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。

图片 24图片 25 使用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"));
         }
     }
 }

执行后

图片 26

  • 用时14.8秒

 

在执行上述命令的时候可能会报错 : 错误提示:消息 15281,级别 16,状态
1,过程 xp_cmdshell,第 1 行

使用平面文件迁移数据,最大麻烦是就是特殊字符,或是有垃圾数据。如果原数据包含与字符分隔符相同的字符,如这里面的“||”,或是有一些不可见的字符,如回车,换行符,等。这些字符会造成导入时,分割字段错位,导致导入错误,数据导不全,甚至导入失败。

 

3.使用外部文件数据,使用oracle_loader来填充数据来生成外部表

05.使用Linked Server进行数据迁移

先在源数据库上对目标数据库建立Linked
Server,或者反过来也行。 

图片 27图片 28建立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;'

图片 29图片 30是用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 '用时/毫秒' 

执行用时

图片 31

  • 用时7.97分钟

 

SQL Server 阻止了对组件 ‘xp_cmdshell’ 的 过程 ‘sys.xp_cmdshell’
的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用
sp_configure 启用 ‘xp_cmdshell’。有关启用 ‘xp_cmdshell’
的详细信息,请参阅 SQL Server 联机丛书中的 “外围应用配置器”。

但从导出导入的速度来说,是最快的,平面文件可以跨不同的数据库进行迁移。如果数据不容忍丢失,只能通过工具来导了,但速度会相对较慢。

在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。

图片 32

执行

图片 33

因为也是生成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.结果对比

因为这里测试的环境有网络和表结构的特殊情况,不能说明所有情况下效能的差异,但是也可作为参考之用。

下面给出比较结果。

 图片 34

图片 35😉

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.
-- 允许配置高级选项  
EXEC master.sys.sp_configure 'show advanced options', 1  
-- 重新配置  
RECONFIGURE  
-- 启用xp_cmdshell  
EXEC master.sys.sp_configure 'xp_cmdshell', 1  
--重新配置  
RECONFIGURE  

 

 4.外部表相关视图

图片 36😉

图片 37

a.查看外部表信息

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

 

用完之后再把其关闭,关闭只需要把 1 变为 0 即可。
上面的语句也可以通过输入用户名和密码的形式出来,如下例句:

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

 

EXEC master..xp_cmdshell 'BCP  test.dbo.name out d:\t_008.txt -c -t  -U"sa" -P"ssssaaaa"'

 

外部表定义的几个重点 

  1. BCP的导入 只需要把上面的 OUT 变为 in 就可以了。

    EXEC master..xp_cmdshell ‘BCP test.dbo.name in d:\t_002.txt -c -t -U -T’

 

1.ORGANIZATION EXTERNAL关键字,必须要有。以表明定义的表为外部表。

这个是对刚才导出的数据进行重新的导入,可以很明显的看到表中的数据已经增加了。