java.sql.SQLException:ORA-24778:無法開啟串連

標籤：des   style   blog   io   ar   color   os   sp   for   

      weblogic日誌中報：java.sql.SQLException:ORA-24778:無法開啟串連。分析功能是有多次對遠端資料表的操作,先查看關於db_link的參數：

SQL> show parameter open_links
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
open_links                           integer     4
open_links_per_instance              integer     4

修改參數後重啟系統：

alter system set open_links=50 scope=spfile;
alter system set open_links_per_instance=255 scope=spfile;

官方文檔大致的意思是：

open_links是單個會話開啟遠端連線的最大數目，包括外部的預存程序，外部的進程（反正就是外部的東西）。預設值是4，最大值是255，當設定為0的時候說明沒有分散式交易。

open_links_per_instance是一個執行個體開啟遠端連線的最大數目。預設值是4，最大是4294967295，運用於JDBC的XA驅動，而open_links不運用於JDBC的XA驅動。

我認為open_links_per_instance大小設定應該是open_links的幾倍，且在剛建立資料庫時就應該明確，後期修改重啟有點鬱悶。

OPEN_LINKS

Property	Description
Parameter type	Integer
Default value	4
Modifiable	No
Range of values	0 to 255

OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.

Oracle counts one open link for the following:

• For each user that references a public or private database link

• For each external procedure or cartridge connection when it is executed for the first time

Both types of connections close when the session ends. You can also close a database link connection explicitly by issuing an ALTER SESSION CLOSE DATABASE LINK statement.

You should set this parameter to allow for the external procedure and cartridge connections expected during the session plus the number of databases referred to in typical distributed transactions (that is, a single SQL statement that references multiple databases), so that all the databases can be open to execute the statement. For example, if queries alternately access databases A, B, and C, and OPEN_LINKS is set to 2, time will be lost waiting while one connection is broken and another made. Increase the value if many different databases are accessed over time.

This parameter refers only to connections used for distributed transactions. Direct connections to a remote database specified as an application connects are not counted.

If you set OPEN_LINKS to 0, then no distributed transactions are allowed.

OPEN_LINKS_PER_INSTANCE

Property	Description
Parameter type	Integer
Default value	4
Modifiable	No
Range of values	0 to 4294967295 (4 GB -1)
Real Application Clusters	Multiple instances can have different values.

OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open connections globally for each database instance. XA transactions use migratable open connections so that the connections are cached after a transaction is committed. Another transaction can use the connection, provided the user who created the connection is the same as the user who owns the transaction.

OPEN_LINKS_PER_INSTANCE is different from OPEN_LINKS, which indicates the number of connections from a session. The OPEN_LINKS parameter is not applicable to XA applications.

java.sql.SQLException:ORA-24778:無法開啟串連