这是几年前做的实验，共享一下

备注：我使用的是sys用户

SQL> create directory tns as 'C:\oracle\product\10.1.0\db_1\network\admin'
  2  /

目录已创建。

SQL> create table tnsnames(
  2  text varchar2(400)
  3  ) organization external
  4  (type oracle_loader
  5  /
(type oracle_loader
                  *
第 4 行出现错误:
ORA-30654: 缺少 DEFAULT 关键字

SQL> create table tns(text varchar2(400)
  2  )
  3  organization external
  4  (records delimited by newline)
  5  location('tnsnames.ora');
(records delimited by newline)
 *
第 4 行出现错误:
ORA-30654: 缺少 DEFAULT 关键字

SQL> create table tns(text varchar2(400)
  2  )
  3  organization external
  4  (type oracle_locader
  5  default directory tns
  6  (records delimited by newline
  7  )location('tnsnames.ora')
  8  );
(records delimited by newline
*
第 6 行出现错误:
ORA-30648: 缺少 LOCATION 关键字

SQL> edit
已写入 file afiedt.buf

  1  create table tns(text varchar2(400)
  2  )
  3  organization external
  4  (type oracle_locader
  5  default directory tns
  6    access parameters
  7  (records delimited by newline
  8  ) location ('tnsnames.ora')
  9* )
SQL> /
create table tns(text varchar2(400)
             *
第 1 行出现错误:
ORA-30657: 操作在外部组织表上不受支持

SQL> conn /as sysdba
已连接。
SQL> drop directory tns;

目录已删除。

SQL> create directory tns as 'C:\oracle\product\10.1.0\db_1\network\admin';

目录已创建。

SQL> create table tns(
  2  text varchar2(400))
  3  organization external
  4  (
  5  type oracle_loader
  6  default directory tns
  7  access parameters (
  8  records delimited by newline
  9  )
 10  location
 11  ('tnsnames.ora')
 12  /
('tnsnames.ora')
               *
第 11 行出现错误:
ORA-00907: 缺失右括号

SQL> edit
已写入 file afiedt.buf

  1  create table tns(
  2  text varchar2(400))
  3  organization external
  4  (
  5  type oracle_loader
  6  default directory tns
  7  access parameters (
  8  records delimited by newline
  9  )
 10  location
 11  ('tnsnames.ora')
 12* )
SQL> /

表已创建。

SQL> select * from tns;
select * from tns
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
ORA-30653: 已达到拒绝限制值
ORA-06512: 在 "SYS.ORACLE_LOADER", line 52
ORA-06512: 在 line 1

SQL> select * from tns where rownum<20;
select * from tns where rownum<20
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
ORA-30653: 已达到拒绝限制值
ORA-06512: 在 "SYS.ORACLE_LOADER", line 52
ORA-06512: 在 line 1

SQL> edit
已写入 file afiedt.buf

  1* select * from tns where rownum<20
SQL> /
select * from tns where rownum<20
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
ORA-30653: 已达到拒绝限制值
ORA-06512: 在 "SYS.ORACLE_LOADER", line 52
ORA-06512: 在 line 1

SQL> drop table tns;

表已删除。

SQL> create table tns(
  2  text varchar2(400))
  3  organization external
  4  (
  5  type oracle_loader
  6  default directory tns
  7  access parameters (
  8  records delimited by newline
  9  )
 10  location
 11  ('tnsnames.ora')
 12  ) reject limit unlimited
 13  /

表已创建。

SQL> select * from tns;

TEXT
--------------------------------------------------------------------------------

# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\db_1\network

\admin\tnsnames.ora

# Generated by Oracle configuration tools.
LION =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = relis)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lion)
    )

TEXT
--------------------------------------------------------------------------------

  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

TEXT
--------------------------------------------------------------------------------

LION2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = relis)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lion)
    )
  )
LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =

TEXT
--------------------------------------------------------------------------------

      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = relis)(PORT = 1522))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

已选择39行。