Re: dblink question....

Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid> wrote:
>gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
>>Sorry for all the questions...
>>I have 2 db instances, connected via dblinks. I've got some
>>shared tables which I have synonyms created for. I'm noticing
>>that connections are persisting across the links even after
>>transactions are completed.
>>
>>For example, 10 connections into instance #1 that update a
>>synonym for a table on instance #2, open 10 sessions on
 instance
>>#2. (verified from v$session)...However, once the inserts are
>>finished, the connections are still held on instance #2.
>>
>>Is there anyway to free up these connections over the dblink
>>after the transactions are finished. I'm concerned about
>>resource consumption of instance #2.
>>
>>As an alternative, I've thought about using replication. Or
>>possibly creating my own replication procedure (combined with
>>the built in oracle scheduler).
>>
>>Thanks for any advice.
>>
>Yes, if you are concerned about the resources used by the
>distributed transactions you can close the link connection used
>by a session from the session:
>
>'alter session close database link link_name ;'

Both databases are 8.1.5. The above statement will work very well for me since already all of the DML we do is inside procedures.

PL/SQL doesn't seem to like the alter session statement inside a procedure, so placed it inside an EXECUTE IMMEDIATE call. Is this the correct approach?

Thanks for your help!
>
>You will need to modify the affected programs to contain this
>command which means the users need alter session privilege or
>you will need to encapsulate the code in a stored procedure.
>
>
>-- Mark D. Powell -- The only advice that counts is the advice
 that
>you follow so follow your own advice. --
>
>-----------------------------------------------------------
>
>Got questions? Get answers over the phone at Keen.com.
>Up to 100 minutes free!
>http://www.keen.com
>
>

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Fri Aug 11 2000 - 00:00:00 CDT