oracle 外部表

来源：互联网发布：facebook中国知乎编辑：程序博客网时间：2024/05/03 16:35

准备数据：

[oracle@sgpc146 sqlldr]$ more ext.txt
1|tc
2|tcyang
3|tcy

DROP TABLE SYS.EXT_TABLE_NAME CASCADE CONSTRAINTS;create table EXT_TABLE_NAME(COL_1 NUMBER,COL_2 VARCHAR2(512))ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY DIR_SQLLDRACCESS Parameters(RECORDS DELIMITED BY NEWLINEbadfile 'bad_dev.txt'LOGFILE 'log_dev.txt'FIELDS TERMINATED BY '|')LOCATION('ext.txt')) reject limit unlimited;--查看数据SQL> select * from EXT_TABLE_NAME;     COL_1----------COL_2--------------------------------------------------------------------------------         1tc         2tcyang         3tcyElapsed: 00:00:00.03SQL>

如果报错：

ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-04040: 未找到文件 ext.txt, 在DIR_SQLLDR中.

解决：

[oracle@sgpc146 ~]$ cd ..
[oracle@sgpc146 home]$ ll
total 12
drwx------ 4 grid oinstall 4096 Sep 6 2012 grid
drwx------ 8 oracle oinstall 4096 Sep 22 23:33 oracle
[oracle@sgpc146 home]$ chmod 755 /home/oracle/
[oracle@sgpc146 home]$ ll
total 12
drwx------ 4 grid oinstall 4096 Sep 6 2012 grid
drwxr-xr-x 8 oracle oinstall 4096 Sep 22 23:33 oracle
[oracle@sgpc146 home]$