Oracle DBLink連線數過多的問題(Ora-02020)

strivechao發表於2019-04-02
Oracle
報錯全資訊：
Error:OR A -04052在查: 找遠端物件 NIP.PB_PERADDRESSLIST@DB_NIP 時出錯
       ORA-00604 : 遞迴 SQL 級別 1 出現錯誤
        ORA-02020 : 過多的資料庫連結在使用中
 
檢視一下有關link的引數
SQL>  show parameter open_links
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer     4
open_links_per_instance              integer     4
 
SQL>
 
引數的解釋：
***open_links :每個session最多允許的dblink數量;
***open_links_per_instance:指每個例項最多允許的dblink個數
擴大允許使用dblink 量；
 
SQL> alter system set open_links=50 scope=spfile;
 
System altered
SQL> alter system set open_links_per_instance=50 scope=spfile;
 
System altered
 
SQL> show parameter open_links
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                                                  integer     4
open_links_per_instance              integer     4
 
SQL> 
 
重啟後生效...
 
SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> 
SQL> startup
ORACLE 例程已經啟動。

Total System Global Area  293601280 bytes
Fixed Size                  1248600 bytes
Variable Size              96469672 bytes
Database Buffers          188743680 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> 
 
引數檢查：
SQL>  show parameter open_links
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer     50
open_links_per_instance              integer     50
SQL>
 
在編譯就OK咧.
----------------------
How to view open dblink connections (Doc ID 387848.1)

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 
------------------------- ------ ----- ----- -------- ------- --- ------ ------ 
INST2.ACME.COM 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#, 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 bugmnt INACTIVE
68 11
celclnx1.us.oracle.com

29318 pts/15
sqlplus@celclnx1.us.oracle.com (TNS V1-V3)

 

REPADMIN bugmnt INACTIVE
232 5
celclnx1.us.oracle.com

28081 pts/14
sqlplus@celclnx1.us.oracle.com (TNS V1-V3)

SQL>

-----------------------------------------------

SQL> alter system set open_links=255 scope=spfile;
alter system set open_links=255 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup


SQL> alter system set open_links=255 scope=both; 
alter system set open_links=255 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set open_links_per_instance=255 scope=spfile;
alter system set open_links_per_instance=255 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup


SQL> alter system set open_links_per_instance=255 scope=both; 
alter system set open_links_per_instance=255 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25469263/viewspace-2640138/，如需轉載，請註明出處，否則將追究法律責任。

相關文章