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
Solved

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

Posted on 2013-10-30
5
1,342 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
  • 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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

856 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