Re: shutdown abort and database consistency

Jonathan Lewis wrote:
>
> The data dictionary entries to record its creation are logged,
> but the sequence of events is:
> create temporary segment
> load data into temporary segment
> create most table definitions entries
> attach temp segement to table definition
> eliminate temp segment definition.
>
>
> It doesn't change the nature of your argument, but if you
> had not been committing between inserts you would have
> got oracle error:
> ORA-12838: cannot read/modify an object after modifying it in parallel.
> ;)

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 - 10:05:10 CDT