Re: Multi Threaded server not working for many inserts?

<sybrandb_at_yahoo.com> wrote in message
news:a1d154f4.0403120721.45a16560_at_posting.google.com...
> "Royco" <bla_at_bla.nl> wrote in message

news:<c2s5do$m2m$1_at_azure.qinip.net>...
> > Hi,
> >
> > Sorry for the cross-posting but I am not sure which newsgroup to use.
> > We have the following configuration
> > Oracle 8.1.7.3.0 running on VMS, 2 database-instances and on both
instances
> > Multi Threaded Server is configured.
> >
> > There is a databaselink defined from instance B to instance A and we
defined
> > a synonym for tableA in instance B
> > We log-on to instance B.
> >
> > Now we want to do the following (from SQLPLUS).
> >
> > insert into tableA (on instance A) select field1, field2, field3 from
tableB
> > (on instance B)
> >
> >
> > It looks that when the select only returns 1 row the insert into the
other
> > instance works.
> > But when the selects returns more rows the insert will hang. No error is
> > reported back from Oracle.
> >
> > The only thing we can do is let our DBA 'kill' the underlying proces.
> >
> > Are there any known bugs and workarounds in Multi Threaded Server
> > configurations which indicate problems with large inserts????
> >
> >
> > Please help (google didn't give me any answers)
> > Roy
>
>
> Why use MTS for a database link? Looks like a bad idea to me. Just set
> up an appropiate tnsnames.ora entry on the server a to b adding
> (server=dedicated) after (service_name=) or (sid=) and retry.
>

Hi,

It's more a 'cause-and-effect' for the reason why we use Multi Threaded Server.

We make C++ programs/objects (using OCI) running under Windows 2000-Component Server.
The components are therefor marked 'transactional' and Oracle states in their manuals that 'Using databaselinks in a transactional aware environment needs Multi Threaded Server configured on all instances which are included in the databaselinks'

(if Multi Threaded Server is not configured we recieve error ORA-24777: Use of Non-migratable database link not allowed).

We now found out that a 'pull' mechanisme works fine. So
insert into tableA_at_instanceA select field1, field2, field3 from tableB_at_instanceB

when we log-on to instanceA (pull) works fine (...uh...we haven't had Oracle hanging YET)
but when we log-on to InstanceB (push) we still have the hanging problem.

so.... again... does this sound like a known issue?

Roy Received on Tue Mar 16 2004 - 02:24:26 CST