Oracle Database Global Transaction Support
This topic is for the Oracle 10g and 11g source connector types. Oracle has global transaction support, but you must configure your Oracle database instances for shared server mode. If you do not, when you try to link two Oracle databases, you receive an ORA-24777 (use of non migrateable database link not allowed) error. This is because the transaction, which must be able to migrate between sessions, tried to access a remote database from a non multi-threaded server process.
To configure an Oracle database instance
To resolve this issue, either do your work on the local database or open a connection to the remote database from the client. If the multi-threaded server option is installed, connect to the Oracle instance through the dispatcher. Then follow the configuration instructions:
• Existing databases (already configured) - You can use the Oracle DataBase Configuration Assistant to change the mode of the database. Open the database and choose Configure Database Options in a database. Click Next and then Next again. In the screen provided, set to either dedicated or shared mode. After changing this setting, stop and then restart the service for the change to take effect.
• New databases - When you create a new database, you can select shared mode from the initial setup options.