I’m an Oracle dinosaur, so I like using SQL Plus. Its simple, fast, comes with every version and installation and platform, and I’m very familiar with it. (And who knows, it might still be at the forefront of the Oracle development teams! http://www.slideshare.net/hillbillyToad/sqlcl-overview-a-new-command-line-interface-for-oracle-database )
But there is one important thing I always take care of when I’m using SQL Plus, and it’s easiest to explain with an example.
You start off by wanting to delete a couple of rows from a critical table as part of a patching process. You type this:
Uh oh….. I forgot the WHERE clause. That’s not so good…
Now, if I keep my cool, I can let that command finish and then type rollback, and the damage is limited to potentially blocking other users for a little while.
But of course, what normally happens, is that you see your command, your jaw drops to the floor, and you grab your mouse, race up to the top left or right corner and click to close that window
Phew ! Crisis averted – your statement never finished, so Oracle will happily clean up after you and roll all the deletions back.
But that is a very very risky thing to do … Because what if JUST WHEN YOU CLICK, the delete completes, and the screen looks like this:
Well… by default, when you exit SQL Plus, it will commit any outstanding changes. And since your delete has completed… you just wiped out your table, and you’re off to LinkedIn looking for a new place to work 😦
There is a better way. In your login.sql file for SQL Plus, make sure you always have the following:
set exitcommit off
You never, ever want to be committing (with any tool) unless you explicitly request it.
Well said Connor! AND, if you have the authority to do so adding this to the glogin.sql site can save others from autocommit as well.
oh, it works for 11g+ SQL*Plus. I prefer sqlplusw.exe. It is not supported since 10g.
Let’s start a campaign on change.org to change the default!
I alerted “Dr.DBA” to this when Oracle “secretly” released release 7.0 way back in the early 90s.
Apparently no one has detected this little gotcha since then or paid any particular attention to my alert.
Ah well, what can I say other than “Perfectly in character…”! 🙂
“…you just wiped out your table, and you’re off to LinkedIn looking for a new place to work”