`

Oracle创建外部表,再由外部表创建实体表

阅读更多

外部表:

       这些表并不存储在数据库本身中,而是放在数据库之外,即放在平常的操作系统文件中。在Oracle9i
及以上版本中,利用外部表可以查询数据库之外的一个文件,就好像这个文件也是数据库中平常的表一样。外部表对于向数据
库加载数据最有用(外部表是非常强大的数据加载工具)

 

  • 创建的语法类似于: "CREATE TABLE ... ORGANIZATION EXTERNAL"
  • 数据在数据库的外部组织,是操作系统文件。
  • 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。      
  • 数据是只读的。(外部表相当于一个临时的只读的虚表)
  • 不可以在上面运行任何 DML 操作,不可以创建索引。   
  • 可以查询操作和连接。可以并行操作。

例子:

1、有以下文件demo.txt:

 

 

 

20100830000000,0830,10086,01,0551,40,2,11,100,20100830000005,11,
20100830000000,0830,10086,01,0551,40,2,11,100,20100830000008,11,
20100830000000,0830,10086,08,0558,32,3,11,100,20100830000009,11,
20100830000000,0830,10086,17,0567,32,3,11,100,20100830000313,11,
20100830000001,0830,10086,01,0551,32,3,1911,100,201008300000,11,
20100830000001,0830,10086,01,0551,32,3,11,100,20100830000011,11,
20100830000001,0830,10086,08,0558,32,3,11,100,20100830000013,11,
20100830000001,0830,10086,08,0558,32,3,11,100,20100830000015,11,
20100830000001,0830,10086,03,0553,40,2,11,100,20100830000021,11,
20100830000001,0830,10086,03,0553,40,2,10,80,20100830000145,11,
20100830000001,0830,10086,08,0558,32,3,11,100,20100830000306,11,
20100830000001,0830,10086,11,0561,32,3,11,100,20100830000311,11,
20100830000001,0830,10086,07,0557,32,3,11,100,20100830000313,11,
20100830000001,0830,10086,14,0564,52,1,12,100,20100830000328,12,
20100830000002,0830,10086,10,0550,40,2,11,100,20100830000006,11,
20100830000002,0830,10086,14,0564,40,2,11,100,20100830000007,11,
20100830000002,0830,10086,10,0550,40,2,11,100,20100830000011,11,
20100830000002,0830,10086,01,0551,40,2,10,80,20100830000017,10, 

  

2、创建目录,并赋予权限

SQL> CREATE DIRECTORY testdir AS 'd:\temp\'; 
Directory created 
SQL> GRANT ALL ON DIRECTORY testdir TO scott; 
Grant succeeded 

 

注意:接着把student.txt文件拷贝到d:\temp下面

 

 

3、创建外部表

 

CREATE TABLE demo_ext( 
		LogDate VARCHAR2(20), 
		PartID CHAR(4), 
		CalleeNo VARCHAR2(20), 
		CityID VARCHAR2(10), 
		CityCode VARCHAR2(5), 
		UserGrade NUMBER(4), 
		UserType NUMBER(4), 
		OperateTrace VARCHAR2(30), 
		OperateResult NUMBER(4), 
		PressDate VARCHAR2(20), 
		KeyTrace VARCHAR2(10), 
		ExitDate VARCHAR2(20) 
) 
ORGANIZATION EXTERNAL( 
	TYPE ORACLE_LOADER 
	DEFAULT DIRECTORY testdir 
	ACCESS PARAMETERS ( 
	RECORDS DELIMITED BY NEWLINE 
	BADFILE 'bad.txt' 
	LOGFILE 'log.txt' 
	READSIZE 10485760 
	FIELDS TERMINATED BY ',' 
	LDRTRIM 
	MISSING FIELD VALUES ARE NULL 
	REJECT ROWS WITH ALL NULL FIELDS 
	(LogDate,PartID,CalleeNo,CityID,CityCode,UserGrade,UserType,OperateTrace,OperateResult,PressDate,KeyTrace,ExitDate) 
) 
	LOCATION('demo.txt') 
)reject limit unlimited 
/

 

 

 

 

 

 

3、查看外部表

SQL> SELECT * FROM demo_ext;
LOGDATE PARTID CALLEENO CITYID CITYCODE USERGRADE USERTYPE OPERATETRACE OPERATERESULT PRESSDATE KEYTRACE EXITDATE 
-------------------- ------ -------------------- ---------- -------- --------- -------- ------------------------------ ------------- -------------------- ---------- -------------------- 
20100830000000 0830 10086 01 0551 40 2 11 100 20100830000005 11 
20100830000000 0830 10086 01 0551 40 2 11 100 20100830000008 11 
20100830000000 0830 10086 08 0558 32 3 11 100 20100830000009 11 
20100830000000 0830 10086 17 0567 32 3 11 100 20100830000313 11 
20100830000001 0830 10086 01 0551 32 3 1911 100 201008300000 11 
20100830000001 0830 10086 01 0551 32 3 11 100 20100830000011 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000013 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000015 11 
20100830000001 0830 10086 03 0553 40 2 11 100 20100830000021 11 
20100830000001 0830 10086 03 0553 40 2 10 80  20100830000145 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000306 11 
20100830000001 0830 10086 11 0561 32 3 11 100 20100830000311 11 
20100830000001 0830 10086 07 0557 32 3 11 100 20100830000313 11 
20100830000001 0830 10086 14 0564 52 1 12 100 20100830000328 12 
20100830000002 0830 10086 10 0550 40 2 11 100 20100830000006 11 
20100830000002 0830 10086 14 0564 40 2 11 100 20100830000007 11 
20100830000002 0830 10086 10 0550 40 2 11 100 20100830000011 11 
20100830000002 0830 10086 01 0551 40 2 10 80  20100830000017 10 
18 rows selected 

 

 可见外部表已经创建成功!

 

接着用外部表的数据创建实体表数据:

1、创建实体表

CREATE TABLE t_demo( 
	LogDate VARCHAR2(20), 
	PartID CHAR(4), 
	CalleeNo VARCHAR2(20), 
	CityID VARCHAR2(10), 
	CityCode VARCHAR2(5), 
	UserGrade NUMBER(4), 
	UserType NUMBER(4), 
	OperateTrace VARCHAR2(30), 
	OperateResult NUMBER(4), 
	PressDate VARCHAR2(20), 
	KeyTrace VARCHAR2(10), 
	ExitDate VARCHAR2(20) 
) 
/ 

 

2、将外部表数据插入实体表

 

SQL> INSERT INTO t_demo SELECT * FROM demo_ext; 
18 rows inserted 

 

3、查看实体表

SQL> SELECT * FROM t_demo;
LOGDATE PARTID CALLEENO CITYID CITYCODE USERGRADE USERTYPE OPERATETRACE OPERATERESULT PRESSDATE KEYTRACE EXITDATE 
-------------------- ------ -------------------- ---------- -------- --------- -------- ------------------------------ ------------- -------------------- ---------- -------------------- 
20100830000000 0830 10086 01 0551 40 2 11 100 20100830000005 11 
20100830000000 0830 10086 01 0551 40 2 11 100 20100830000008 11 
20100830000000 0830 10086 08 0558 32 3 11 100 20100830000009 11 
20100830000000 0830 10086 17 0567 32 3 11 100 20100830000313 11 
20100830000001 0830 10086 01 0551 32 3 1911 100 201008300000 11 
20100830000001 0830 10086 01 0551 32 3 11 100 20100830000011 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000013 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000015 11 
20100830000001 0830 10086 03 0553 40 2 11 100 20100830000021 11 
20100830000001 0830 10086 03 0553 40 2 10 80  20100830000145 11 
20100830000001 0830 10086 08 0558 32 3 11 100 20100830000306 11 
20100830000001 0830 10086 11 0561 32 3 11 100 20100830000311 11 
20100830000001 0830 10086 07 0557 32 3 11 100 20100830000313 11 
20100830000001 0830 10086 14 0564 52 1 12 100 20100830000328 12 
20100830000002 0830 10086 10 0550 40 2 11 100 20100830000006 11 
20100830000002 0830 10086 14 0564 40 2 11 100 20100830000007 11 
20100830000002 0830 10086 10 0550 40 2 11 100 20100830000011 11 
20100830000002 0830 10086 01 0551 40 2 10 80  20100830000017 10 
18 rows selected 

 

 

 

*******有关建立外部表的参数说明:

 

 

 

 

create table extnl_table
(
 col01 varchar2(100),
 col02 number,
 ......
)
organization external
( type oracle_loader
  default directory "xxx"
  access parameters
  (
  records delimited by 0x'0a'
  skip 1 fields
  terminated by ','
  enclosed by '"'
  lrtrim
  missing field values are null
  reject rows with all null fields
  (col01,col02,……)
)
  location( 'data.txt',……)
);

 

外部表的定义要害是oracle loader参数,records要害字后定义假如识别数据行,fields要害字后定义假如识别字段,常用的如下:


 records:

delimited by 'xxx' —— 换行符,常用newline定义换行,假如文件中使用了非凡的字符就要另外定义了;假如是非凡符号,可以使用ox'十六位值',例如tab(\t)的十六位是9,那么就是delimited by 0x'09';cr(\r)的十六位是d,那么就是delimited by 0x'0d'。

skip x —— 跳过x行数据,有些文件中第一行是列名,需要跳过第一行,则使用skip 1。


fields:

 terminated by 'x' —— 字段分割符。

 enclosed by 'x' —— 字段引用符,包含在此符号内的数据都当成一个字段。例如一行数据格式如:"abc","a""b,""c,"。使用参数terminated by ',' enclosed by '"'后,系统会读到两个字段,第一个字段的值是abc,第二个字段值是a"b,"c,。

 lrtrim —— 删除空白字符。

 missing field values are null —— 空缺值都设为null。

 

 

假如外部表文件的字段长度和分割符不确定,可以打开文件看看。但是假如文件非常大,比如用十几g,用ultraedit之类的工具打开也许会等十几个小时。那么可以使用分割文件的工具将文件切割成1m左右的小文件,再用记事本打开,复制几行数据出来作测试用。

 

读取外部表时最常见的问题就是报buffer不足了,假如增大read size还不能解决问题,可能就是断行错误,也许是换行符不对。因为有些换行符在记事本中看不出来,比如说windows的换行符是\n\r,而mac等系统中是line feed(lf, 0x0a)。假如在windows中打开带lf的文件,记事本中显示的是一个黑色的方块,而写字板中是能够显示正确的换行。所以最好使用ultraedit打开,直接看16进制代码,使用delimited by 0x'xx'断行。

 

还有可能在从外部表读取数据时,读取前面的数据没问题,读到一定的行数就报错。这可能是某行数据出问题导致。只是可以在外部文件目录下发现文件名带有"bad"的日志文件,其中就保存了出错的数据,有记事本打开看看那里出错,是否与外部表定义冲突。

分享到:
评论

相关推荐

    oracle外部表Externaltable[借鉴].pdf

    2. 创建外部表结构:CREATE TABLE X1 (...) ORGANIZATION EXTERNAL (...); 3. 查询外部表:SELECT * FROM X1; 外部表的限制: * 外部表是只读的,不能进行 DML 操作。 * 外部表不能创建索引。 * 外部表的数据存储...

    oracle 10g创建外部表

    - **外部表结构**:外部表与普通表类似,但其数据存储在数据库外部,由Oracle通过外部表驱动程序访问。 - **外部目录**:在创建外部表之前,首先需要创建一个外部目录对象,这相当于一个指向文件系统的链接。 - *...

    oracle外部表的使用

    ### 一、创建外部表 外部表的创建主要通过`CREATE TABLE`语句,加上`ORGANIZATION EXTERNAL`子句来实现。以下是一个创建外部表的基本语法: ```sql CREATE TABLE table_name ( column1 datatype, column2 ...

    ORACLE建外部表笔记

    ### ORACLE创建外部表知识点详解 #### 一、创建外部表背景及意义 在Oracle数据库中,外部表是一种特殊的表类型,它允许直接访问文件系统中的数据文件,而无需将这些数据加载到数据库中。这种方式既节省了存储空间...

    \Oracle 外部表

    1. **目录对象的创建:**在创建外部表之前,需要先创建一个目录对象来指定外部文件所在的路径。 2. **操作系统文件的要求:**文件必须具有固定的格式,通常为逗号分隔或固定宽度的文本文件。文件不应包含标题行,...

    oracle 创建表空间命令

    在Oracle数据库管理系统中,创建表空间是管理数据库存储空间的关键操作。表空间是数据库中用于存储数据对象(如表、索引、视图等)的逻辑结构。它将物理磁盘上的一个或多个数据文件组织成一个逻辑单元,使得数据库...

    学习oracle创建一个表空间创建临时表空间创建用户表空间资源的权限

    以下将详细介绍如何在Oracle中创建表空间、临时表空间以及赋予用户对表空间的权限。 首先,我们来了解如何创建一个常规表空间: 1. **创建表空间**: 使用`CREATE TABLESPACE`语句来创建一个新的表空间。例如,...

    oracle创建表创建唯一索引

    在Oracle数据库管理系统中,创建表和唯一索引是数据库设计中的关键步骤,它们对于数据的组织、查询效率和数据完整性至关重要。以下将详细介绍如何创建学员信息表,创建唯一索引,以及如何修改表来添加主键和检查约束...

    oracle创建表空间用户等

    ### Oracle 创建表空间、用户及分配权限详解 在Oracle数据库管理中,创建表空间和用户是常见的基础操作之一。本文将详细介绍如何在Oracle数据库中创建表空间、创建用户并分配相应的权限,以及如何设置表空间的自...

    oracle 外部表语法

    3. **创建外部表**:使用`CREATE TABLE`命令创建外部表,并指定`ORGANIZATION EXTERNAL`选项以及其他必要的参数。 #### 四、创建外部表示例 以下是一个具体的示例: 1. **创建CSV文件**:“TestTable.csv”,位于...

    Oracle数据库外部表.doc

    - 使用`CREATE TABLE`语句创建外部表,同时指定其组织方式为外部表。 - 组织方式的选择包括: - Heap:堆表 - Index:索引组织表 - External:外部表 - 创建外部表的具体语法如下: ```sql CREATE TABLE ...

    Oracle创建表空间.创建用户.创建表

    ### Oracle 创建表空间、创建用户、创建表 #### 一、创建表空间 表空间是Oracle数据库中的逻辑存储单元,它由一个或多个数据文件组成。表空间是Oracle数据库存储结构的基本单位,用来组织和管理数据库中的数据。在...

    oracle创建分区表.pdf

    本篇文档详细介绍了Oracle数据库中分区表的创建、使用和扩容过程。 一、分区表的优点 当处理大型表时,数据访问和管理效率会大幅下降。分区表能够将一个大表分解为小的、更易于管理的部分,这些部分在逻辑上是表的...

    oracle数据库创建表空间和用户

    oracle数据库导入、导出数据、创建表空间、创建用户、用户授权等操作

    Oracle 19c创建表空间及用户.txt

    Oracle 19c创建表空间及用户

    oracle创建数据库表空间.doc

    Oracle 创建数据库表空间 Oracle 是一种流行的关系数据库管理系统,创建数据库表空间是 Oracle 数据库管理员的一项基本任务。本文将详细介绍如何在 Linux 环境下创建和删除 Oracle 表空间及用户。 一、创建临时表...

    Oracle外部表特性深入浅出

    - 在创建外部表时,Oracle并不会在数据库内创建物理表结构,也不会分配存储空间,仅创建元数据记录。 4. **删除操作**: - 删除外部表前应先删除与之关联的目录对象,以避免“对象不存在”的错误。 - 查询`dba_...

    oracle创建删除表空间

    主要用于创建表空间,创建用户,指定默认表空间临时表空间,删除表空间等

    Oracle数据库创建表空间和用户.doc

    在Oracle数据库管理中,创建表空间和用户是基础但至关重要的操作,这直接影响到数据库的性能、安全性和数据管理效率。以下将详细解析如何在Oracle数据库中创建表空间和用户,以及这一过程中涉及的关键概念和步骤。 ...

    利用kettle自动创建oracle表分区

    "利用Kettle自动创建Oracle表分区" 在本篇文章中,将会介绍如何使用Kettle自动创建Oracle表分区。Oracle表分区是一种非常重要的数据库优化技术,它可以将大型表分割成多个小的独立表,从而提高查询效率和数据管理...

Global site tag (gtag.js) - Google Analytics