select in a view with a remote connection need a commit? Does this leaves open connections?
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you.
I will test that in our system right now.