Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: shutdown abort and database consistency

Re: shutdown abort and database consistency

From: Jonathan Lewis <>
Date: Wed, 10 Jul 2002 18:59:36 +0100
Message-ID: <>

Oracle simply locks the table, and starts inserting into blocks above the HWM,
adding extents as it goes.

If you commit, then the HWM is updated
in the segment header and the lock is
released. If you rollback, the HWM is not updated in the segment header, and the
lock is released.

Any extents that have been added stay added since they are recorded in the data dictionary (or at least the segment header and file bitmap for tables in locally managed tablespaces) but as they are above the HWM, Oracle treats them as unformatted, and formats them as it moves the HWM for future inserts.

Of course, if you have indexes on the table, they are updated (using a bulk optimisation method) in a fairly normal fashion, generating undo and redo as they go, so a rollback would actually cause the normal type of rollback activity (with its associated redo) on the index blocks.

Jonathan Lewis

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November

Sean McKeown wrote in message <>...

>OK, so I have a question that's bugged me for a while now. How does
>Oracle accomplish a rollback of an insert /*+ APPEND */ into a nologging
>table? (Actually, I guess it doesn't really matter if the table is
>nologging or not - I just care about the undo, not the redo). The
>transaction shouldn't generate undo for the blocks in the table in
>question, yet you can roll it back before a commit. If you do the
>insert and don't commit, then try to query against the table, you get
>the ORA-12838, confirming that no undo (read-consistent) image of the
>table is available. I've always assumed that since the new blocks are
>being written above the HWM, Oracle can be tricky about how it load data
>in there (and "unloads" it after a rollback), and your post gives me a
>hunch... Does Oracle:
>- create temp segment
>- load data into temp segment
>- grab a chunk of blocks above HWM
>- wait for user to issue a commit or rollback
>- if a commit, write temp segment to that chunk and eliminate temp
>- if a rollback, just eliminate the temp segment and return the chunk?
>That way, if the db crashes while waiting for the commmit/rollback, the
>table itself hasn't yet changed physically, so other than the temp
>segment there's nothing really to clean up during instance recovery?
>Obviously just guessing here...
Received on Wed Jul 10 2002 - 12:59:36 CDT

Original text of this message