oracle - commit over dblink?

sql database oracle commit dblink
toop picture toop · Mar 6, 2012 · Viewed 13.7k times · Source

If I connect to an oracle database as user smith, and issue the following 3 commands:

update smith.tablea
set col_name = 'florence' where col_id = 8;

insert into bob.other_table@mylink
values ('blah',2,'uncle','new');

commit;

Does this mean that the update to the local table (smith.tablea) and the insert to the remote db table (bob.other_table) have both been committed or that just the update to the local table has been committed?

Note: that 'mylink' represents a dblink to a remote database.

Answer

Marcin Wroblewski picture Marcin Wroblewski · Mar 6, 2012

From documentation

The Oracle two-phase commit mechanism is completely transparent to users who issue distributed transactions. In fact, users need not even know the transaction is distributed. A COMMIT statement denoting the end of a transaction automatically triggers the two-phase commit mechanism to commit the transaction. No coding or complex statement syntax is required to include distributed transactions within the body of a database application.

so - yes, if everything goes fine, both operations are commited.

Related questions

oracle - what statements need to be committed?

What are the list of statements that need to be committed before further action on the table in order to avoid a lock? I am not talking about full transactions with multiple statements and transaction integrity; instead I am referring …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More