Re: DDL/DML and rollback

Thomas Kyte wrote:
> In article <cu4r84$hcs$1_at_nemesis.news.tpi.pl>, vertigo says...
> >
> >
> >
> >> 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)
> >
> >ok, if the system can rollback drop table statement after crash
(using
> >dictionary information written in UNDO) why the user can not
rollback
> >drop table statement ?
>
> because DDL is processed as:
>
> begin
> commit; - any of your outstanding work;
> DO_THE_DDL;
> commit;
> exception
> when others
> then
> rollback;
> raise;
> end;
>
>
> updates to the dictionary must be short and sweet, in order to not
lockup the
> system itself.
>
> >
> >When drop table or truncate table is executed the data from table is

> >left untouched. When is it deleted ?
> >
>
> it isn't, it is just "unmapped".. just like when you erase a 4 gig
file, you
> don't wait for 4 gig to be overwritten, the OS just wipes out memory
of that
> space being allocated
>
> That is what the database is doing, the space is just marked as
"free", someone
> else can use it.
>
>
> >How does High Water Mark is adjusted when i use truncate table
statement
> >and delete from statement ?
> >
>
> the high water mark is an attribute of the segment, when you
truncate, we
> actually create a new segment, with a wiped out "header" - next time
you
> truncate, look at the OBJECT_ID and DATA_OBJECT_ID.... - object id
will stay the
> same, data object id will have changed....
>
>
>
> ops$tkyte_at_ORA10G> select object_id, data_object_id from
> 2 user_objects where object_name = 'T';
>
> OBJECT_ID DATA_OBJECT_ID
> ---------- --------------
> 75999 75999
>
> ops$tkyte_at_ORA10G> truncate table t;
>
> Table truncated.
>
> ops$tkyte_at_ORA10G> select object_id, data_object_id from
> 2 user_objects where object_name = 'T';
>
> OBJECT_ID DATA_OBJECT_ID
> ---------- --------------
> 75999 76901
>
>
>
>
>
> >Thanx
> >Michal
> >
> >
>
>
> --
> Thomas Kyte
> Oracle Public Sector
> http://asktom.oracle.com/
> opinions are my own and may not reflect those of Oracle Corporation

I have always been suprised why Oracle does not allow rollback of ddl commands using
rollback statement. I have recently been learning SQL Server and if I am not mistaken
it does let you rollback DDL's.

Prem Received on Sun Feb 06 2005 - 15:31:51 CST