1 2 3 4 Previous Next 47 Replies Latest reply on Aug 25, 2015 5:38 PM by Dude!

    Does Truncate Table generate Undo?

    Dude!

      From what I understand, TRUNCATE TABLE resets the High Water Mark and according to some discussions and information on the web creates a small amount of undo. For example, http://www.orafaq.com. However, the Oracle 11gR2 documentation at Truncating Tables and Clusters clearly outlines that it does not generate any undo.

       

      Quote: A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back.

       

      TRUNCATE does apparently do a COMMIT before and after it executes, which would explain why there is no ROLLBACK. And if I'm not mistaken, any DML or DDL statement needs to create an entry in the redo log first, before being executed.

       

      What is correct? Does TRUNCATE really create no UNDO and the implicit COMMIT is perhaps just only a safeguard, or what is the real story here? I tried a couple of tests, playing with the employee_demo table, but I have not been able so far to find out what changed in the UNDO tablespace. Unfortunately I'm not a big expert on PL/SQL and don't use it very much. Is there a simply way to query or compare the undo table before and after a truncate table operation to see the difference? Comparing just the size does not.

       

      Thanks!

       

      Summary/Conclusion:

       

      The answer, put simply, is that TABLE TRUNCATE produces Undo. This Undo information cannot be used by the user to ROLLBACK a TABLE TRUNCATE, which also applies to other DDL statements, but can be required by the instance to perform database crash recovery.

       

      Since TABLE TRUNCATE modifies metadata in the Data Dictionary and resets the High Water Mark of the object, it produces much less Undo than DML statements like DELETE. However, it would probably not be Oracle database if everything was quite that simple.

       

      If you happen to find this question interesting and wish to learn more about the redo and undo process in regard to DDL statements, some of the responses I received where very resourceful and provided information that you many not know or won't find in the usual documentation.

       

      I can only mark one answer as correct and simply took the first response that provided an answer to my initial question. The responses I have marked as helpful are those responses that I think provided good explanations or added very interesting new aspects or information to the topic.

       

      Thanks to everyone for your time and contributions!

        • 1. Re: Does Truncate Table generate Undo?

          TRUNCATE does NOT generate UNDO.

          • 2. Re: Does Truncate Table generate Undo?
            Jarkko Turpeinen

            sol.beach kirjoitti:

             

            TRUNCATE does NOT generate UNDO.

            only for educational reasons, how that can be verified is my interest currently

             

            tried to trace events 100054 and 10055 but i cannot recognize undo activities (don't know what to look for).

             

            I found out that after truncate there is happening updates and deletes

             

            update sys.mon_mods$...

            delete from superobj$...

            delete from tab_stats$...

            update tab$...

            ...

             

            and at the end

             

            WAIT #127983856: nam='log file sync'

            WAIT #127983856: nam='SQL*Net message to client'

             

            that means commit and done.

             

            edit: i'm sure undo is not generated from that truncated table. I think question is, does truncate generate any undo from anything. I believe it does, but at the moment i cannot prove that to myself.

            • 3. Re: Does Truncate Table generate Undo?
              From what I understand, TRUNCATE TABLE resets the High Water Mark and according to some discussions and information on the web creates a small amount of undo. For example, http://www.orafaq.com.

              There is NOTHING in that article that says TRUNCATE generates ANY undo at all.

              • 4. Re: Re: Does Truncate Table generate Undo?
                Jonathan Lewis

                To check generation of undo (example):

                 

                SQL> select count(*) from t1;

                 

                  COUNT(*)

                ----------

                  1000000

                 

                1 row selected.

                 

                SQL> select name, value from V$mystat ms, v$statname sn where sn.statistic# = ms.statistic# and sn.name like '%undo%' and ms.value != 0;

                 

                NAME                                                                  VALUE

                ---------------------------------------------------------------- ----------

                undo change vector size                                                192

                 

                1 row selected.

                 

                SQL> truncate table t1;

                 

                Table truncated.

                 

                SQL> select name, value from V$mystat ms, v$statname sn where sn.statistic# = ms.statistic# and sn.name like '%undo%' and ms.value != 0;

                 

                NAME                                                                  VALUE

                ---------------------------------------------------------------- ----------

                undo change vector size                                              31272

                 

                1 row selected.

                 

                 

                The undo that would have been generated by a delete would have been in the order of a hundred and twenty megabytes - but you'll have to take my word for that.

                This tiny amount of undo would have been related to a few space management blocks being cleared, the segment header being updated, and possibly some updates to the data dictionary (your tab_stats$ changes).

                 

                To prove that some of the undo related to the space management blocks for the table (and its indexes) you'd probably have to switch log files just before the truncate, then truncate, then dump the current log file to get a minimal log file dump that you could review to see which blocks had been affected by redo and which bits of the redo had matching (or, rather, reversing) entries created in the undo. You'd then be able to see exactly what had happened to the table (and index) blocks, and how much of that action had been protected by undo.

                 

                Bottom line - people often say that "truncate" generates no undo, what they really mean is that it generates no undo for the data although it still generates relatively tiny amount of undo for the meta-data.

                 

                Regards

                Jonathan Lewis

                 

                 

                P.S.  Before anyone points out that there are always special cases - truncate of a table in a cluster is implemented as a delete.

                • 5. Re: Does Truncate Table generate Undo?
                  Dude!

                  Here is the quote from the link in case you missed it:  TRUCATE is faster and doesn't use as much undo space as a DELETE.

                   

                  But maybe that's a type and should read "redo". However there are other examples:

                   

                  http://stackoverflow.com/questions/2701879/drop-truncate-and-rollback-segment

                  https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:2860243091362

                  • 6. Re: Does Truncate Table generate Undo?
                    Dude!

                    Thanks for the response. Looks like there is no real easy way to verify what exactly happens. But it seems very clear that table truncate creates no undo that could be used for a user managed rollback.

                     

                    AskTom mentions that "if the machine crashed in the middle of doing DDL, we need to be able to undo that operation when we recover."

                     

                    Well, from what I understand, the recovery process uses redo logs to roll the database forward, which in turn also rebuilds undo. At the end of the recovery process, uncommitted transactions are rolled back, probably cleaning some information and updating some data headers. Since DDL's create an entry in the redo before being executed and have an implicit commit, why would there be a need for rollback, and instead not simply be a replay of the DDL? If a crash happens in the middle of table truncate, the table is either there or empty depending on what can be recovered from the redo log anyway.

                    • 7. Re: Does Truncate Table generate Undo?

                      Here is the quote from the link in case you missed it:  TRUCATE is faster and doesn't use as much undo space as a DELETE.

                      No - I didn't miss it. Zero is less than 'something' isn't it?

                      • 8. Re: Does Truncate Table generate Undo?
                        Stew Ashton

                        Dude! wrote:

                         

                        ...Since DDL's create an entry in the redo before being executed and have an implicit commit, why would there be a need for rollback, and instead not simply be a replay of the DDL?...

                        DDLs have two implicit commits, one before and one after. There would be a need to roll back to the point of the first commit.

                         

                        No command is "replayed" during recovery. The caller who said "TRUNCATE" isn't connected anymore, so how could Oracle notify him that the command finally succeeded?

                         

                        The need is to roll back to the point of the first commit, which simply means undoing the small changes Jonathan listed. That requires UNDO, not REDO - except for any REDO needed to recreate the UNDO.

                        • 9. Re: Does Truncate Table generate Undo?
                          Dude!

                          As far as I know, during recovery, everything that has been committed is recovered using redo. If recovery finds a SCN in a datafile for which there is no corresponding record in the redo log, the information is cleaned or rolled back using the appropriate undo data.

                           

                          According to http://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm

                          When a transaction is committed, the following occurs:

                          1. The internal transaction table for the associated undo tablespace records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.
                          2. The log writer process (LGWR) writes redo log entries in the SGA's redo log buffers to the redo log file. It also writes the transaction's SCN to the redo log file. This atomic event constitutes the commit of the transaction

                           

                          Hovever, DDL statements have an implicit commit and require an entry in the redo log before being executed. As far as I understand, that's a fundamental difference between DML and DDL. 

                           

                          So when there is recovery, the information from the redo log is recovered. I cannot see why there should be any undo of a DDL necessary. How will the recovery process recognize that a undo of a DDL was necessary?

                          • 10. Re: Does Truncate Table generate Undo?

                            Do changes to REDO get written into UNDO?

                            • 11. Re: Re: Does Truncate Table generate Undo?
                              Solomon Yakobson

                              Dude! wrote:

                              Here is the quote from the link in case you missed it:  TRUCATE is faster and doesn't use as much undo space as a DELETE.


                              The above is true in general, but as Jonathan pointed out "there are always special cases" and here "special" case doesn't need to be as exotic as cluster table. For example, deleting empty table uses zero undo while truncating empty table still uses small amount of undo


                              SQL> create table tbl as select 1 n from dual;

                              Table created.

                              SQL> truncate table tbl;

                              Table truncated.

                              SQL> connect scott/tiger
                              Connected.
                              SQL> select  name,
                                2          value
                                3    from  V$mystat ms,
                                4          v$statname sn
                                5    where sn.statistic# = ms.statistic#
                                6      and sn.name like '%undo%'
                                7      and ms.value != 0
                                8  /

                              NAME                                                                  VALUE
                              ---------------------------------------------------------------- ----------
                              undo change vector size                                                108

                              SQL> truncate table tbl;

                              Table truncated.

                              SQL> select  name,
                                2          value
                                3    from  V$mystat ms,
                                4          v$statname sn
                                5    where sn.statistic# = ms.statistic#
                                6      and sn.name like '%undo%'
                                7      and ms.value != 0
                                8  /

                              NAME                                                                  VALUE
                              ---------------------------------------------------------------- ----------
                              undo change vector size                                                708

                              SQL> connect scott/tiger
                              Connected.
                              SQL> select  name,
                                2          value
                                3    from  V$mystat ms,
                                4          v$statname sn
                                5    where sn.statistic# = ms.statistic#
                                6      and sn.name like '%undo%'
                                7      and ms.value != 0
                                8  /

                              NAME                                                                  VALUE
                              ---------------------------------------------------------------- ----------
                              undo change vector size                                                132

                              SQL> delete tbl;

                              0 rows deleted.

                              SQL> select  name,
                                2          value
                                3    from  V$mystat ms,
                                4          v$statname sn
                                5    where sn.statistic# = ms.statistic#
                                6      and sn.name like '%undo%'
                                7      and ms.value != 0
                                8  /

                              NAME                                                                  VALUE
                              ---------------------------------------------------------------- ----------
                              undo change vector size                                                132

                              SQL>


                              Now notice how I created table tbl? I forced segment creation. If I simply create table without using CTAS and segment creation is deferred (which is the default) neither DELETE nor truncate will use any undo:


                              SQL> drop table tbl purge;

                              Table dropped.

                              SQL> create table tbl(n number);

                              Table created.

                              SQL> connect scott/tiger
                              Connected.
                              SQL> select  name,
                                2          value
                                3    from  V$mystat ms,
                                4          v$statname sn
                                5    where sn.statistic# = ms.statistic#
                                6      and sn.name like '%undo%'
                                7      and ms.value != 0
                                8  /

                              NAME                                                                  VALUE
                              ---------------------------------------------------------------- ----------
                              undo change vector size                                                132

                              SQL> truncate table tbl;

                              Table truncated.

                              SQL> select  name,
                                2          value
                                3    from  V$mystat ms,
                                4          v$statname sn
                                5    where sn.statistic# = ms.statistic#
                                6      and sn.name like '%undo%'
                                7      and ms.value != 0
                                8  /

                              NAME                                                                  VALUE
                              ---------------------------------------------------------------- ----------
                              undo change vector size                                                132

                              SQL>


                              SY.

                              1 person found this helpful
                              • 12. Re: Does Truncate Table generate Undo?
                                Dude!

                                Not as far as I know, but changes to undo get written to redo. There is no rollback or undo after data has been committed.

                                 

                                I'm not entirely sure, but I think that data blocks or their corresponding vector or metadata is written to undo and redo before and after data blocks have changed. Whether this also applies to DDL statements was part of my initial question. Once a DML transaction has been committed, the information of the undo before image is no longer requried and can be overritten by the next transaction. And I guess this is how redo can be used to generate undo information.


                                • 13. Re: Does Truncate Table generate Undo?
                                  Dude!

                                  Maybe understanding what an Oracle Change Vector is could help me to better understand the concept . Isn't that also what is stored in the redo? If that is just metadata and not the actual data block or physical content, that would actually explain a lot things.

                                  • 14. Re: Does Truncate Table generate Undo?
                                    Solomon Yakobson

                                    And what your question has to do with REDO? Anyway, for simplicity, look at data dictionary tables as is was a regular table. UPDATE/INSERT/DELETE against data dictionary table needs UNDO same as UPDATE/INSERT/DELETE against user table.

                                     

                                    SY.

                                    1 2 3 4 Previous Next