Re: DDL/DML and rollback

In article <cu4hj1$1eu$1_at_atlantis.news.tpi.pl>, vertigo says...
>
>Hello
>Does all DML commands generates rollback informations ?
>Does all DDL command do not generate rollback informations ?
>
>When i use DROP TABLE command is anything saved in undo segment ?
>If yes, what for ?
>
>Thanx
>Michal
>(Oracle 9.2i)
>

All statements generate some amount of undo information.

Take the DDL statement "drop table", drop table can be thought of as if it were a "stored procedure". It's job - to delete from obj$, col$, ind$, etc - dictionary tables - to wipe out the existence of a table.

The table data (of the table being dropped) is not stored in undo for this operation, it need not be (in order to "rollback" the drop table). Rather, the UNDO for the dictionary information that would provide access to this table is stored in UNDO. That way, if the system were to fail 1/2 way through the drop table (before it finished) -- upon restart of the system -- the table would still be there, still be intact (the undo would be applied to the dictionary and it would be as if the operation never took place)

DML generates undo for the object being modified (and the dictionary as well, as we add extents and such). Some DML, insert /*+ APPEND */ into t select * from another_t in particular, can skip UNDO on the table being modified (but not any indexes on it!) - but undo is still generated. This undo again in this case is undo against the oracle data dictionary. In the event the insert append were to fail, when the system rolled back - it would roll back the changes to the dictionary and the newly added data would just "be forgotten"

So, it is fairly safe to say that most all DDL (alter session being an exception for exmaple) generates some undo somewhere (for the dictionary changes being made). Truncate -- generates UNDO (for the dictionary changes). Alter table Add column -- generates UNDO for the dictionary. Delete from table -- generates undo to undo the delete. Insert -- generates undo to undo the insert. And so on.

What the UNDO is generated for is what changes from command to command.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation