1 、创建目录及授权
create directory exttab as 'F:oracleproduct10.2.0oradataexttab'
GRANT READ ON DIRECTORY exttab TO system;
GRANT WRITE ON DIRECTORY exttab TO system;
2、准备数据文件到相关目录
F:oracleproduct10.2.0oradataexttab>dir
Volume in drive F has no label.
Volume Serial Number is 5B8D-A9E4
Directory of F:oracleproduct10.2.0oradataexttab
2007-05-18 16:09
2007-05-18 16:09
2007-05-18 15:17 221 1.dat
2007-05-18 14:49 300 1.dat.bak
2007-05-18 15:17 156 2.dat
2007-05-18 14:49 234 2.dat.bak
2007-05-18 15:32 591 EXTTEST2_2292_3356.log
2007-05-18 15:18 84 EXTTEST_2292_3356.bad
2007-05-18 15:18 2,860 EXTTEST_2292_3356.log
2007-05-18 15:12 84 EXTTEST_2292_3964.bad
2007-05-18 15:12 1,430 EXTTEST_2292_3964.log
2007-05-18 15:13 84 EXTTEST_2292_588.bad
2007-05-18 15:13 1,430 EXTTEST_2292_588.log
2007-05-18 16:09 244 EXTTXTM_2292_3356.bad
2007-05-18 16:11 1,536 EXTTXTM_2292_3356.log
2007-05-18 15:27 81 test2.txt
2007-05-18 16:05 121,756 txtm.txt
15 File(s) 131,091 bytes
3、创建外部表
SQL> CREATE TABLE exttxtm
2 ( KEY_1 CHAR(31),
3 STAT CHAR(1),
4 TEXT CHAR(200),
5 TEXT_LENGTH CHAR(3),
6 TEXT_BOUND_LEN CHAR(3))
7 ORGANIZATION EXTERNAL
8 (TYPE ORACLE_LOADER
9 DEFAULT DIRECTORY exttab
10 ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
11 FIELDS TERMINATED BY ',')
12 LOCATION('txtm.txt'));
Table created.
4、校验数据是否可读
SQL> select * from exttxtm where rownum<2;
KEY_1 ST TEXT
TEXT_L TEXT_B
------------------------------ -- ----------------------------------------------
---- ------ ------
003BORACT010000000000008206000 0 NZD RESIDENTIAL INVESTMT LOAN
029 000
1