外部表创建需要指定数据库目库（新版本）
grant CREATE ANY directory to test;
create directory dir_data as '/dm8/backup';
grant read, write on directory dir_data to test;

外部表不支持指定约束

创建外部表：可以指定数据文件创建外部表，也可以指定控制文件创建外部表。
CRLF 0x0d0a
LF 0x0a
1、指定数据文件创建外部表
create EXTERNAL TABLE t_extemp
(
"EMPLOYEE_ID" INT,
"EMPLOYEE_NAME" VARCHAR(20),
"IDENTITY_CARD" VARCHAR(18),
"EMAIL" VARCHAR(50),
"PHONE_NUM" VARCHAR(20),
"HIRE_DATE" DATE,
"JOB_ID" VARCHAR(10),
"SALARY" INT,
"COMMISSION_PCT" INT,
"MANAGER_ID" INT,
"DEPARTMENT_ID" INT)
from DATAFILE DEFAULT DIRECTORY DIR_DATA LOCATION ('DMHR_EMPLOYEE.txt')
parms (fields DELIMITED by '|', records 0x0a,skip 1, character_code 'UTF-8');
2、指定控制文件创建外部表：
1）编写控制文件
[dmdba@localhost data]$ cat dept.ctl
options
(skip=1
errors=10
log='t_extdept.log'
character_code='UTF-8')
load data
infile 'DMHR_DEPARTMENT.txt' str X '0d0a'
badfile 't_extdept.bad'
into table t_extdept
fields '|'
2）基于控制文件创建外部表
create EXTERNAL TABLE t_extdept
(
"DEPARTMENT_ID" INT,
"DEPARTMENT_NAME" VARCHAR(30),
"MANAGER_ID" INT,
"LOCATION_ID" INT
)
from DEFAULT DIRECTORY DIR_DATA LOCATION ('dept.ctl') ;