Forum Stats

  • 3,750,233 Users
  • 2,250,137 Discussions
  • 7,866,845 Comments

Discussions

ORA-02089: COMMIT is not allowed in a subordinate session

user5549222
user5549222 Member Posts: 15 Green Ribbon

I am getting "ORA-02089: COMMIT is not allowed in a subordinate session" when I used below query in Stored procedure in Oracle.

execute immediate 'truncate table xxx';

Actually, when I ran stored procedure with above query in SQL developer, it is working fine. But, when I triggered from Java code, it is giving "ORA-02089: COMMIT is not allowed in a subordinate session" issue.

Can someone help me on this?

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,650 Black Diamond
    edited Jul 23, 2021 10:42AM Accepted Answer

    TRUNCATE is DDL and DDL commits implicitly. Exception ORA-02089: COMMIT is not allowed in a subordinate session is raised in XA applications. XA uses two-phase commit (we commit explicitly using xa_commit) and doesn't support implicit commits, therefore DDL statements are not allowed in XA. So most likely your Java application is using XA. That's why it runs in SQL*Developer which isn't using XA and fails in your XA using Java.

    SY.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,650 Black Diamond
    edited Jul 23, 2021 10:42AM Accepted Answer

    TRUNCATE is DDL and DDL commits implicitly. Exception ORA-02089: COMMIT is not allowed in a subordinate session is raised in XA applications. XA uses two-phase commit (we commit explicitly using xa_commit) and doesn't support implicit commits, therefore DDL statements are not allowed in XA. So most likely your Java application is using XA. That's why it runs in SQL*Developer which isn't using XA and fails in your XA using Java.

    SY.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond

    Dynamic DDL (like truncating a table) is not a thread safe approach in a multi-process and multi-session architecture. Also does not make much sense from a basic processing perspective - if the table's content is transient then consider using a (global) temporary table.

    As for the XA issue spotted by Sy - try the DDL call from a procedure using an autonomous transaction. Likely will not work (I never used XA explicitly), but worth a quick check.