Why COMMIT resolved the issue after execute immediate?

Question

BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';
     EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML' ;
END;
/

getting following error in 1 out of 3 oracle servers. oracle : 11.2.0.4

ora 12841 cannot alter the session parallel DML state within a transaction

But the system worked fine after adding COMMIT

BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';
     COMMIT;
     EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML' ;
END;
/

Why that issue not seen after adding COMMIT?

score 3 · Answer 1 · answered Apr 05 '15 at 14:35

3

This:

EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';

... started a new transaction, after which you attempted to alter the session parallel DML state.

COMMIT finished the transaction and started another, therefore you were then able to alter the session parallel DML state.

Docs on transactions here: https://docs.oracle.com/database/121/CNCPT/transact.htm

Edit: possibly the BEGIN is confusing you. In PostgreSQL, BEGIN starts a transaction: http://www.postgresql.org/docs/9.1/static/sql-begin.html. In Oracle "a transaction begins when the first executable SQL statement is encountered".

answered Apr 05 '15 at 14:35

David Aldridge

51,479
8
68
96

If COMMIT is must have then why it is not breaking in all oracle servers. This issue was seen only in 1 server while the statement worked fine without COMMIT in other servers. – touchchandra Apr 05 '15 at 14:43
1

Hmmm, I wonder if it could be that the "ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE" is not what is starting the transaction, but another statement prior to it is? If on one server that previous statement was not executed then it would explain the difference. – David Aldridge Apr 05 '15 at 14:47
What you might try is connecting to the database, executing a COMMIT, executing "SELECT XID FROM V$TRANSACTION;", then "ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE", then "SELECT XID FROM V$TRANSACTION;" again. Post what is returned from the select statements. – David Aldridge Apr 05 '15 at 14:49
1

Thanks David, the issue is now resolved. the issue was that the there was separate open link connection. – touchchandra Apr 05 '15 at 16:02

touchchandra · Accepted Answer · 2015-04-10T09:40:26.363

This issue happens if you have some statements that starts any transaction prior to the mentioned block.

In My case there was simple select from the synonym which was causing the error. Once adding commit after that transaction the block executed fine.

Even in your case if after adding COMMIT prior to following statement executes without error then it is for-sure that there is some transaction for which you are missing commits.

COMMIT;  
EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES => TRUE'; 
EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML' ;

For me this link was helpful http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_admin004.htm#ADMIN12167

Thanks/Refer @DavidAldridge for more clarity

Why COMMIT resolved the issue after execute immediate?

2 Answers2