DBLink Question

[WebNickson]

I currently assess data from one database to another db located on another server using dblink.

My question is
1) How long does Oracle keep the dblink connection "alive" since the first connection?

2) If ODBC connection is used to execute the query using the dblink, will the ODBC connection be able to make use of the dblink connection that was created so that subsequent connections are faster.

3) Also is the same dblink connection used when sql are executed via different ODBC sessions?

Need your expert advise out there

rgds,
Nickson

 

[TaTips]

I can help you with 1.

The link is always there. Its created much in the same way as a sequence is. So the link will only expire when the link is dropped.

Happy new year.
N

 

[sem]

NikTips, can you provide the source of this information? I'm sure this is incorrect, though if you provide some solid source, I'd probably resign myself

Regards, Dima

http://seminihin.narod.ru

 

[neilsie]

Sem,

i would agree with Niktips - the link doesnt expire until you delete it. Remember, you have private or public links available to you.

br,
N.

 

[sem]

This doesn't mean that such connectionis kept opened forever. Moreover, it's not opened upon creation, just on the first request. Then the same connection may be reused by a number of sessions. Then remote server may be rebooted or an account of this link may be restricted by profile etc.
Read original question. It was about connection, not about link itself, so I see no analogy with sequences and unfortunately can not answer that question.
Remote objects are more or less "transparent", so under normal conditions it makes no difference whether you work with local or remote objects, regardless of type of connection (plain sql*net, ODBC...).

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Concur with Sem (as usual!):
The LINK is permanent, just like a procedure or a table. The only way to get rid of it is to drop it.

However, the CONNECTION is NOT permanent! Consider this scenario - if the remote server goes down, is your connection still intact? (Hint: no). Is your database link still defined? Yes. So this would disprove the original assertion.

Here is what Oracle documentation says:

When a global object name is referenced in a SQL statement or remote procedure call, database links establish a connection to a session in the remote database on behalf of the local user. The remote connection and session are only created if the connection has not already been established previously for the local user session.

The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application or user explicitly terminates them. Note that when you issue a SELECT statement across a database link, a transaction lock is placed on the rollback segments. To re-release the segment, you must issue a COMMIT or ROLLBACK statement.

Terminating remote connections established using database links is useful for disconnecting high cost connections that are no longer required by the application. You can terminate a remote connection and session using the ALTER SESSION statement with the CLOSE DATABASE LINK clause. For example, assume you issue the following transactions:

SELECT * FROM emp@sales;
COMMIT;

The following statement terminates the session in the remote database pointed to by the sales database link:

ALTER SESSION CLOSE DATABASE LINK sales;

To close a database link connection in your user session, you must have the ALTER SESSION system privilege.

 

[WebNickson]

I've gone to do some more testings. Maybe I should explain on my code and what I've observed...

Case 1 :
Code
I have an asp page that executes the sql with the dblink and of course ODBC connection is used. In addition, connection pooling is enabled for the ODBC connection.

Testing & Result
The sql when executed in sql plus takes abt 4s for the first time. Thereafter, it is like instantaneous since the sql has been cache. I got abt the same timing with the execution through the asp page except that it again takes 4s to execute once the connection pool time has expired.

Conclusion :
1) It seems that Oracle is taking that 4s to establish that first dblink connection.
2) Oracle seems to have kept the dblink connection alive for EACH session. Otherwise the query would not have been immediate when executed again with another value and before the connection pooling time expired.
3) The dblink connection seems to be different for each session because I have tried running that sql using the dblink in sql plus and then executed the same code from the asp web page immediately after, the execution still takes 4s. If statement 1 holds true, then dblink created is not shared and a new connection is created with each new session.

Do you guys think I am right?

rgds,
Nickson

 

[JtheRipper]

Interesting topic...just tagging along