oracle dblink性能,Oracle DBLink连接数过多的问题(Ora-02020)

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>

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