Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: DDL/DML and rollback

Re: DDL/DML and rollback

From: Jonathan Lewis <>
Date: Sun, 6 Feb 2005 11:03:39 +0000 (UTC)
Message-ID: <cu4tia$o19$>

Note in-line


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated Jan 21st 2005

"vertigo" <> wrote in message 

> ok, if the system can rollback drop table statement after crash (using
> dictionary information written in UNDO) why the user can not rollback drop
> table statement ?
The drop statement is largely a set of update statements to the data dictionary, followed by a commit. The 'drop' has not quite happened until the data dictionary changes have been committed. So on crash recovery, Oracle isn't really 'rolling back the drop table'. If the commit occurred before the crash, the table is still dropped because the data dictionary updates will have been committed during recovery. If the commit had not occurred before the crash, the updates to the data dictionary will be replayed on the (ordinary crash-recovery) roll-forward, and then reversed out on the (ordinary crash-recovery) rollback, because on crash recovery, any transactions not committed are rolled back.
> When drop table or truncate table is executed the data from table is left
> untouched. When is it deleted ?
There are a couple of special cases, but generally it is deleted when the blocks are formatted as they are used by the next segment that acquires the space, and only as the high-water mark is moved upwards. (And the details about the HWM vary depending on whether you are using the newer ASSM mechanisms, or the older freelist mechanism).
> How does High Water Mark is adjusted when i use truncate table statement
> and delete from statement ?
The HWM is not adjusted by a delete statement. The HWM (which is stored in the segment header block) is reset to zero when you issue a truncate. I would guess that this takes place after all the other data dictionary updates (and tablespace bitmap updates if you are using LMTs) have taken place, but without doing a detailed check of the log file I couldn't guarantee that.
> Thanx
> Michal
Received on Sun Feb 06 2005 - 05:03:39 CST

Original text of this message