Re: DDL/DML and rollback

premmehrotra_at_hotmail.com wrote:
>
> 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

as does DB2...

then again, non-blocking read is pretty nice :-)

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------