linux查看tnsnames.ora,使用外部表来查看tnsnames.ora内容的方法

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

备注：我使用的是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行。