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?

Answer 1

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