Oracle中dblink所产生远程会话的一些表现

惯性思维总是 令我们离大师们 有着一定的距离,这种差距 在知识广袤的领域尤其明显。

Oracle领域被称为Oracle的世界,当之无愧;一丁点的想当然就让我们偏离于事实。

以dblink的表现为例,我一直认为dblink的远程连接session仅在操作(select,dml)发生时短期存在,在操作完成后依据一定条件保留或退出。

而事实并非如此,随便使用一个远程查询语句如下:

SQL> select * from help@LZ;       –LZ 为dblink名

在远程数据库端观察session,可以发现:

select sid,username,machine,program,module from v$session where module=’ORACLE.EXE’

SID USERNAME MACHINE PROGRAM MODULE
1 526 SYSTEM WIN_DESK1 ORACLE.EXE ORACLE.EXE

且该远程会话一直保留直到原会话退出为止,无论是成功退出还是程序失败。

若希望在原会话中关掉已打开的远程会话,则需要使用一下命令:
Alter session close database link DBLINKNAME;

这里要注意,需要先执行commit后以上关闭远程session SQL方会成功,即便是Select操作也是如此;

若没有执行commit,会出现:

ORA-02080: 数据库链接正在使用中

Comments

  1. admin says

    How To Remove a Dead Connection to the Target Database Through DBlink After a Network Connection Failure
    Applies to:
    Oracle Net Services – Version: 8.1.7 to 9.2
    Information in this document applies to any platform.
    Goal
    This document describe how you can remove a dead connection to the remote database through dblink after a network connection failure with the remote database server.
    Solution

    1. Please set (ENABLE=BROKEN) in the connect string for the target database in the tnsnames.ora file at the origin or source database ORACLE_HOME\network\admin or TNS_ADMIN directory.

    E.g.
    TEST.WORLD =
    (DESCRIPTION =
    (ENABLE=BROKEN)
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <>)(PORT = <>))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = DISCARD.WORLD)
    ) )
    2. Please create the dblink using this connect string.
    3. Please set the OS tcp/ip keepalive parameter in the local databse to a desired low value (e.g. 5 minutes).

    Please note that in the case of a database link, the target destination of the link constitutes the server side of the connection. So it is not possible to use the DCD to close the database link from the origin database server in case of a network failure.

  2. ricky says

    我的数据库就因为dblink连接太多报错:
    Thu Aug 23 02:52:55 2012
    Error 2068 trapped in 2PC on transaction 21.39.390098. Cleaning up.
    Error stack returned to user:
    ORA-02068: following severe error from ODSLINK
    ORA-03113: end-of-file on communication channel

    系统2:

    Mon Jul 16 21:47:21 2012
    Errors in file /oracle/app/admin/net_ibss/udump/net_ibss_ora_1429730.trc:
    ORA-00600: internal error code, arguments: [729], [160], [space leak], [], [], [], [], []
    ORA-02068: following severe error from DBLINK_YNWDFZY
    ORA-03113: end-of-file on communication channel
    Tue Jul 17 00:26:19 2012
    Errors in file /oracle/app/admin/net_ibss/udump/net_ibss_ora_1433798.trc:
    ORA-00600: internal error code, arguments: [729], [144], [space leak], [], [], [], [], []
    ORA-02068: following severe error from DBLINK_YNWDFZY
    ORA-03113: end-of-file on communication channel

    想问:是不是和dblink连接多,导致session不释放,所以产生以上错误呢。

  3. 建立一个数据库链接,测试链接是正常的。
    但是有个问题。

    select * from puinfo @ACTUAL_TO_PGIS where rownum<=100;
    commit;
    alter session close database link ACTUAL_TO_PGIS;

    以前三个语句在command windows执行无任务任何错误
    如果在sql windows中执行,一条条的执行也没有任何问题。
    如何选中三条一起执行或者F8则出现 数据库链接正在使用中的错误,大师们,请教为何?

  4. 大师好,我的是在存储过程中调用 update .. tablename@dblink_B ,之后commit ,并没有(Alter session close database link) 我在B后台看会话连接会直接释放 这个是为啥啊?

ricky进行回复 取消回复

*

沪ICP备14014813号-2

沪公网安备 31010802001379号