rollback

[mary]

I thought rollback segments only record DML statements. Someone made a statement that rollback segment may contain index creation statements as well as DML statements. Please help me to understand it.
Thank you.

[jmodic]

Actually neither DML nor DDL (or DCL or ...) statements are recorded in rollback segments. What is recorded in RB are *changes to database blocks*, made by DML statements.

So RBs do not contain index creation statements (and they dont contain table creation statements either). But what they do contain related to indexes are changes made to index blocks that are triggered by changes in the underlying table rows.

So if you have a table with bunch of indexes and you perform a large transaction on on it (INSERT UPDATE, DELETE) you'll generated a lot more rollback entries as if there were no indexes on a table.

HTH,

[sreddy]

[url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c02block.htm#8401[/url]

Jmodic,
You mean here in documentation DML means just mere block changes and nothing else...Even this is TRUE with DDL ?

[Edited by sreddy on 03-04-2001 at 07:14 AM]

[jmodic]

sreddy,

I'm not sure I understand your question, please explain in more detail.

Anyway, DML statements change the contents of segments (tables, indexes, clusters) and the changes of those changed blocks are recorder in rollback entries. As a matter of fact, RB segments do store block changes, but block contents before the changes took place.

DDL statements (generaly) do not change user data blocks, they change only the contents of data dictionary (true, the contents of data dictionary is also recorded in database blocks, but those blocks are managed by the RDBMS and are treated differently as far as rollback is concerned). So for example, if you drop a table, oracle records this only in data dictionary and does not even touch the data blocks of this table, so there is nothing to put in RB. And hence you can't rollback the DROP TABLE DDL.

Sometimes DDL is not recorded in dictionary, as is the case with TRUNCATE TABLE. In this case Oracle has nothing to record into the dictionary, but to the table segment header (it resets the HWM). Again, "true data" blocks (header block is not "true data" block as it does not hold true table data) are not touched by this operation, so there is nothing to record in RB. And again, that's why you can't rollback TRUNCATE.

[sreddy]

I got you. Thanks.