Truncate leaves an object in place but immediately destroys the segment header blocks, extent map blocks, space management blocks and root blocks of indexes. It has to do this because the same physical space might be reused immediately for more data for the same object.
Drop turns the space into tablespace free space - so there is no question of anyone re-using it **for the same object**, so it doesn't overwrite any of the existing blocks in the object. That's why cross-DLL read consistency is possible on drop - from one user's perspective the object is still there, from another user's perspective there is no object at all just free space.
I still can't think of any reason why this should have any connection with recovery in general and PITR
Regards Jonathan Lewis http://jonathanlewis.wordpress.com----- Original Message ----- From: "Paresh Yadav" <yparesh@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx> Cc: "free" <oracle-l@xxxxxxxxxxxxx> Sent: Sunday, July 10, 2011 6:12 AMSubject: Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?
Everyone, Thanks for trying to answer my question. Jonathan, Your answer explains "why for drop". Why do you consider it as a side effect on truncate for piggy backing on same code? i.e. What is wrong with Truncate honoring Cross-DDL read-consistency? Can you connect this with anything regarding recovery and PITR? Thanks, Paresh