金沙国际唯一官网网址SQLServer导数据到Oracle

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

金沙国际唯一官网网址 31
sqlldr user/"user_password" control=import-t1.ctl

在SQL Server Agent新建三个功课

1.OPAJEROGANIZATION EXTE卡宴NAL根本字,必须要有。以标记定义的表为外界表。

01.使用SQL Server Import and Export Tool

但从导出导入的快慢来讲,是最快的,平面文件可以跨不一样的数据库进行搬迁。如若数据不容忍错过,只好通过工具来导了,但速度会绝对很慢。

 

4.刨除却界表可能目录对象

平常意况下,先删除却界表,然后再删除目录对象,如若目录对象中有几个表,应除去全数表之后再删除目录对象。
假定在未删减外界表的意况下,强制删除了目录,在查询到被去除的表面表时,将收到”对象不设有”的错误新闻。
查询dba_external_locations来得到当前具备的目录对象以致有关的外表表,同一时间会付出那几个外界表所对应的操作系统文件的名字。 假使只是在数据库层面上剔除却界表,并不会活动删除操作系统上的外表表文件。

07.结出相比较

因为此处测验的条件有网络和表结构的出格景况,不可能注脚具备情状下效果的间距,然而也可看作参照之用。

上面给出比较结实。

 金沙国际唯一官网网址 1

以下使用第2种方式来扩充数据迁移的。

 

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;

06.使用RedGate的SQL Data Compare实行数量迁移

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

金沙国际唯一官网网址 2

执行

金沙国际唯一官网网址 3

因为也是生成INSERT的SQL实施的,所以就不做过多比较了,上边已经测量检验过了。

 

 

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;
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

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;

职业中有段时间常常提到到不一样版本的数据库间导出导入数据的难题,索性整理一下,并简短相比下品质,有所疏漏的法子也迎接探究、补充。

比如导出时还供给做一些数量的管理,举例多表关联,字符管理等,相比较复杂的逻辑,最佳是做成存款和储蓄进程,BCP直接调用存款和储蓄进度就可以。

3.1
数据导出导入自动化与数量接口

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

 

02.选用Generate Scripts生成脚本

在源数据库上右键,接纳Task
-> Geneate Scripts…

金沙国际唯一官网网址 4

配备相关音讯,注意采纳数据库的本子并将Script
Data设置成True。

金沙国际唯一官网网址 5

此间必要注意,因为有100万的数量,所以导出的SQL文件就有400多M,所以用SQL
Server Management Studio是打不开的。

为此只可以动用sqlcmd实行。

金沙国际唯一官网网址 6sqlcmd语句 

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

用时约28分钟

 

把以下的剧情用vi,写到import-t1.ctl

figure-12

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'));

能够先看下测验的结果

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

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

表面表对不当的管理 

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.外表表平时遇上BUFFE安德拉不足的景况,因而尽只怕的增大READSIZE
2.换行符不对暴发的主题材料。在分裂的操作系统中换行符的意味方法区别,遇到错误日志提醒如是换行符难题,能够应用
UltraEdit展开,直接看十六进制
3.特定行报错开上下班时间,查看带有”BAD”的日志文件,此中保存了失误的数目,用记事本展开看看这里出错,是还是不是存在于表面表定义相冲突

 

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  
)

 

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;

06.使用RedGate的SQL Data Compare

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

进程要花上几分钟的日子技能变成,请耐烦等待一下。关于数据的结构,能够参见作者的另风度翩翩篇博文:

3.DEFAULT DIRECTOLANDY:缺省的目录指明了表面文件所在的路径

 04.使用SqlBulkCopy

.NET Framework
2.0中追加的SqlBulkCopy类能够张开高效的数据迁移动作,那也为代码实现数据迁移提供了接口。

再者SqlBulkCopy类提供了修改字段Mapping关系的艺术ColumnMappings。

金沙国际唯一官网网址 7金沙国际唯一官网网址 8 使用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"));
         }
     }
 }

执行后

金沙国际唯一官网网址 9

  • 用时14.8秒

 

  1. 运用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle
    Provider for OLE DB连接受Oracle
  2. 导出到平面文件
  3. 导出包涵数据的SQL脚本。
  4. 使用ETL工具。
  5. 本人开荒软件。
database_name 指定的表或视图所在数据库的名称。如果未指定,则使用用户的默认数据库。
in | out| queryout | format
  • in 从文件复制到数据库表或视图。

  • out 从数据库表或视图复制到文件。如果指定了现有文件,则该文件将被覆盖。提取数据时,请注意 bcp 实用工具将空字符串表示为 null,而将 null 字符串表示为空字符串。

  • queryout 从查询中复制,仅当从查询大容量复制数据时才必须指定此选项。

  • format 根据指定的选项(-n-c-w-N)以及表或视图的分隔符创建格式化文件。大容量复制数据时,bcp 命令可以引用一个格式化文件,从而避免以交互方式重复输入格式信息。format 选项要求指定 -f 选项;创建 XML 格式化文件时还需要指定 -x 选项。

    in 从文件复制到数据库表或视图。
    out 从数据库表或视图复制到文件。如果指定了现有文件,则该文件将被覆盖。提取数据时,请注意 bcp 实用工具将空字符串表示为 null,而将 null 字符串表示为空字符串。
    queryout 从查询中复制,仅当从查询大容量复制数据时才必须指定此选项。

-c 使用字符数据类型执行该操作。此选项不提示输入每个字段;它使用 char 作为存储类型,不带前缀;使用 \t(制表符)作为字段分隔符,使用 \r\n(换行符)作为行终止符。
-w 使用 Unicode 字符执行大容量复制操作。此选项不提示输入每个字段;它使用 nchar 作为存储类型,不带前缀;使用 \t(制表符)作为字段分隔符,使用 \n(换行符)作为行终止符。
-tfield_term 指定字段终止符。默认值为 \t(制表符)。使用此参数可以替代默认字段终止符。
-rrow_term 指定行终止符。默认值为 \n(换行符)。使用此参数可替代默认行终止符。
-Sserver_name[ \instance_name] 指定要连接的 SQL Server 实例。如果未指定服务器,则 bcp 实用工具将连接到本地计算机上的默认 SQL Server 实例。如果从网络或本地命名实例上的远程计算机中运行 bcp 命令,则必须使用此选项。若要连接到服务器上的 SQL Server 默认实例,请仅指定 server_name。若要连接到 SQL Server 的命名实例,请指定 server_name\instance_name。
-Ulogin_id 指定用于连接到 SQL Server 的登录 ID。
-Ppassword 指定登录 ID 的密码。如果未使用此选项,bcp 命令将提示输入密码。如果在命令提示符的末尾使用此选项,但不提供密码,则 bcp 将使用默认密码 (NULL)。
-T 指定 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL Server。不需要网络用户的安全凭据、login_id 和 password。如果未指定 –T,则需要指定 –U–P 才能成功登录。

外部表定义的多少个关键 

00.确立测量试验情状

树立三个测量试验的情况,贰个数据源数据库,版本为SQL Server
二零零六,一个指标数据库,版本为SQL Server 3000。

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

金沙国际唯一官网网址 10

 

金沙国际唯一官网网址 11树立测验表并扭转100万的测验数据金沙国际唯一官网网址 12

  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

 

行使BCP合适导出大体积数据。这里导出千万等级的数量,也是快速就会不辱职责。

 

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.

 01.使用SQL Server Import and Export Tool

运用SQL Server Import and Export
Tool进行多少的导出,也足以在目的数据库端使用Import实行导入,那有的套件也是SSIS的风姿洒脱部分。

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

金沙国际唯一官网网址 13

个别填写源数据库和对象数据库的连接音信。

金沙国际唯一官网网址 14

 

金沙国际唯一官网网址 15

 

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

分选须求导数据的表,况且能够编写列的Mapping关系。

金沙国际唯一官网网址 16

能够选取马上实践大概存储为SSIS的包,用于推行计划等任何用途。

此地大家采纳及时施行。

金沙国际唯一官网网址 17

留意导入的时候若是遇上如下的错误

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秒

使用Oracle的SQL*LOADE奥迪Q3导入平面文件。假诺Oracle中有曾经创办好的表,与导入文本对应。

figure-7

创设外界表的瞩目事项 

07.结果比较

以下是日记文件,突显数据导入的局地音信。成功导入了18495032行记录,未有导入退步的记录。

 

2..根本参数外界表的品类

ORACLE_LOADECR-V:定义外界表的缺省格局,只可以只读格局贯彻公文数据的装载。
ORACLE_DATAPUMP:援助对数据的装载与卸载,数据文件必得为二进制dump文件。能够从外表表提取数据装载到里面表,也得以从里面表卸载数据作为二进制文件填充到外界表。

金沙国际唯一官网网址 18 

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

金沙国际唯一官网网址 19

外表表概述

外界表只好在Oracle
9i之后来利用。简单地说,外界表,是指不真实于数据库中的表。通过向Oracle提供描述外界表的元数据,大家能够把八个操作系统文件就是叁个只读的多少库表,就疑似这几个多少存款和储蓄在多个通常数据库表中一样来张开访谈。外界表是对数据库表的延长。

00.建设构造测量试验景况

默许下,生成的日记文件在当前目录下。无论成功与否,绝对要查阅日志。看看是或不是导入成功或倒闭,或是部分成功。导入的标题经常从日记文件就可以找到。

figure-3

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

不等的操作系统对于外界表有例外的解说和呈现格局
如在Linux操作系统中创制的公文是分号分隔且每行一条记下,但该公文在Windows操作系统上张开则并非那样。
提出制止差异操作系统以至不一致字符集所带来的熏陶

02.使用Generate Scripts

使用SQL*LOADERAV4注意多少个难点:

code-2

g.创设正常的表,将表面表数据导入,那正是运用ORACLE_DATAPUMP类型的额外界表完成多少迁移

create table tb1 as select * from in_tb1;

 

从SQLServer导数据到Oracle大概有以下两种方式:

figure-2

1.亟待先成立目录对象

在确立指标的时候,须求小心,Oracle数据库系统不会去肯定那么些目录是还是不是真正存在。借使在输入那么些目录对象的时候,十分大心把路子写错了,那大概这几个外部表依旧能够健康创建,不过却无能为力查询到多少。由于营造目录对象时,缺少这种自己检讨的机制,为此在将路线授予给这些目录对象时,须要极其的举世瞩目。别的须要留神的是路径的抑扬顿挫写。在Windows操作系统中,其路线是不区分朗朗上口写的。而在Linux操作系统,那一个门路要求区分抑扬顿挫写。故在分化的操作系统
中,创立目录对象时索要介意那一个尺寸写的分裂

03.使用BCP

如若有不当,还大概会转移与导入文本同名的t1.bad文件。

 

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.

对于利用上述方式创造的外界表能够将其复制到其余渠道作为外界表的原来数据来生成新的外表表,用于转移数据。

05.应用Linked Server进行多少迁移

先在源数据库上对指标数据库构造建设Linked
Server,或然反过来也行。 

金沙国际唯一官网网址 20金沙国际唯一官网网址 21建立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;'

金沙国际唯一官网网址 22金沙国际唯一官网网址 23是用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 '用时/毫秒' 

实行用时

金沙国际唯一官网网址 24

  • 用时7.97分钟

 

应用平面文件迁移数据,最大麻烦是就是特殊字符,或是有破烂数据。借使原数据包涵与字符分隔符同样的字符,如那中间的“||”,或是有风流洒脱部分不可以看到的字符,如回车,换行符,等。这个字符会形成导入时,分割字段错位,导致导入错误,数据导不全,以至导入败北。

figure-13

 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"

 03.使用BCP实行导出导入

在品味了日前五个功用低下的工具之后,大家毕竟最早尝试下SQL
Server中特意用来导数据的工具:BCP。

关于BCP的详尽用法能够参见MSDN的扶持文书档案。

大家先利用BCP导出多少。

金沙国际唯一官网网址 25

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

金沙国际唯一官网网址 26

咱俩得以看见100万的数目导出仅用了1.8秒。

当今大家再使用BCP进行导入。

金沙国际唯一官网网址 27

实施后发觉,导入数据运用了20.8秒,依旧相当的慢的。

金沙国际唯一官网网址 28

  • 用时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

金沙国际唯一官网网址 29

4.LOCATION:定义了外界表的地点

05.利用Linked Server实行数量迁移

 

3.运用外界文件数量,使用oracle_loader来填充数据来变化外界表

04.使用SqlBulkCopy

code-5

 4.表面表相关视图

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

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

金沙国际唯一官网网址 30

5.ACCESS PARAMETEENCORES:描述怎么着对表面表进行访谈

RECOEvoqueDS关键字后定义怎么样分辨数据行  
DELIMITED BY
‘XXX’——换行符,常用newline定义换行,并指明字符集。对于特种的字符则供给独自定义,如特殊符号,能够使用OX’16个人值’,举个例子tab(/t)的15个人是9,则DELIMITEDBY0X’09’;
cr(/r)的14个人是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,。
LRTMuranoIM ——删除首尾空白字符。
MISSING FIELD VALUES ARE NULL——某个字段空缺值都设为NULL。
对于字段长度和分割符不显著且筹算作为外界表文件,能够利用UltraEdit、Editplus等来扩充深入分析测量检验,倘诺文件相当的大,则必要考虑将文件分割成小文件并从当中提取数额开展测验。

figure-1

创立外界表 

接纳CREATE TABLE语句的O奥迪Q5GANIZATION
EXTENERAL子句来创建国门外界表。外部表不分红任何盘区,因为唯有是在数量字典中创制元数据。

SQL
SESportageVEEnclave提供三种分化的数目导出导入的工具,也足以编写SQL脚本,使用存款和储蓄进程,生成所需的数据文件,以致能够转移包涵SQL语句和数据的本子文件。各有优缺点,以适用区别的需求。下边介绍大容积数据导出导入的利器——BCP实用工具。同期在后头也介绍BULK
INSERT导入大体积数据,甚至BCP结合BULK
INSERT做多少接口的执行(在SQL二〇〇九Rubicon2上试行)。

a.查看表面表新闻

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

 

在SSMS上同期也得以实行:

3.在创制一时表时的相关范围

对表中字段的称谓存在特殊字符的动静下,必得选拔匈牙利(Hungary)语状态的下的双引号将该表列名称连接起来。如应用”SalseID#”。
对此列名字中特殊符号未采用双引号括起来时,会导致不能够平常查询数据。
建议不用采取异乎常常的列标题字符
在开创外界表的时候,并不以往在数据库中创造表,也不会为外界表分配任何的囤积空间。
创造外界表只是在数额字典中开创了外界表的元数据,以便对应访谈外界表中的数码,而不在数据库中存款和储蓄外界表的数额。
一言以蔽之地说,数据仓库储存储的只是与外表文件的活龙活现种对应涉及,如字段与字段的照顾关系。而从不存款和储蓄实际的数码。
鉴于存款和储蓄实际多少,故无法为外界表成立索引,同期在数码选择DML时也不援救对外界表的插入、更新、删除等操作。

figure-11

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

创制外界表时,必得钦命操作系统文件所选拔的相间符号。何况该分隔符有且唯有叁个。创立外界表时,不能够含有标题列。要是这几个题目消息与外表表的字段类型不雷同(如字段内容是number数据类型,而标题音信则是字符型数据,则在查询时就能出错)。纵然数据类型凑巧黄金年代致的话,这么些标题消息Oracle数据库也会作为普通记录来相比。

当Oracle数据库系统访谈这么些操作系统文件的时候,会在此个文件所在的目录自动创立一个日志文件。无论最终是或不是访谈成功,那么些日志文件都会如期营造。查看这几个日志文件,能够了然数据库访谈外界表的功能、是或不是中标访谈等等。私下认可情况下,该日志在与外表表的如出蒸蒸日上辙directory下发出。

figure-10

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

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

金沙国际唯一官网网址 31

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.

这里不行使格式化文件举行导出导入的演示了。详细介绍与应用,请参见联机丛书。

外界表的特征 

投身文件系统之中,按自然格式分割,如文本文件可能此外门类的表能够视作外界表。
对外界表的拜望能够由此SQL语句来达成,而无需先将表面表中的数额装载进数据库中。
外表数据表都以只读的,因而在外部表不可以看到实施DML操作,也无法创设索引。
ANALYZE语句不扶持搜集外界表的计算数据,应该运用DMBS_STATS包来采摘外界表的总计数据。

 

外界表的受制性 

1.SQLLDDisco Volante得以钦定多少提交三次,即ROWS=?,
外界表却未有,那对于大数据量的导入有个别不方例。
2.sqlldr errors象征同意错误的行数,外界表用REJECT LIMIT
UNLIMITED,这么些效应上基本同样。
3.外界表的列不能够钦赐为not nullable,那样就很难拒绝某列为空值的笔录。
4.外界表不可能选取continueif ,如若记录有换行的就比较难管理。

 

code-1

1.外界表的创立语法

createtabletable_name
           (col1 datatype1,col2 datatype2,col3 datatype3)
            organization exteneral
           (…..)
详见语法可参见我的另两篇文章

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

Oracle外部表ORACLE_LOADE君越类型的创办语法详解: