Configuring Oracle for XA

Oracle 10.2.0.3 and lower

NOTE: this tip was written with Oracle DBMS version 10.2.0.2.0

How can you know if your Oracle server is correctly configured for XA?

The simplest and usually the most effective way is the following:

• logon to SQLPlus (or use another tool if you like); make sure to use the user/password that you are using for your application
• execute the query: select * from SYS.DBA_PENDING_TRANSACTIONS;

If this works then XA is configured.

If not then you should do one or all of the following:

1. run the oracle scripts 'initxa.sql' and 'xaview.sql' (or ask your DBA to do this for you).
2. ask your DBA to grant select rights (to your user) on SYS.DBA_PENDING_TRANSACTIONS
3. for the driver ojdbc14.jar, you also need execute rights on SYS.DBMS_SYSTEM

The last step is NOT needed for the older (JDK 1.2) drivers in classes12.jar

Oracle 10.2.0.4, 11g and higher

For Oracle 10.2.0.4 and up, you no longer need execute rights on SYS.DBMS_SYSTEM. This was fixed in Oracle 10.2.0.4 (BUG 5945463).

Make sure you are also using client driver ojdbc14 version 10.2.0.4 and up.

If all else fails

Try this:

grant execute on dbms_xa to <user> ;
grant select on pending_trans$ to <user>; 
grant select on dba_2pc_pending to <user>;
grant select on dba_pending_transactions to <user>;