Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

select in a view with a remote connection need a commit? Does this leaves open connections?

Posted on 2013-10-30
5
Medium Priority
?
1,677 Views
Last Modified: 2013-11-01
Hi experts.

I have a view in a database that connects to a remote database.  Does the select * from this view each time, leaves open transactions or does not release undo segments  if I don't use a commit or close the db link?
This I question because I read the following document:



http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_appdev002.htm

Controlling Connections Established by Database Links

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 undo segments. To rerelease 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.

Note:
Before closing a database link, first close all cursors that use the link and then end your current transaction if it uses the link.
0
Comment
Question by:LindaC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39612443
Interesting.  I was not aware of that transaction lock in the remote database.  I would suggest that a rollback would be better than a commit *IF* you only do a select across the link *AND* if you don't need a commit for something else.  But, if your query across a database link is part of a bigger process that will do a commit anyway (to commit work done in the local database) that should release the lock in the remote database.

I will test that in our system right now.
0
 
LVL 8

Author Comment

by:LindaC
ID: 39612448
I will wait then, because also it is important to know of the release of the undo segments and I dont'know if exiting will do an implicit commit or a rollback.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 39612581
When I tested here, I tried a rollback first, and that did not release the lock in the remote database.  I then tried: "ALTER SESSION CLOSE DATABASE LINK [db_link_name];"  and that released the lock.

I then repeated the query of the remote database (which opened a new session there, and acquired a new lock).  I then tried a commit, but the lock remained.  I had to repeat the:
"ALTER SESSION CLOSE DATABASE LINK [db_link_name];"  to release the lock in the remote DB.

So, I learned something today, and our Oracle 10.1 and 11.2 databases work about as this document indicates, with the exception of releasing the lock in the remote system.  In my tests, that didn't get released with either a commit or rollback, but only by explicitly closing the link.  I didn't test closing the connection in the local database, but I'm sure that would also close the DB link and release the lock.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39613184
Commit or rollback are not required as far as i am aware of the oracle stuff while doing selects on the views but i have not tested this.

But this is true and have seen on many occassions that it is better to use the alter session close database link statement to release the connections to the target database as they can be used by others when required and also it does seem to free up some memory/pointers internal to oracle on the source database as well.

"I didn't test closing the connection in the local database, but I'm sure that would also close the DB link and release the lock. " --> I guess this is true as mentioned by markgeer and pmon/smon are probably taking the responsibility to get this stuff done.
0
 
LVL 8

Author Closing Comment

by:LindaC
ID: 39616473
Thank you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question