[oracle@localhost mydir]$ cat samp1.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
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
SQL> create or replace directory mydir as '/home/oracle/mydir';
Directory created.
SQL> grant read,write on directory mydir to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
CREATE TABLE admin_ext_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
(
records delimited by newline
badfile mydir:'badxt%a_%p.bad'
logfile mydir:'logxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask "dd-mon-yyyy",
salary, commission_pct, department_id, email
)
)
LOCATION ('samp1.txt')
)
PARALLEL REJECT LIMIT UNLIMITED;
PARALLEL --指定查詢時的並行度
REJECT LIMIT UNLIMITED --指定外部表查詢錯誤數量
執行成功看產生的日志。
[oracle@localhost mydir]$ ls -al
total 20
drwxr-xr-x 2 oracle oinstall 4096 Nov 20 16:12 .
drwx------ 5 oracle oinstall 4096 Nov 20 15:53 ..
-rw-r--r-- 1 oracle oinstall 1413 Nov 20 16:12 logxt000_13688.log
-rw-r--r-- 1 oracle oinstall 1555 Nov 20 16:12 logxt000_16408.log
-rw-r--r-- 1 oracle oinstall 480 Nov 20 15:53 samp1.txt
[oracle@localhost mydir]$ cat logxt000_13688.log
LOG file opened at 11/20/15 16:12:03
Field Definitions for table ADMIN_EXT_EMPLOYEES
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
EMPLOYEE_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
FIRST_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
LAST_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
JOB_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MANAGER_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
HIRE_DATE CHAR (80)
Date datatype DATE, date mask dd-mon-yyyy
Terminated by ","
Trim whitespace same as SQL Loader
SALARY CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COMMISSION_PCT CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
DEPARTMENT_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
EMAIL CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
[oracle@localhost mydir]$ cat logxt000_16408.log
LOG file opened at 11/20/15 16:12:03
Field Definitions for table ADMIN_EXT_EMPLOYEES
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
EMPLOYEE_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
FIRST_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
LAST_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
JOB_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MANAGER_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
HIRE_DATE CHAR (80)
Date datatype DATE, date mask dd-mon-yyyy
Terminated by ","
Trim whitespace same as SQL Loader
SALARY CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COMMISSION_PCT CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
DEPARTMENT_ID CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
EMAIL CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
Date Cache Statistics for table ADMIN_EXT_EMPLOYEES
Max Size: 1000
Entries : 1
Hits : 7
Misses : 0
logxt000_16408.log日志比logxt000_13688.log尾部多了一些統計信息。 查看外部表 SQL> select EMPLOYEE_ID,FIRST_NAME from ADMIN_EXT_EMPLOYEES; EMPLOYEE_ID FIRST_NAME ----------- -------------------- 360 Jane 361 Mark 362 Brenda 363 Alex 401 Jesse 402 Abby 403 Carol 404 John 3.外部表修改 能夠使用於alter table下面子句來修改外部表訪問參數 reject limit:ALTER TABLE admin_ext_employees REJECT LIMIT n; project column: ALTER TABLE admin_ext_employees PROJECT COLUMN REFERENCED; ALTER TABLE admin_ext_employees PROJECT COLUMN ALL; default dectory:ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir; 查看修改的屬性 SQL> select OWNER,TABLE_NAME,REJECT_LIMIT,DEFAULT_DIRECTORY_NAME,PROPERTY from dba_external_tables; OWNER TABLE_NAME REJECT_LIM DEFAULT_DIRECTORY_NAME PROPERTY ---------- ------------------------------ ---------- ------------------------------ ---------- SCOTT ADMIN_EXT_EMPLOYEES UNLIMITED MYDIR ALL 4.預處理外部表 SQL> create or replace directory mydir as '/home/oracle/mydir'; Directory created. SQL> grant read,write,execute on directory mydir to scott; Grant succeeded. 在/home/oracle/mydir下面創建一個文件
[oracle@localhost mydir]$ cat uncompress
/bin/gzip -cd $1
[oracle@localhost mydir]$ chmod +x uncompress
SQL> conn scott/tiger
Connected.
CREATE TABLE admin_ext_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
(
records delimited by newline
PREPROCESSOR exec_file_dir:'uncompress'
badfile mydir:'badxt%a_%p.bad'
logfile mydir:'logxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask "dd-mon-yyyy",
salary, commission_pct, department_id, email
)
)
LOCATION ('samp1.txt.gz')
)
PARALLEL
REJECT LIMIT UNLIMITED;
5.外部表的刪除使用DROP TABLE語句這條語句僅僅刪除數據庫中的介質,對實際數據沒有影響。 SQL> drop table admin_ext_employees; Table dropped. 6.有關外部表的系統權限和對象。 外部表的系統權限 CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE 外部表的對象權限 ALTER SELECT
