4 Replies Latest reply: Sep 28, 2013 2:22 AM by Martin Preiss RSS

    uncommitted trans. will writes to datafile or not

    Saran.M

      At what scenario uncommitted transaction queries writes in data file?

       

      I searched for the answer. But i got some answer. I must know the answer is correct or not.

      When the database is running in no archivelog mode ,then the uncommitted transaction queries writes into datafile for instance recovery at next start up.

        • 1. Re: uncommitted trans. will writes to datafile or not
          jgarry

          Oracle is optimistic, with lazy writes.  So it is entirely possible to have a transaction write millions of rows, the database writer write those to data files, then the transaction ends with a rollback.

           

          What matters most is the redo stream - a commit gets written there, the data becomes "real."

           

          This one is pretty good: The Arup Nanda Blog: 100 Things You Probably Didn't Know About Oracle Database

          • 2. Re: uncommitted trans. will writes to datafile or not
            sb92075

            Saran.M wrote:

             

            At what scenario uncommitted transaction queries writes in data file?

             

            I searched for the answer. But i got some answer. I must know the answer is correct or not.

            When the database is running in no archivelog mode ,then the uncommitted transaction queries writes into datafile for instance recovery at next start up.

             

            a single process issues a LARGE, Large, large DML such that the amount of data exceeds  sum total of all REDO log files, all RAM, and total of all TEMP tablespace,

            so where does all this changed data go (prior to any COMMIT being issued)?

            • 3. Re: uncommitted trans. will writes to datafile or not
              Nikolay Savvinov


              Hi,

               

              Oracle doesn't wait for the data to get committed to write it to data files (that's why rollbacks in Oracle are generally much more expensive than commits).

               

              Best regards,

                Nikolay

              • 4. Re: uncommitted trans. will writes to datafile or not
                Martin Preiss

                what has been already said about Oracle's defering of writing to the data files is also true for noarchivelog mode: Oracle only makes sure that the transaction protocol (i.e. the redo logs) contains the information on all committed changes and that these information are persisted on disk. The only difference between archivelog and noarchivelog mode is that in noarchivelog mode the system does not care what you do with the logs that have been used in the past and only makes sure that there are the logs that are neccessary for an instance recovery. In archivelog mode all the historic logs are archived and make it (in theory) possible to recover to any point in time since the creation of the database (of course only if you keep all these logs).