It is the function of the RECO background process to resolve this issue. This is also where COMMIT and ROLLBACK with the FORCE option come into play. If the cause of the problem was a network failure between Sites 1, 2, and 3, then the DBAs at Sites 2 and 3 could actually call the DBA at Site 1, ask them for the outcome, and apply the commit or rollback manually, as appropriate.
There are some, but not many, limitations to what you can do in a distributed transaction, and they are reasonable (to me, anyway, they seem reasonable). The big ones are as follows:
•\ You can’t issue a COMMIT over a database link. That is, you can’t issue a COMMIT@remote_site. You may commit only from the site that initiated the transaction.
•\ You can’t do DDL over a database link. This is a direct result of the preceding issue. DDL commits. You can’t commit from any site otherthan the initiating site; hence, you can’t do DDL over a database link.
•\ You can’t issue a SAVEPOINT over a database link. In short, you can’t issue any transaction control statements over a database link. All transaction control is inherited from the session that opened the database link in the first place; you can’t have different transaction controls in place in the distributed instances in your transaction.
The lack of transaction control over a database link is reasonable, since the initiating site is the only one that has a list of everyone involved in the transaction. If in our three- site configuration, Site 2 attempted to commit, it would have no way of knowing that Site 3 was involved. In Oracle, only Site 1 can issue the commit command. At that point, it is permissible for Site 1 to delegate responsibility for distributed transaction control to another site.
We can influence which site will be the actual commit site by setting the COMMIT_ POINT_STRENGTH (a parameter) of the site. A COMMIT_POINT_STRENGTH associates a relative level of importance to a server in a distributed transaction.
The more important the server (the more available the data needs to be), the more probable that it will coordinate the distributed transaction.
You might want to do this if you need to perform a distributed transaction between your production machine and a test machine. Since the transaction coordinator is never in doubt as to the outcome of a transaction, it’s best if the production machine coordinated the distributed transaction. You don’t care so much if your test machine has some open transactions and locked resources. You certainly do care if your production machine does.
The inability to do DDL over a database link is actually not so bad at all. First, DDL is rare. You do it once at installation or during an upgrade. Production systems don’t do DDL (well, they shouldn’t do DDL). Second, there is a method to do DDL over a database link, in a fashion, using the job queue facility DBMS_SCHEDULER. Instead of trying to do DDL over the link, you use the link to schedule a remote job to be executed as soon as you commit. In that fashion, the job runs on the remote machine, is not a distributed transaction, and can do the DDL. In fact, this is the method by which the Oracle Replication Services perform distributed DDL to do schema replication.
