1 2 Previous Next 24 Replies Latest reply: Feb 11, 2013 2:38 AM by 930854 RSS

    DML operations

    930854
      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
          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
            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
              >

              >
              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
                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
                  Billy~Verreynne
                  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
                    >
                    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
                      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
                        So options are flawed according to you ;)
                        Buckle up Oracle ;)
                        • 9. Re: DML operations
                          Billy~Verreynne
                          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
                            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
                              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
                                Hi all,

                                It's a oracle buk or what else. Please explain me.
                                • 13. Re: DML operations
                                  Hemant K Chitale
                                  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
                                    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