Re: Problem setting SERIALIZABLE in a distributed transaction.

Very curious. I don't seem to have problem doing what you are doing. What version of ORACLE are you using? Can you post your full code? I am running 9.2. Db link athena connects to a 2nd database on the same server. I don't think placing the 2nd db on a 2nd server would make any difference.

SQL> set transaction isolation level serializable;

Transaction set.

SQL> select * from test2_at_athena;

C1 C2
-- ----------

a           1
b           2
c           3
e           1
K           6
a           4
a           5
a           6
a           7
a           6
a           7

C1         C2
-- ----------
G           1

12 rows selected.

SQL> insert into test2_at_athena values('P',0);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test2_at_athena;

C1 C2
-- ----------

a           1
b           2
c           3
e           1
K           6
P           0
a           4
a           5
a           6
a           7
a           6

C1         C2
-- ----------
a           7
G           1

13 rows selected.

SQL> Alasdair I MacLeod <alasdair_i_macleod_at_talk21.com> wrote in message news:<3D5A8034.DD61CECC_at_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 Thu Aug 15 2002 - 21:14:20 CDT