===============================
General
===============================
DB_LINKS by Example.
Some notes, example, troubleshooting.
===============================
DB_LINKS related tables
===============================
DBA_DB_LINKS and V$DBLINK
DBA_DB_LINKS lists all defined db_links in the Instance.
V$DBLINK lists currently open db_links by the session.
You cannot query V$DBLINK for other sessions, even as privileged user!!!
You cannot query V$DBLINK for other sessions, even as privileged user!!!
V$DBLINK columns
db_link Db link name
owner_id Owner name
logged_on Is the database link currently logged on
protocol Dblink's communications protocol
open_cursors Are there any cursors open for the db link
in_transaction Is the db link part of an open transaction which
update_sent Was there an update on the db link
===============================
How to control the number of DB Links:
===============================
SELECT name, value from V$PARAMETER WHERE name like '%link%';
NAME VALUE
------------------------------ ------------------------------
open_links 12
open_links_per_instance 4
OPEN_LINKS is a static parameter, it cannot be modified in run time.
ALTER SYSTEM SET OPEN_LINKS=10 SCOPE=BOTH;
ALTER SYSTEM SET OPEN_LINKS=10 SCOPE=BOTH
*
ERROR at line 1:
ORA-02095: specified initialization parameter
cannot be modified
ALTER SYSTEM SET OPEN_LINKS=10 SCOPE=SPFILE;
System altered.
===============================
ORA-02020: too many database links
===============================
If number of open DB_LINKS exceeds the limit set by open_links,exception is thrown:
ORA-02020: too many database links in use
===============================
Closing a DB_LINK.
===============================
Per Oracle documentation:
"If you access a database link in a session, then the link remains open until you close the session.
A link is open in the sense that a process is active on each of the remote databases accessed through the link."
The only way to forcefully close a database link is to issue commit, even if the session does not perform any DML operations, and then, optionally, explicitly close DB_LINK with ALTER SESSION CLOSE DATABASE LINK.
Per Oracle documentation:
"If you access a database link in a session, then the link remains open until you close the session.
A link is open in the sense that a process is active on each of the remote databases accessed through the link."
The only way to forcefully close a database link is to issue commit, even if the session does not perform any DML operations, and then, optionally, explicitly close DB_LINK with ALTER SESSION CLOSE DATABASE LINK.
commit;
ALTER SESSION CLOSE DATABASE LINK <link_name>;
This is equivalent to using DBMS_SESSION.CLOSE_DATABASE_LINK.
BEGIN
commit;
commit;
DBMS_SESSION.CLOSE_DATABASE_LINK('link_name');
END;
===============================
Connection via DBLink Hangs
===============================
When connecting via db link - the connection hangs
When connecting via sqlplus - all is well
-?
Per Oracle technote, might need to increase SDU size:
SQL Hangs When Using Database Link (Doc ID 551117.1)
SDU stands for Session Data Unit - and is a controls the Session OSI layer buffers size.
SDU is the Session Data Unit of the NS layer and regulates the size of the sent and read data to the NT layer.
Default value is 2048 bytes.
To override value, set (SDU=8192) in tnsnames.ora
The possible value, should be a multiplyer of 2048 (2048, 4096,8196...)
ARM_ARTEL_IPN =(DESCRIPTION = (SDU=8192)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST = 100.200.300.400)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME = igt)))
===============================
Reference
===============================
No comments:
Post a Comment