What happens when you exit sqlplus without using a commit statement. It does a rollback, right? Wrong!
We’ve been doing some scripting in SQLPlus and I was asked this question, and I gave a confident reply - of course it rolls back. And then decided to test this.
My assumption was if you did not commit, your transactions would ROLLBACK. It seems I was totally wrong!!
$ sqlplus jde@jdeprod
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 24 15:21:50 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Jul 24 2019 15:21:43 +10:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> create table shae (username varchar(20)) ;
Table created.
SQL> insert into shae values ('nigel') ;
1 row created.
SQL> quit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
I would think that now, this record would not exist… but as you can see, after a standard quit (or exit), a commit is issued!
$ sqlplus jde@jdeprod
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 24 15:24:43 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Jul 24 2019 15:24:38 +10:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> select count(1) from shae ;
COUNT(1)
----------
1
SQL> select * from shae ;
USERNAME
--------------------
nigel
SQL> update shae set username = 'ralph' ;
1 row updated.
SQL> quit
Holy moly!
You need to do this if you want to rollback, specify it in the exit command:
SQL> update shae set username = 'testing';
1 row updated.
SQL> exit rollback ;
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
$ sqlplus jde@jdeprod
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 24 15:59:10 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Jul 24 2019 15:59:05 +10:00
Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from shae
2 ;
USERNAME
--------------------
shannon
And the supporting documentation
EXIT
Syntax
{EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Commits or rolls back all pending changes, logs out of Oracle Database, terminates SQL*Plus and returns control to the operating system.
In iSQL*Plus, commits or rolls back all pending changes, stops processing the current iSQL*Plus script and returns focus to the Input area. There is no way to access the return code in iSQL*Plus. In iSQL*Plus click the Logout button to exit the Oracle Database.
Commit on exit, or commit on termination of processing in iSQL*Plus, is performed regardless of the status of SET AUTOCOMMIT.
No comments:
Post a Comment