Solved

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

Posted on 2013-10-30
5
1,213 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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now