Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Does Truncate Table generate Undo?

Dude!Aug 22 2015 — edited Aug 25 2015

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!

This post has been answered by Jonathan Lewis on Aug 22 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 22 2015
Added on Aug 22 2015
47 comments
29,938 views