ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 介绍oracle外部表

介绍oracle外部表

原创 Linux操作系统 作者:pingley 时间:2012-05-30 21:39:04 0 删除 编辑
介绍oracle外部表
外部表(external tables)是对sql*loader 的一个补充,可以让我们访问数据库外部的数据资源。不过外部表存在着诸多的限制,所以我们使用前必须对其有充分的了解。外部表中的一些限制:
1、不能在外部表上执行DML操作。
2、不能在外部表上创建index。
3、不支持analyze 分析语句,但是可以使用dbms_stats。
4、不支持虚拟列。
ref:http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4347075367803
oracle 10g 以后可以允许我们把数据库中的表,write out 成为外部表。
外部表创建前的准备工作
在创建外部表前我们需要做一些准备工作。
1、在OS 上面创建一个目录,或者选择一个已经存在的目录。
2、确保上面创建的目录安装oracle 数据库的用户有读写的权利(这一点在类Unix 系统中需要注意)。
3、把外部表的数据实际数据放置在这个目录下。
930070350,smith,1111@qq.com,m,23
930070351,tom,2222@qq.com,m,27
930070352,peter,3333@qq.com,m,24
930070353,jack,4444@qq.com,m,25
930070354,king,5555@qq.com,m,26
将以上数据填充进ms-excel 保存为students_info.csv
4、在数据库系统中创建目录对象(默认情况下拥有DBA权限的用户才能创建目录对象)。
SQL> show user
USER is "SYS"
SQL> create or replace directory data_dir
  2  as 'c:\sqlloader\external';
Directory created.
5、把目录对象的read,write 权限授予给需要使用该目录对象的用户。
SQL> grant read,write on directory data_dir to hr;
Grant succeeded.
创建外部表
做完了上述的准备工作,我们就可以来创建外部表了。
SQL> ed
Wrote file afiedt.buf
  1  create table students(-- 外部表的创建语句和通常的create table 语句大部分相同。
  2  id     varchar2(16),
  3  name   varchar2(30),
  4  email  varchar2(20),
  5  gender char(1),
  6  age    number(2)
  7  )
  8  organization external--指定为外部表。
  9  ( type oracle_loader-- 指定外部表的加载类型。有oracle_loader,oracle_datapump。
 10    default directory data_dir--指定外部表默认的读写位置,是通过目录对象指定的,而不是实际的目录。
 11    access parameters--指定根据什么规则把外部文件中的数据加载到外部表中。
 12    ( records delimited by newline
 13      fields terminated by ','
 14    )
 15    location ('students_info.csv')--指定外部表加载的数据源的位置。
 16* )
SQL> /
Table created.
ref:http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm#i1012231
  http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#ADMIN01507
在创建外部表的过程中,并不会实际的加载外部数据源中的数据,也不会验证access parameter 参数的有效性,这些动作要等到向外部表发出查询的时候才执行。
SQL> select * from students;
ID         NAME       EMAIL        GEND        AGE
---------- ---------- ------------ ---- ----------
930070350  smith      1111@qq.com  m            23
930070351  tom        2222@qq.com  m            27
930070352  peter      3333@qq.com  m            24
930070353  jack       4444@qq.com  m            25
930070354  king       5555@qq.com  m            26
使用oracle_loader 方式加载外部数据的时候,根据不同的情况像sql*loader 中一样也会生成 log,bad,discard file.当加载出现错误的时候,查看log文件非常重要。
现在我们可以访问students 中的数据,但是不能在students 表中执行DML操作和创建索引,这限制了students 表的用处,把students 表中的数据再加载到普通的oracle table 中来解决这个问题。
SQL> create index students_idx on students(id);
create index students_idx on students(id)
                             *
ERROR at line 1:
ORA-30657: 操作在外部组织表上不受支持
SQL> select * from students_info;
ID         NAME       EMAIL        GEND        AGE
---------- ---------- ------------ ---- ----------
930070350  smith      1111@qq.com  m            23
930070351  tom        2222@qq.com  m            27
930070352  peter      3333@qq.com  m            24
930070353  jack       4444@qq.com  m            25
930070354  king       5555@qq.com  m            26
SQL> create index students_info_idx on students_info(id);
Index created.
也就是说可以借助外部表把oracle 数据库外的数据加载进oracle 数据库中的普通表中。
以上介绍的都是使用oracle_loader 的方式来加载数据库外的数据,下面我们考虑使用一种新的方式来加载和卸载数据。
注:卸载数据只有使用oracle_datapump 的时候才是可用的。
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 EMPLOYEE_ID                                        NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
SQL> create table export_emp
  2  organization external
  3  ( type oracle_datapump
  4    default directory data_dir
  5    location ('export_emp.dmp')
  6  ) as select * from emp;
Table created.
现在我们把 emp 这张表中的数据导出到了data_dir 目录对象指定的位置,文件的格式是oracle_datapump 专用的格式。
注:oracle_datapump 加载和卸载数据的格式都是oracle_datapump 专用的。
现在我们就可以把oracle_datapump 方式生成的 export_emp.dmp 文件传输到其他系统中了,可以用作数据迁移的一种方式。下面演示把刚才导出的文件加载到其他schema 下。
SQL> show user
USER is "SYS"
SQL> grant read,write on directory data_dir to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table import_emp
  2  ( employee_id number(6),
  3    first_name  varchar2(20),
  4    last_name   varchar2(25)
  5  )
  6  organization external
  7  ( type oracle_datapump
  8    default directory data_dir
  9    location ('export_emp.dmp')
 10  );
Table created.
SQL> select count(*) from import_emp;
  COUNT(*)
----------
       107
SQL> create index test_idx on import_emp(employee_id);--记得import_emp 是个外部表哦。
create index test_idx on import_emp(employee_id)
                         *
ERROR at line 1:
ORA-30657: 操作在外部组织表上不受支持

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26110315/viewspace-731506/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ora_rowscn 伪列
下一篇: 关于目录对象
请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    721303