We also use an Oracle Database synchronized with a SQL2000 Server.
The first thing to know is that ther is a wellknown bug in that context.
The linked Server works but so slow!
If you need only SELECT statements on Oracle DB better use an OPENROWSET command
like this one:
SELECT MAT_USR, NOM_USR, PRENOM_USR, SIGNET_USR, CC_DEFLT_USR, STATUS_USR into #b
'Select DISTINCT FIELD1, FIELD2, FIELD3, FIELD4 instance.table1 abc where abc.FIELD1 = 3')
The only little problem is that it does not work when called though a storedproc or client code ...
-> 'OraOLEDB.Oracle' does not support distributed transactions."
but I also dicoverd a walkaround :)))
-> Put this into a job executed by a trigger you fire on an update of a TOTO table.