CODEWITHSUNDEEP
sqlhana
user2357112
user2357112

Reputation: 281381

Determining the current AUTOCOMMIT DDL setting

With SET TRANSACTION AUTOCOMMIT DDL ON and SET TRANSACTION AUTOCOMMIT DDL OFF, you can set whether your session automatically commits after any DDL statement.

Is there a way to determine what you currently have it set to? I want to be able to write code that temporarily uses a certain value of the setting and then restores the original, or figure out whether I've accidentally been using the wrong setting.

Upvotes: 1

Views: 1451

Answers (1)

Lars Br.
Lars Br.

Reputation: 10396

Yes, there is. If the AUTOCOMMIT DDL has been set to OFF, then M_SESSION_CONTEXT contains the key DDL_AUTO_COMMIT with value FALSE.

select key, value 
    from m_session_context 
where 
    connection_id = current_connection
and key ='DDL_AUTO_COMMIT';

KEY             VALUE
DDL_AUTO_COMMIT FALSE

So, if you don't find this record, the AUTOCOMMIT DDL mode is ON (default).

Upvotes: 2

Related Questions