创建外部表语法create table ......orginzition external，可以把外部表假想为一个view，可以正常的做select。
analyze 和 虚拟列 表分析不使用与外部表
创建外部表语法
外部表文件：
example1.txt
360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda
example2.txt
401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard
创建外部表oracle可识别路径：SQL> conn / as sysdba
SQL> create directory external_dir as ‘/u01/app/oracle/oradata/external‘;
Directory created.
SQL> grant read，write on directory external_dir to kevin;
Grant succeeded.
外部表的使用用户kevin，创建外部表SQL> CREATE TABLE ex_employees
2 (employee_id NUMBER(4),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25),
5 job_id VARCHAR2(10),
6 manager_id NUMBER(4),
7 hire_date DATE,
8 salary NUMBER(8,2),
9 commission_pct NUMBER(2,2),
10 department_id NUMBER(4),
11 email VARCHAR2(25)
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_LOADER
16 DEFAULT DIRECTORY external_dir
17 ACCESS PARAMETERS
18 (
19 records delimited by newline
20 badfile external_dir:‘empxt%a_%p.bad‘
21 logfile external_dir:‘empxt%a_%p.log‘
22 fields terminated by ‘,‘
23 missing field values are null
24 ( employee_id, first_name, last_name, job_id, manager_id,
25 hire_date char date_format date mask "dd-mon-yyyy",
26 salary, commission_pct, department_id, email
27 )
28 )
29 LOCATION (‘example1.txt‘, ‘example2.txt‘)
30 )
31 PARALLEL
32 REJECT LIMIT UNLIMITED;
Table created.
创建完成后可以可以通过cats把数据导入至数据库的表中，如果数据比较多，可以开启session level parallel导入alter session enable parallel；
create table employee as select * from ex_employee;
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
----------- -------------------- ------------------------- ---------- ---------- --------- ---------- -------------- ------------- -------------------------
360 Jane Janus ST_CLERK 121 17-MAY-01 3000 0 50 jjanus
361 Mark Jasper SA_REP 145 17-MAY-01 8000 .1 80 mjasper
362 Brenda Starr AD_ASST 200 17-MAY-01 5500 0 10 bstarr
363 Alex Alda AC_MGR 145 17-MAY-01 9000 .15 80 aalda
401 Jesse Cromwell HR_REP 203 17-MAY-01 7000 0 40 jcromwel
402 Abby Applegate IT_PROG 103 17-MAY-01 9000 .2 60 aapplega
403 Carol Cousins AD_VP 100 17-MAY-01 27000 .3 90 ccousins
404 John Richardson AC_ACCOUNT 205 17-MAY-01 5000 0 110 jrichard
10 rows selected.
创建完成，外部表可以正常使用
本文出自 “DBA的天空” 博客，请务必保留此出处http://kevinora.blog.51cto.com/9406404/1690038
oracle创建外部表
标签：external table
本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源：http://kevinora.blog.51cto.com/9406404/1690038