Skip to main content

Was it rolled back



    Ever had that experience?

    Was it really a rollback?

    This post will compel you to think about Oracle COMMIT/ROLLBACK!!!..

    I have – many times over when working on SQPLUS.

    Any SQLPLUS connection initiates a transaction irrespective of its type. You might be doing a series of databases changes and while coming out from the SQLPLUS, you did a clean exit, however, forgot doing a COMMIT/ROLLBACK.

    Do you think that you lost the changes…..

    No really!!!!!......

    Oracle does AUTOCOMMIT when you do a clean exit from SQLPLUS, but when abnormal termination of SQLPLUS, don't capture uncommitted changes. This pattern is clearly visible in the session trace where XCTEND (transaction END) explains about it.

    XCTEND rlbk=%d rd_only=%d
    ----------------------------------------------------------------------------
    XCTEND A transaction end marker.
    rlbk 1 if a rollback was performed, 0 if no rollback (commit).
    rd_only 1 if transaction was read only, 0 if changes occurred.

    XCTEND rlbk=0, rd_only=0
    Commit followed by a change
    XCTEND rlbk=0, rd_only=1
    Commit but transaction was read only

    And tested it on various versions of Oracle with 10046 session trace (level 12) trace.

    Open a SQLPLUS connection
    Statement.execute(insert)            inset into abc values (1,1);
    close connection                              exit;


    1. Oracle 8.1.7.4 (auto commit off, inset and exit)
    =====================
    PARSING IN CURSOR #1 len=29 dep=0 uid=68 oct=2 lid=68 tim=0 hv=4004558140 ad='3fb259f4'
    insert into abc values (1,1)
    END OF STMT
    PARSE #1:c=0,e=0,p=0,cr=1,cu=0,mis=1,r=0,dep=0,og=4,tim=0
    BINDS #1:
    EXEC #1:c=0,e=0,p=0,cr=1,cu=8,mis=0,r=1,dep=0,og=4,tim=0
    WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
    *** 2019-05-10 06:59:15.327
    WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
    XCTEND rlbk=0, rd_only=0                                                                                                                     --->>>>>> Clean exit caused commit without a manual commit.
    WAIT #0: nam='log file sync' ela= 0 p1=150 p2=0 p3=0

    1. Oracle 9.2.0.6.0 (auto commit off, inset and exit)
    =====================
    PARSING IN CURSOR #1 len=28 dep=0 uid=154 oct=2 lid=154 tim=1520995508029588 hv=958750158 ad='17712390'
    insert into abc values (1,1)
    END OF STMT
    PARSE #1:c=0,e=14016,p=1,cr=4,cu=0,mis=1,r=0,dep=0,og=4,tim=1520995508029587
    BINDS #1:
    EXEC #1:c=0,e=321,p=0,cr=1,cu=7,mis=0,r=1,dep=0,og=4,tim=1520995508029970
    WAIT #1: nam='SQL*Net message to client' ela= 18 p1=1650815232 p2=1 p3=0
    WAIT #1: nam='SQL*Net message from client' ela= 1702142 p1=1650815232 p2=1 p3=0
    XCTEND rlbk=0, rd_only=0                                                                                                                      --->>>>>> Clean exit caused commit without a manual commit.
    WAIT #0: nam='log file sync' ela= 683 p1=289 p2=0 p3=0

    Terminate SQPLUS abnormally, no commit for un-committed data
    =====================
    PARSING IN CURSOR #1 len=28 dep=0 uid=154 oct=2 lid=154 tim=1520995668096475 hv=958750158 ad='17712390'
    insert into abc values (1,1)
    END OF STMT
    PARSE #1:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1520995668096474
    BINDS #1:
    EXEC #1:c=0,e=195,p=0,cr=1,cu=2,mis=0,r=1,dep=0,og=4,tim=1520995668096718
    WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0

    <<< No trace of commit or rollback>>

    1. 10.2.0.5.0 (auto commit off)
    =====================
    PARSING IN CURSOR #1 len=29 dep=0 uid=41 oct=2 lid=41 tim=54076777936127 hv=4081149660 ad='2de4f4d8'
            insert into abc values (1,1)
    END OF STMT
    PARSE #1:c=0,e=253,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=54076777936126
    BINDS #1:
    EXEC #1:c=0,e=114,p=0,cr=1,cu=3,mis=0,r=1,dep=0,og=1,tim=54076777936275
    WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=54076777936294
    WAIT #1: nam='SQL*Net message from client' ela= 177 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=54076777936494
    XCTEND rlbk=0, rd_only=0                                                                                                                     --->>>>>> Clean exit caused commit without a manual commit.
    WAIT #0: nam='log file sync' ela= 1420 buffer#=1719 sync scn=778977306 p3=0 obj#=-1 tim=54076777938249

    Terminate SQPLUS abnormally, no commit for un-committed data
    =====================
    PARSING IN CURSOR #2 len=28 dep=0 uid=41 oct=2 lid=41 tim=54076973866562 hv=4189345757 ad='2de4e0a8'
    insert into abc values (1,1)
    END OF STMT
    PARSE #2:c=0,e=879,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=54076973866559
    BINDS #2:
    EXEC #2:c=0,e=181,p=0,cr=1,cu=3,mis=0,r=1,dep=0,og=1,tim=54076973866802
    WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=54076973866836

    <<< No trace of commit or rollback>>

    1. 11.2.0.3 (auto-commit off, inset and exit)
    =====================
    PARSING IN CURSOR #4573582016 len=28 dep=0 uid=42 oct=2 lid=42 tim=122673609722161 hv=4189345757 ad='7000000ee3d63e0' sqlid='cnrrryvwv8pyx'
    insert into abc values (1,1)
    END OF STMT
    PARSE #4573582016:c=740,e=1210,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=122673609722160
    EXEC #4573582016:c=144,e=269,p=0,cr=1,cu=2,mis=0,r=1,dep=0,og=1,plh=0,tim=122673609722484
    STAT #4573582016 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=223 us)'
    WAIT #4573582016: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=122673609722546

    *** 2019-05-10 15:56:53.101
    WAIT #4573582016: nam='SQL*Net message from client' ela= 4418658 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=122673614141222
    XCTEND rlbk=0, rd_only=0, tim=122673614141357                                                                           --->>>>>> Clean exit caused commit without a manual commit.
    WAIT #0: nam='log file sync' ela= 1680 buffer#=2140 sync scn=980847163 p3=0 obj#=-1 tim=122673614143202
    CLOSE #4573582016:c=5,e=9,dep=0,type=0,tim=122673614143242

    Terminate SQPLUS abnormally, no commit for un-committed data
    =====================
    PARSING IN CURSOR #4573580840 len=28 dep=0 uid=42 oct=2 lid=42 tim=122673710725269 hv=4189345757 ad='7000000ee3d63e0' sqlid='cnrrryvwv8pyx'
    insert into abc values (1,1)
    END OF STMT
    PARSE #4573580840:c=63,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=122673710725268
    EXEC #4573580840:c=181,e=297,p=0,cr=1,cu=2,mis=0,r=1,dep=0,og=1,plh=0,tim=122673710725621
    STAT #4573580840 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=244 us)'
    WAIT #4573580840: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=122673710725720

    <<< No trace of commit or rollback>>


    1. 12.1.0.2.0 (auto-commit off, inset and exit)
    =====================
    PARSING IN CURSOR #4857144720 len=29 dep=0 uid=69 oct=2 lid=69 tim=167591381335333 hv=4081149660 ad='70001004cac4838' sqlid='dxcjxz7tn2tqw'
            insert into abc values (1,1)
    END OF STMT
    PARSE #4857144720:c=2819,e=63004,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=167591381335332
    EXEC #4857144720:c=109,e=181,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=0,tim=167591381335583
    STAT #4857144720 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  ABC (cr=1 pr=0 pw=0 time=132 us)'
    WAIT #4857144720: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=167591381335651
    WAIT #4857144720: nam='SQL*Net message from client' ela= 714 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=167591381336394
    XCTEND rlbk=0, rd_only=0, tim=167591381336451                                                                           --->>>>>> Clean exit caused commit without a manual commit.
    WAIT #0: nam='log file sync' ela= 1420 buffer#=3225 sync scn=476572878 p3=0 obj#=-1 tim=167591381338010
    CLOSE #4857144720:c=5,e=10,dep=0,type=0,tim=167591381338062
    CLOSE #4857009160:c=3,e=5,dep=0,type=0,tim=167591381338100

    Terminate SQPLUS abnormally, no commit for un-committed data
    =====================
    PARSING IN CURSOR #4857155928 len=28 dep=0 uid=69 oct=2 lid=69 tim=167591527104954 hv=4189345757 ad='70001004ca65c98' sqlid='cnrrryvwv8pyx'
    insert into abc values (1,1)
    END OF STMT
    PARSE #4857155928:c=1449,e=2385,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=167591527104953
    EXEC #4857155928:c=143,e=238,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=0,tim=167591527105259
    STAT #4857155928 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  ABC (cr=1 pr=0 pw=0 time=189 us)'
    WAIT #4857155928: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=167591527105331

    <<< No trace of commit or rollback>>


    Since this was an inbuilt default feature of SQLPLUS to send a COMMIT post while doing clean EXIT, however, killed SQLPLUS session did not have the opportunity to send a COMMIT on EXIT and then PMON comes into the picture to do the process recovery which don't find any COMMIT mark for the failed session and move on for rollback for killed SQLPLUS session and does the process clean-up.


    Here is EXIT behavior of SQLPLUS.

    SET EXITC[OMMIT] {ON | OFF}

    Above syntax specifies whether the default EXIT behavior is COMMIT or ROLLBACK, however, the default setting is ON, which means that work is committed on exit, whether you expected it to be committed or not. Set EXITCOMMIT OFF to rollback work on exit.

    Below the table shows the exit action associated with combinations of SET commands (AUTOCOMMIT & EXITCOMMIT) and the EXIT command.


    AUTOCOMMIT
    EXITCOMMIT
    EXIT
    Exit Behaviour
    ON
    ON
    -
    COMMIT
    ON
    OFF
    -
    COMMIT
    OFF
    ON
    -
    COMMIT
    OFF
    OFF
    -
    ROLLBACK
    ON
    ON
    COMMIT
    COMMIT
    ON
    ON
    ROLLBACK
    COMMIT
    ON
    OFF
    COMMIT
    COMMIT
    ON
    OFF
    ROLLBACK
    COMMIT
    OFF
    ON
    COMMIT
    COMMIT
    OFF
    ON
    ROLLBACK
    ROLLBACK
    OFF
    OFF
    COMMIT
    COMMIT
    OFF
    OFF
    ROLLBACK
    ROLLBACK

    Summary

    A developer can rollback and commit in read-only transactions which are pretty much mandatory, in fact, if you never do them, your view of the database would be FROZEN forever. When you issue "set transaction read only", it is as if you "stopped time". All of your queries will be as of the point in time you began that transaction (by a set transaction statement). Until you commit or rollback -- your view of the data in the database is frozen. It is much like flashback query which uses the same exact mechanism of read consistency. So, in nutshell, the transaction wanted to stop seeing data as of the point in time of the initial set transaction, it wanted to complete its transaction. 

    SQLPLUS by default does COMMIT on exit which means work will be saved.


    Did it helped you or you also came across the same experience, please share. If any question, please let me know. Happy to answers.


References

Oracle support online documentation.


Comments

  1. Satyam13 May 2019 at 08:39

    Thank you Kanhaiya for such detailed explanations about behavior of COMMIT/ROLLBACK on sqlplus.

    ReplyDelete
  2. Munish Jindal17 June 2019 at 13:16

    Excellent thought on basic which most the techie ignore. Thank you. keep on sending.

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle OS Watcher - Start and generate html report

Hi Guys, OS Watcher is an excellent utility for system performance investigation. OSWatcher invokes these distinct operating system utilities, each as a distinct background process, as data collectors. These utilities will be supported, or their equivalents, as available for each supported target platform. ps top ifconfig mpstat iostat netstat traceroute vmstat meminfo (Linux Only) slabinfo (Linux Only) OSWatcher is java program and requires as a minimum java version 1.4.2 or higher. This can run on on any Unix/Windows. An X Windows environment is required because oswbba uses Oracle Chart builder which requires it. Make sure java path is set $which java $<java path>/java Following will take system snapshot in every 30 seconds and will log last 24 hours data to OS Watcher archive log files. $cd <OS watcher dir> $nohup ./startOSWbb.sh 30 24 gzip <OS Watcher file location> & Without compress $nohup ./s...
9 comments
Read more

Alter System Checkpoint

Hello Techies, This happens most of the time that you need to add/drop redo log groups in Oracle databases due to any administrative reasons, however, if candidate redo group is in ACTIVE status, then you can't drop it and DBA need to wait until this comes in INACTIVE status to become this group available from drop. Here is the trick. Current status of redo log groups in my test database and you can see highlighted groups in ACTIVE status. In this situation, if DBA need to modify/drop these groups he/she need to wait until this comes in INACTIVE status. SQL> select * from v$log;     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------...
12 comments
Read more

ORA-15180: could not open dynamic library odm library, error [open]

Hello Friends, Faced " ORA-15180: could not open dynamic library odm library, error [open] " which is linked with libodm11.so and libodmd11.so. Target system was refreshed from a source system which was enabled to use Oracle Disk Manager (ODM), hence got source system configuration on target and while performing database PITR, this did not came easily at nomount stage. Have a look below. Following was the way, how I sorted it out. $> sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 6 16:42:50 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup nomount ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORA-15180: could not open dynamic library odm library, error [open] SQL> exit Disconnected Checked $ORACLE_HOME/lib/libod* condition. In your case, some of date related files may/may not be there. $> ls -lart $ORACLE_HOME/lib/l...
7 comments
Read more