FB

SQL Developer AutocommitIn this article, I’ll explain what the SQL Developer Autocommit feature is, where to find it, and how to turn it on or off.

What Is SQL Developer Autocommit?

When you write an SQL statement in SQL Developer, you perform an operation on that data. If you’re just reading the data, this is pretty straightforward.

Where it acts differently is when you change data in the database. This is usually done with the DELETE, INSERT, or UPDATE statements.

If you run an SQL statement that does any of these functions, then the data will be updated, but it won’t be committed.

If you’re not sure what committed means (when we talk about databases), it just means that the data is not permanently saved. It allows you to see the changes in your session, and then you can make a conscious decision to update the changes permanently (committing) or undoing the changes (rolling back).

Now, this is the default behaviour. There are two separate steps to this – running the statement to change the data, and then a second command to commit the changes to the database.

SQL Developer allows you to change this default behaviour so that the statement is run and the changes are saved in a single step – hence the term “auto commit”.

Get Your SQL Cheat Sheet

Download the SQL Cheat Sheets: common commands and syntax - to save you time.
You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.
Print them or use them as an easy reference.

 

How Do I Change The Autocommit Setting in SQL Developer?

SQL Developer is pretty flexible when it comes to what you can change, and autocommit is no exception.

To find the autocommit setting, go to the Tools > Preferences.

SQL Developer Tools and Preferences

The Preferences window will appear.

SQL Developer Preferences

Click on the + icon next to Database to expand it.

SQL Developer Preferences

Then, click on Advanced.

SQL Developer Preferences Advanced

Here, you’ll see the option for Autocommit.

SQL Developer Preferences Autocommit

Click the checkbox to turn it on.

Here’s what the setting does:

ValueImpact
CheckedAutocommit is on. SQL statements that change data will have their changes automatically committed to the database.
UncheckedDefault. Autocommit is off. SQL statements that change data will not have their changes automatically committed to the database. A separate COMMIT action is needed.

What Are The Disadvantages of Autocommit?

Sure, turning on autocommit may save you time and confusion if you expect changes to be saved but they aren’t.

But there are some disadvantages.

You won’t be able to undo any of your changes by issuing a ROLLBACK statement, as you’d normally be able to do. If you run a DELETE or UPDATE statement, your changes are made permanently, and it’s much harder to get them back

It’s not impossible, though. If you have set up save points or database backups then you can restore from a backup, but it is a hassle.

Generally, I would prefer to have this autocommit option turned off in SQL Developer. The only exception would be if you’re running Oracle Express on your own computer and want to save some time with your SQL.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheet

Download the SQL Cheat Sheets: common commands and syntax - to save you time.
You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.
Print them or use them as an easy reference.