Oracle外界表详解

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
SECRUISERVE奥迪Q3提供多样分化的数量导出导入的工具,也足以编写制定SQL脚本,使用存款和储蓄进程,生成所需的数据文件,以至足以变动包蕴SQL语句和数量的本子文件。各有优缺点,以适用不一致的必要。上面介绍大体积数据导出导入的利器——BCP实用工具。同不经常候在背后也介绍BULK
INSERT导入大体量数据,以致BCP结合BULK
INSERT做多少接口的施行(在SQL2009Evoque2上进行)。

外表表的表征 

位于文件系统之中,按自然格式分割,如文本文件或许另外品类的表能够当做外部表。
对外界表的拜望能够经过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*LOADE揽胜导入平面文件。假诺Oracle中有已经创办好的表,与导入文本对应。

 

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

差异的操作系统对于外界表有不一样的解说和显示格局
如在Linux操作系统中开创的文本是分号分隔且每行一条记下,但该公文在Windows操作系统上开发则并不是那样。
建议幸免分歧操作系统以至差别字符集所推动的熏陶

06.使用RedGate的SQL Data Compare

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

语法:

创立外界表 

选用CREATE TABLE语句的O昂科威GANIZATION
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_LOADE凯雷德类型的创办语法详解:

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

使用SQL*LOADE纳瓦拉注意多少个难题:

 

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
二〇〇八,叁个目的数据库,版本为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讴歌ZDXNAL要害字,须要求有。以标注定义的表为外界表。

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

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

ORACLE_LOADELacrosse:定义外界表的缺省情势,只好只读格局完毕公文数据的装载。
ORACLE_DATAPUMP:支持对数据的装载与卸载,数据文件必须为二进制dump文件。能够从表面表提取数据装载到中间表,也得以从当中间表卸载数据作为二进制文件填充到外界表。

code-2

3.DEFAULT DIRECTO奔驰G级Y:缺省的目录指明了表面文件所在的门径

 

 

4.LOCATION:定义了表面表的地点

澳门新萄京 32

5.ACCESS PARAMETERAV4S:描述怎么着对外表表打开拜见

RECO凯雷德DS关键字后定义如何识别数据行  
DELIMITED BY
‘XXX’——换行符,常用newline定义换行,并指明字符集。对于特种的字符则须要单独定义,如特殊符号,能够使用OX’十三人值’,比方tab(/t)的十三个人是9,则DELIMITEDBY0X’09’;
cr(/r)的16个人是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,。
LRTCR-VIM ——删除首尾空白字符。
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.表面表日常碰到BUFFE帕杰罗不足的景观,由此尽恐怕的增大READSIZE
2.换行符不对发生的难题。在差别的操作系统中换行符的代表方法不均等,碰着错误日志提示如是换行符难题,能够使用
UltraEdit展开,直接看十六进制
3.特定行报错开上下班时间,查看带有”BAD”的日记文件,在那之中保存了失误的多寡,用记事本张开看看那里出错,是还是不是留存于外界表定义相冲突

 

外表表的局限性 

1.SQLLD宝马X3方可钦赐多少提交一次,即ROWS=?,
外部表却未有,那对于大数据量的导入某些不方例。
2.sqlldr errors象征同意错误的行数,外界表用REJECT LIMIT
UNLIMITED,这一个效果上基本同样。
3.外界表的列不可能钦命为not nullable,这样就很难拒绝某列为空值的记录。
4.外界表无法动用continueif ,要是记录有换行的就相比较难处理。

 

从个人来说,笔者更赏识使用第三种跟queryout慎选一齐利用的写法,因为如此能够更进一竿灵敏决定要导出的数量。假若进行BCP命令遭逢那样的失实提醒:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图