I have a middle size query with 500.000 registers from an Oracle to an DB2 system through a DBLink. Can this query block the DB2 table so that it can be updated until i close the DBLink or until i finish the job whith the query (an insert-select into an Oracle table)
It seems that it's posible to block tables with selects through Oracle dblinks, because it's not posible to define the DBLINK as READ UNCOMMITED, but the problem can be solved with another DB system which supports that feature, like SQLServer. That is what says the last post in this forum:
"There is no way to set or fool oracle into selecting with a uncommitted read even when selecting data from an external database over a DB_LINK
What we did to get around this was to by-pass the odbc driver from oracle to progress all together as any locked columns on the progress side bombed out for us when selecting over the DB_LINK even with us set transaction read only.
We instead used SQLServer as the buffer between ORACLE and Progress. With SQLServer you can set the isolation level to read uncommitted. so we created SQLServer views of progress tables via a SQLServer Linked server and then we select from those views from a DB_Link from oracle to SQLServer.
This seems to be working fine"