For some reasons, I was getting XA resource exceptions when wrapping Oracle’s XA data source into Atomikos each time an XA recovery call is made within my application. This puzzled me, so I took time to understand the error and look into Oracle’s XA implementations. Then I found out that XA recovery is by default not enabled on Oracle Express Edition (XE) 10g for all users. I was a bit surprised but maybe there’s some security issues behind this.
So solving these errors in my application was as easy as 4 lines of SQL commands:
GRANT SELECT ON sys.dba_pending_transactions TO PUBLIC;
GRANT SELECT ON sys.pending_trans$ TO PUBLIC;
GRANT SELECT ON sys.dba_2pc_pending TO PUBLIC;
GRANT EXECUTE ON sys.dbms_system TO PUBLIC:
Granting the said privileges to PUBLIC is of course only recommended on a test environment wherein you have a lot of users that may need it. This will somehow save you time to run the commands for each new user that may want to access your test DB. On production servers, it would be wise to just do GRANT for authorized users. 
[tags]Oracle Express Editions, Oracle XE, SQL, XA resources, XA Data Sources, XA recovery[/tags]
Popularity: 7% [?]