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

最新推荐文章于 2021-04-15 16:26:28 发布

cldnu44288

最新推荐文章于 2021-04-15 16:26:28 发布

阅读量206

点赞数

文章标签：数据库网络

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/119941/viewspace-674258/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/119941/viewspace-674258/