A Transaction Is Not Ended After A DDL or select Commands using dblink (Doc ID 1618928.1)

Last updated on FEBRUARY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

Oracle documentation says:

End of a Transaction

A transaction ends when any of the following actions occurs:
• A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
• In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits. The transaction shown in Figure 10-1 ends with a commit.
• A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER.
• The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.

http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm#CNCPT88953

As a transaction is not ended after a DDL using DBlink command and getting ORA-01453

create database link remote using 'utf11g2b';

Database link created.

drop table foo;

Table dropped.

create table foo( a char(10) );

Table created.

select xidusn,xidslot,xidsqn from v$transaction;

no rows selected

create or replace view goo as select oops from goo@remote
                                    *
ERROR at line 1:
ORA-00904: "OOPS": invalid identifier

   XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
        4         18      76748

set transaction read write name 'INSERT_INTO_FOO'
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction

========================

Also the ora-01453 reported after select using DBLINK

SQL> CREATE DATABASE LINK orcl_db connect to hr identified by hr using 'orcl';
Database link created.
SQL> select sysdate from dual@orcl_db;
SYSDATE
---------
15-JAN-14

SQL> set transaction read write name 'INSERT_INTO_FOO';
set transaction read write name 'INSERT_INTO_FOO'
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms