This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Feb 11, 2013 12:38 AM by 930854 RSS

DML operations

930854 Newbie
Currently Being Moderated
HI all,

I have a doubt in DML operations.

Que: Which two statements are true regarding a transaction? (Choose two)

a) If a data manipulation statement fails in the middle of a transaction, only that statement is rolled back
b) If a data manipulation statement fails in the middle of a transaction, the entire transaction is rolled back
c) A transaction is committed when a user exits the session normally without issuing an explicit COMMIT command
d) A transaction is rolled back when a user exits the session normally without issuing an explicit COMMIT command

Please explain.
  • 1. Re: DML operations
    Rahul_India Journeyer
    Currently Being Moderated
    It is welll documented in Oracle docs.Read Statement-Level Rollback
    http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm#i1666

    http://docs.oracle.com/cd/B10501_01/server.920/a96524/c17trans.htm

    Edited by: Rahul India on Feb 8, 2013 12:14 PM
  • 2. Re: DML operations
    Karthick_Arp Guru
    Currently Being Moderated
    For 1 and 2

    Oracle maintains Transaction level Atomicity. So its all COMMIT or all ROLLBACK. You should spend some time in reading about [url http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm]Transactions in oracle.

    ...Updating...

    The Rollback of a specific statement in case of failover is at Statement level.

    Please read [url http://docs.oracle.com/cd/B28359_01/server.111/b28318/transact.htm] Transaction Management  For further insight.

    ................

    For 3 and 4

    The answer for this depends on the client tool that is being used. In SQL Developer it askes the user if you want to COMMIT or ROLLBACK. But if you take SQL Plus it issues COMMIT when you exit, its just the way it works.

    Here is an example
    SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 8 01:31:29 2013
     
    Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
     
     
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
     
    
    SQL> create table t(no integer);
     
    Table created.
     
    SQL> insert into t values (1);
     
    1 row created.
     
    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    ...connecting again
     
    SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 8 01:32:17 2013
     
    Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
     
     
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
     
    SQL> select * from t;
     
            NO
    ----------
             1
    The basics is that oracle never issue COMMIT or ROLLBACK by itself for DML statements, its truly decided by the client.

    Edited by: Karthick_Arp on Feb 7, 2013 10:45 PM
    ...added link for transaction management ...
  • 3. Re: DML operations
    Rahul_India Journeyer
    Currently Being Moderated
    >

    >
    The basics is that oracle never issue COMMIT or ROLLBACK by itself for DML statements, its truly decided by the client.
    So the options are flawed?
  • 4. Re: DML operations
    Karthick_Arp Guru
    Currently Being Moderated
    Rahul India wrote:
    >
    The basics is that oracle never issue COMMIT or ROLLBACK by itself for DML statements, its truly decided by the client.
    So the options are flawed?
    Which options are you referring to?
  • 5. Re: DML operations
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Rahul India wrote:

    So the options are flawed?
    Where is the Answer? Is it behind door #1? Or maybe door #2? And what about door #3?

    The correct answer is none of the doors.

    Why?

    Because a motorcycle does not have doors.
  • 6. Re: DML operations
    Rahul_India Journeyer
    Currently Being Moderated
    >
    So the options are flawed?
    Which options are you referring to?
    The OP gave 4 options for the question.
    Its a z-051 certification question
    http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=303&p_certName=SQ1Z0_051

    Edited by: Rahul India on Feb 8, 2013 12:22 PM
  • 7. Re: DML operations
    Karthick_Arp Guru
    Currently Being Moderated
    As far as i know the option 3 and 4 does not make sense to me. I guess Billy has offered a better answer already :)
  • 8. Re: DML operations
    Rahul_India Journeyer
    Currently Being Moderated
    So options are flawed according to you ;)
    Buckle up Oracle ;)
  • 9. Re: DML operations
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    The answer is, as it often is, it depends. As in the case of a motorcycle where doors are not applicable. :-)
  • 10. Re: DML operations
    Rahul_India Journeyer
    Currently Being Moderated
    Billy  Verreynne  wrote:
    Billy ,Karthick
    My question is that theabove mentioned question is not correct in the context of the options given?
    Or is it that the Oracle assumes that all the queries are being executed from SQL*PLUS environment?

    Edited by: Rahul India on Feb 8, 2013 1:01 PM
  • 11. Re: DML operations
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    It should have been two distinct questions. Not 1 question with a "choose two of four options" !

    The first question relates to statement level rollback -- which you have pointed out.

    The second question is relative -- it depends on the client used. In the context of the sample questions, you might assume that the client is SQL*Plus.


    Hemant K Chitale
  • 12. Re: DML operations
    930854 Newbie
    Currently Being Moderated
    Hi all,

    It's a oracle buk or what else. Please explain me.
  • 13. Re: DML operations
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    It's not a bug.
    The question could have been better framed to be clearer about the context.

    Hemant K Chitale
  • 14. Re: DML operations
    Rahul_India Journeyer
    Currently Being Moderated
    Hemant K Chitale wrote:
    It's not a bug.
    The question could have been better framed to be clearer about the context.

    Hemant K Chitale
    Hemant
    so question is wrong from the examination point of view.Yes or No?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points