AI 助理
备案控制台
开发者社区 数据库 文章 正文

oracle-dblink-常用查询

2022-07-04 708
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

ORACLE实例与DBLINK相关的参数

open_links --限制每个session可以打开的database link 数量
open_links_per_instance --指定每个数据库实例全局可迁移(可共享并缓存)的最大打开连接数，供XA transactions使用。

SQL> @p open_links

NAME                                     VALUE
---------------------------------------- ----------------------------------------
open_links                               4
open_links_per_instance                  4

SQL>

测试当session中使用dblink后，什么时侯关闭?

1.测试发现应用session 关闭时，同时会关闭session open的dblink。
2.或者可以使用以下sql主动关闭：alter session close database link TEST_LINK;

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 09:25:35

---执行使用DBLINK的查询后，创建了一个DBLINK SESSION
SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                           USERNAME
---------- -------------------------------- --------------------
       263 oracle@perf-monitor (TNS V1-V3)  GM

---退出重新登录

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected

SQL>

public daabase link 重用是否会重用？减少DBLINK连接数占用

当多个session使用相关的dblink时，不会共用，每个session只能使用自己的打开DBLINK.
并且下面5个session打开了5个dblink，也可以验证open_links（=4）限制的不是实例级的。

---session 1

alter session set container=perfdb;

SQL> create public database link pub_test_link connect to gm identified by gm using '//192.168.56.1:1521/perfdb';

Database link created.

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected


SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 09:56:31

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL>

--- session 2

alter session set container=perfdb;

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> 

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 09:57:29

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       150 oracle@perf-monitor (TNS V1-V3)                                  GM
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> 

SQL> commit;

Commit complete.

---session 3

SQL> alter session set container=perfdb;

Session altered.

SQL> 
SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       150 oracle@perf-monitor (TNS V1-V3)                                  GM
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 09:59:10

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       150 oracle@perf-monitor (TNS V1-V3)                                  GM
       519 oracle@perf-monitor (TNS V1-V3)                                  GM
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL>

--- session 5

SQL> alter session set container=perfdb;

Session altered.

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 10:09:43

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       150 oracle@perf-monitor (TNS V1-V3)                                  GM
       263 oracle@perf-monitor (TNS V1-V3)                                  GM
       519 oracle@perf-monitor (TNS V1-V3)                                  GM
       681 oracle@perf-monitor (TNS V1-V3)                                  GM
       795 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL>

测试验证open_links限制的是每个session打开的dblink的数量

当一个session打开>open_links个dblink时，报错ORA-02020: too many database links in use

SQL> create public database link pub_test_link2 connect to gm identified by gm using ‘//192.168.56.1:1521/perfdb’;

Database link created.

SQL> create public database link pub_test_link3 connect to gm identified by gm using ‘//192.168.56.1:1521/perfdb’;

Database link created.

SQL> create public database link pub_test_link4 connect to gm identified by gm using ‘//192.168.56.1:1521/perfdb’;

Database link created.

SQL> create public database link pub_test_link5 connect to gm identified by gm using ‘//192.168.56.1:1521/perfdb’;

Database link created.

SQL> @dblinks

OWNER DB_LINK USERNAME HOST CREATED

SYS SYS_HUB SEEDDATA 20180207 20:11:23
PUBLIC PUB_TEST_LINK2 GM //192.168.56.1:1521/perfdb 20200506 10:15:31
PUBLIC PUB_TEST_LINK3 GM //192.168.56.1:1521/perfdb 20200506 10:15:34
PUBLIC PUB_TEST_LINK4 GM //192.168.56.1:1521/perfdb 20200506 10:15:39
PUBLIC PUB_TEST_LINK5 GM //192.168.56.1:1521/perfdb 20200506 10:15:44

8 rows selected.

SQL> select sysdate from dual@TEST_LINK;

SYSDATE
20200506 10:17:14

SQL> select sysdate from dual@PUB_TEST_LINK2;

SYSDATE
20200506 10:17:27

SQL> select sysdate from dual@PUB_TEST_LINK3;

SYSDATE
20200506 10:17:32

SQL> select sysdate from dual@PUB_TEST_LINK4;

SYSDATE
20200506 10:17:34

---当前SESSION打开第5个DBLINK时报错

SQL> select sysdate from dual@PUB_TEST_LINK5;
select sysdate from dual@PUB_TEST_LINK5
*
ERROR at line 1:
ORA-02020: too many database links in use

---测试如果dblink session被kill掉，再次使用时会报错，不是自动重连。

SQL> alter session set container=perfdb;

Session altered.

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 10:29:54

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       551 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> @kill sid=551

COMMANDS_TO_VERIFY_AND_RUN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter system kill session '551,14996'immediate -- GM@perf-monitor (oracle@perf-monitor (TNS V1-V3));

SQL> 
SQL> alter system kill session '551,14996'immediate -- GM@perf-monitor (oracle@perf-monitor (TNS V1-V3));

System altered.

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected

SQL> select sysdate  from dual@TEST_LINK;
select sysdate  from dual@TEST_LINK
                     *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.LOGON_DENIED_TO_ALERT'
ORA-00604: error occurred at recursive SQL level 1
ORA-02051: another session or branch in same transaction failed or finalized
ORA-02051: another session or branch in same transaction failed or finalized
ORA-02063: preceding line from TEST_LINK

--- session commit（没有事务也需要commit）之后kill dblink session,再次使用时不会报错

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 10:43:49

SQL> select sid,module,username from v$session where module  like 'oracle%';

       SID MODULE                                                           USERNAME
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
       645 oracle@perf-monitor (TNS V1-V3)                                  GM

SQL> @kill sid=645

COMMANDS_TO_VERIFY_AND_RUN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter system kill session '645,54133'immediate -- GM@perf-monitor (oracle@perf-monitor (TNS V1-V3));

SQL> commit;

Commit complete.

SQL> alter system kill session '645,54133'immediate -- GM@perf-monitor (oracle@perf-monitor (TNS V1-V3));

System altered.

SQL> select sid,module,username from v$session where module  like 'oracle%';

no rows selected

SQL> select sysdate  from dual@TEST_LINK;

SYSDATE
-----------------
20200506 10:44:23

https://www.modb.pro/db/24927

---查看打开dblink的session:

sqlplus /nolog

connect / as sysdba

select username, osuser, status, sid, serial#,sql_id, machine,
process, terminal, program from v$session
where saddr in (select k2gtdses from sys.x$k2gte );

select username,
       osuser,
       status,
       type,
       sid,
       serial#,
       machine,
       sql_id,
       process,
       terminal,
       program,
       'alter system kill session ''' || sid || ',' || serial# || ',@' ||
       inst_id || ''' immediate' kill_session_script
  from gv$session
 where saddr in (select k2gtdses from sys.x$k2gte)
   and status = 'ACTIVE'
   and type = 'USER';

How to view open dblink connections (Doc ID 387848.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=1bfcfe0k8r_52&id=387848.1

APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 12.1.0.2 [Release 8.1.7 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Information in this document applies to any platform.

GOAL
To find out the number of database links opened.

SOLUTION
OPEN_LINKS
Default: 4 Range: 0 to 255
Specifies the maximum number of concurrent open connections to remote databases in one session.
These connections include database links plus external procedures and cartridges each of which uses a separate process.

The following view shows the database link connections that are currently open in your current session:

V$DBLINK - Lists all open database links in your session, that is, all database links with the IN_TRANSACTION column set to YES.

NOTE: It is important to state that the section above "Lists all open database links in your session" is important, as this is only YOUR open dblinks that can be seen.

For example, you can create and execute the script below to determine which links are open (sample output included):

COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 99999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL OPEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL COMMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"

SELECT * FROM V$DBLINK
/

SQL> @dblink

DB_LINK OWNID LOGON HETER PROTOCOL OPN_CUR TXN UPDATE C_P_S
------------------------- ------ ----- ----- -------- ------- --- ------ ------
<dblink> 0 YES YES UNKN 0 YES YES 255

Note that above displays ONLY details about database links open in the session within which you are working.

If looking for details about database links open by different sessions, might use below:

sqlplus /nolog

connect / as sysdba

select username, osuser, status, sid, serial#,sql_id, machine,
process, terminal, program from v$session
where saddr in (select k2gtdses from sys.x$k2gte );

USERNAME OSUSER STATUS
------------------------------ ------------------------------ --------
SID SERIAL#
---------- ----------
MACHINE
----------------------------------------------------------------
PROCESS TERMINAL
------------------------ ------------------------------
PROGRAM
------------------------------------------------
SCOTT <user> INACTIVE
68 11
<hostname>

29318 pts/15
sqlplus<hostname> (TNS V1-V3)

 

REPADMIN <user> INACTIVE
232 5
<hostname>

28081 pts/14
sqlplus@<hostname> (TNS V1-V3)

SQL>
文章标签：
SQL
关系型数据库
Oracle
数据库
缓存
关键词：
Oracle数据库一键上云查询
lemotree
目录
相关文章
ZPY0821
|
SQL Oracle 关系型数据库
Oracle查询优化-查询只包含数字或字母的数据
【2月更文挑战第4天】【2月更文挑战第10篇】查询只包含数字或字母的数据
ZPY0821
1471 1
1639437117631681
|
7月前
|
Oracle 关系型数据库 Linux
【YashanDB 知识库】通过 dblink 查询 Oracle 数据时报 YAS-07301 异常
客户在使用 YashanDB 通过 yasql 查询 Oracle 数据时，遇到 `YAS-07301 external module timeout` 异常，导致 dblink 功能无法正常使用，影响所有 YashanDB 版本。原因是操作系统资源紧张，无法 fork 新子进程。解决方法包括释放内存、停掉不必要的进程或增大进程数上限。分析发现异常源于 system() 函数调用失败，返回 -1，通常是因为 fork() 失败。未来 YashanDB 将优化日志信息以更好地诊断类似问题。
1639437117631681
99 0
赵渝强老师
|
6月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle的闪回版本查询
本文介绍了Oracle数据库的闪回版本查询（Flashback Version Query）功能，通过示例详细讲解了其使用方法。闪回版本查询可获取指定时间区间内行的不同版本，利用`versions between`子句实现。文中包含视频讲解，并通过创建测试表、插入数据及执行查询等步骤，演示如何获取历史版本信息和伪列详情，帮助用户深入了解该功能的实际应用。
赵渝强老师
129 13
赵渝强老师
|
5月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的闪回事务查询
Oracle数据库的闪回事务查询（Flashback Transaction Query）是闪回版本查询的扩充，可用于审计或撤销已提交的事务。通过`flashback_transaction_query`视图，可生成还原特定事务的SQL语句。本文介绍了其基本概念，并通过实战演示如何使用该功能：从授权、开启UNDO数据增强，到创建测试表和事务，最后利用闪回查询撤销已提交的事务，验证数据恢复效果。附带视频讲解，帮助深入理解。
赵渝强老师
139 3
游客kufrkwrbkmpsa
|
6月前
|
Oracle 关系型数据库 Linux
【YashanDB知识库】通过dblink查询Oracle数据时报YAS-07301异常
【YashanDB知识库】通过dblink查询Oracle数据时报YAS-07301异常
游客kufrkwrbkmpsa
78 3
游客kufrkwrbkmpsa
|
6月前
|
Oracle 关系型数据库 MySQL
【YashanDB知识库】oracle dblink varchar类型查询报错记录
这篇文章主要介绍了 Oracle DBLINK 查询崖山 DB 报错的相关内容，包括 ODBC 安装配置、数据源配置、dblink 环境配置、问题原因分析及规避方法。问题原因是 dblink 连接其他数据库时 varchar 类型转换导致的，还介绍了 long 类型限制、char 等类型区别，规避方法是修改参数 MAX_STRING_SIZE 支持 32K。
游客kufrkwrbkmpsa
177 3
1639437117631681
|
7月前
|
Oracle 关系型数据库 Linux
【YashanDB 知识库】通过 dblink 查询 Oracle 数据时报 YAS-07301 异常
某客户在使用 YashanDB 通过 yasql 查询 Oracle 数据时，遇到 `YAS-07301 external module timeout` 异常，导致 dblink 功能无法正常使用，影响所有版本。问题源于操作系统资源紧张，无法 fork 新子进程。解决方法包括释放内存、停掉不必要的进程或增大进程数上限。分析发现异常原因为系统调用 fork() 失败。经验总结：优化日志记录，提供更多异常信息。
1639437117631681
111 5
蓝易云
|
8月前
|
Oracle 关系型数据库 数据库
【YashanDB知识库】oracle dblink varchar类型查询报错记录
在使用Oracle DBLink查询VARCHAR类型数据时，可能会遇到多种报错。通过了解常见错误原因，采取合适的解决方法，可以有效避免和处理这些错误。希望本文提供的分析和示例能帮助你在实际工作中更好地处理DBLink查询问题。
蓝易云
224 10
游客tvgb6vci6chtq
|
8月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能？
在 Oracle 数据库中，SQL Profiles 是优化查询性能的工具，通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括：1. 启用自动 SQL 调优；2. 手动创建 SQL Profile，涉及收集、执行调优任务、查看报告及应用建议；3. 验证效果；4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
游客tvgb6vci6chtq
389 5
31245487
|
SQL Oracle 关系型数据库
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
31245487
193 0
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询