Re: shutdown abort and database consistency

Thanks, makes sense. As usual I was making things harder than they need be.

Regards,
Sean

Jonathan Lewis wrote:
>
> 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
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK July / Sept
> Australia July / August
> Malaysia September
> USA (MI) November
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Sean McKeown wrote in message <3D2C4CCB.7D8E409F_at_BACKSIESearthlink.net>...
> >
> >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
> >segment
> >- 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...
> >
> >Thanks,
> >SeanM
Received on Wed Jul 10 2002 - 18:49:42 CDT