Problem setting SERIALIZABLE in a distributed transaction.

From: Alasdair I MacLeod <alasdair_i_macleod_at_talk21.com>
Date: Wed, 14 Aug 2002 17:07:16 +0100
Message-ID: <3D5A8034.DD61CECC@talk21.com>

I'm trying to transfer data from one database to another and would like to do it within a SERIALIZABLE transaction (I need to modify my local data, do some processing and then do inserts into a remote table whilst my local table is still growing).

I thought I could do

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Do some local stuff

• INSERT INTO table_at_remote_db COMMIT;

But I get an error message
ORA-02070 database <remote db> does not support set transaction in this context

Is what I'm attempting possible? (I'm struggling up a very steep oracle learning curve). I could simulate this with my own synchronisation logic but would rather go with what seems a simpler solution.

The default tx isolation level of both local and remote dbs is READ COMMITTED. I've read (in the pile of oracle books beside me) that the "compatible" param must be at least 7.3.0 - does this refer to the entry in init.ora? If so mine reads 8.1.0 and I'm waiting to hear what the other end is.

Any suggestion would be gratefully received.

Alasdair.

ps is what I've described a "distributed transaction" the docs also talk about a "remote transaction" but I'm not clear on the difference. Received on Wed Aug 14 2002 - 11:07:16 CDT