『ORACLE』外部表external table创建（11g）

[oracle@enmo1 ~]$ mkdir ext

SQL> create directory dir_ext as '/home/oracle/ext';

Directory created.
SQL> grant read,write on directory dir_ext to public;
Grant succeeded.
SQL> conn scott/tiger  

Connected.

用创建外部表的方式卸载表到目录对象dir_et 指定目录下

SQL> create table ext_dept(deptno,dname,loc)
  2    organization external
  3    (type oracle_datapump
  4     default directory dir_ext
  5     location ('ext1.dat','ext2.dat')
  6    )
  7     parallel 2 
  8     as select * from scott.dept;

Table created.

查看外部表字典表

SQL> select table_name,type_name,default_directory_name from user_external_tables;

TABLE_NAME      TYPE_NAME                     DEFAULT_DIRECTORY_NAME
---------------------- ---------------------------------- -------------------------------------------
EXT_DEPT           RACLE_DATAPUMP         DIR_EXT

——————————————————————————————————————————————

[oracle@enmo1 ~]$ mkdir data
[oracle@enmo1 ~]$ mkdir log
[oracle@enmo1 ~]$ mkdir bad
[oracle@enmo1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 31 21:04:24 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

创建目录对象

create or replace directory dir_data 
   as '/home/oracle/data';
create or replace directory dir_log 
   as '/home/oracle/log';
create or replace directory dir_bad
   as '/home/oracle/bad';
grant read,write on directory dir_data to public;
grant read,write on directory dir_log to public;
grant read,write on directory dir_bad to public;

创建数据文件

[oracle@enmo1 ~]$ cd data
[oracle@enmo1 data]$ vi d1.dat
10,account,New York

[oracle@enmo1 data]$ vi d2.dat
20,research,dallas

[oracle@enmo1 data]$ vi d3.dat
30,sales,chicago
40,operations,boston

创建外部表

SQL> create table extab_ldr
  2     (deptno number(2),
  3      dname  varchar(14),
  4      loc    varchar(13)
  5     )
  6      organization external
  7     (type oracle_loader
  8      default directory dir_data
  9      access parameters
 10    (records delimited by newline
 11     badfile dir_bad:'ext%a_%p.bad'
 12     logfile dir_log:'ext%a_%p.log'
 13     fields terminated by ','
 14     missing field values are null)
 15      location('d1.dat','d2.dat','d3.dat')
 16     )
 17      parallel
 18      reject limit unlimited;
Table created.

查看外部表

SQL> select * from extab_ldr;       
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 research       dallas

        10 account        New York

        30 sales          chicago
        40 operations     boston
6 rows selected.

——————————————————————————————————————————————

外部表查看警告日志

[oracle@enmo1 trace]$ pwd
/u01/app/oracle/diag/rdbms/enmo1/enmo1/trace

SQL> create or replace directory dir_alog as '/u01/app/oracle/diag/rdbms/enmo1/enmo1/trace';
Directory created.

SQL> grant write,read on directory dir_alog to scott;
Grant succeeded.
SQL> conn scott/tiger

SQL> create table alert_log
  2     (text varchar2(4000)
  3     )
  4      organization external
  5     (type oracle_loader
  6      default directory dir_alog
  7      access parameters
  8    (records delimited by newline
  9     nobadfile 
 10     nodiscardfile 
 11     nologfile
 12     )
 13      location('alert_ENMO.log')
 14     )    
 15      reject limit unlimited
 16  /
Table created.