三步教会你掌握Oracle外表(external table)

外表(external table)就像普通的表对像一样，可以select等，只是它是只读的，数据库中只保存了表结构的描述，表数据却没有存放在数据库内，而是存放在了文件系统上。当用户想偶尔使用数据库外的结构化数据时，用起外表来就非常方便，甚至比sqlldr都要方便的多。在这篇文章里，我们为大家演示了

三步就掌握oracle外表过程。通过这次学习，也许大家就会发展原来学习oracle也是好容易哦。


第一步：创建目录并授权

目录是数据文件的存放目标，数据文件通常要求是文本文件。这个过程在9i以前是需要配置utl_file_dir参数的。 复制内容到剪贴板

代码:



sys@TEST>!ls /home/oracle/temp

user.ctl userlist.txt user.log


rudolf@TEST>

sys@TEST>conn system/alibaba

Connected.

sys@TEST>

sys@TEST>CREATE DIRECTORY TEMP AS '/home/oracle/temp/';


Directory created.


sys@TEST>grant read,write on directory TEMP to rudolf;


Grant succeeded.

第二步：创建外表与测试 复制内容到剪贴板

代码:


rudolf@TEST>CREATE TABLE "USERLIST"

2 (

3 ID NUMBER,

4 USERNAME VARCHAR2(30),

5 EMAIL VARCHAR2(128)

6 )

7 ORGANIZATION external

8 (

9 TYPE oracle_loader

10 DEFAULT DIRECTORY TEMP

11 ACCESS PARAMETERS

12 (

13 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

14 BADFILE 'TEMP':'userlist.bad'

15 DISCARDFILE 'TEMP':'userlist.dis'

16 LOGFILE 'TEMP':'user.log'

17 READSIZE 1048576

18 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

19 MISSING FIELD VALUES ARE NULL

20 REJECT ROWS WITH ALL NULL FIELDS

21 (

22 ID CHAR(30)

23 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

24 USERNAME CHAR(30)

25 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

26 EMAIL CHAR(128)

27 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

28 )

29 )

30 location

31 (

32 'userlist.txt'

33 )

34 )REJECT LIMIT UNLIMITED

35

rudolf@TEST>/


Table created.


rudolf@TEST>l

1 select id,username from userlist where rownum < 10

2*

rudolf@TEST>/


ID USERNAME

---------- ------------------------------

1 RudolfLu

3 tomgu

6 coug

7 chao_ping

8 parrotao

9 cnoug

10 FilsDeDragon

11 Dragon


9 rows selected.