In 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”.
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.
The Preferences window will appear.
Click on the + icon next to Database to expand it.
Then, click on Advanced.
Here, you’ll see the option for Autocommit.
Click the checkbox to turn it on.
Here’s what the setting does:
| Value | Impact |
| Checked | Autocommit is on. SQL statements that change data will have their changes automatically committed to the database. |
| Unchecked | Default. 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.
One thing to mention, even though it probably merits it’s own post, is Oracle’s flashback. Granted it’s more important when working in SQL Workshop (Apex dev environment) since auto commit is on and no one is pushing to turn it off. But who hasn’t wished they’d not committed.
But auto commit with Flashback enabled is an option as well. Personally, I have used it more than once in my Apex environment.
As I am fond of saying, it’s not if I mess up the database, but when.
That’s true Charles, flashback is a helpful and important feature. Good saying too!