Re: DDL/DML and rollback

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 6 Feb 2005 08:50:35 -0800
Message-ID: <117708635.00015c43.062@drn.newsguy.com>

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

Received on Sun Feb 06 2005 - 10:50:35 CST