Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?

Notes in line:

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

• Original Message ----- From: "Saibabu Devabhaktuni" <saibabu_d_at_yahoo.com> To: "free" <oracle-l_at_freelists.org> Sent: Wednesday, July 06, 2011 6:04 PM Subject: Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?

> Here are the reasons I think why object level checkpoint is needed for
> Truncate
> or Drop operation:
>
> 1) Datafiles on primary and standby need to be same bit by bit and block by
> block. This is fundamentally required for Oracle physical standby and
> physical
> backups. This will be broken if object level checkpoint wasn't done.
>

But blocks don't get transferred, the redo does, and the redo will ensure that physical standby gets corrected.
If you're thinking about creating a new physical standby - the truncated or dropped blocks are just garbage, apart from the segment header etc. (see reply to Tim Gorman) which would have been handled by local writes.

> 2) Database level flashback can logically corrupt the data, when flashback
> database command is used, if thread level checkpoint wasn't done as part of
> Truncate or Drop operation.
>

There may be a problem - but as a reason that doesn't really work: "I think we'll write truncated objects to disc because in 10 years time we're going to come up with database flashback and then things will break if we don't." ;)

Again, though, flashback simply replaces blocks in the datafiles, then applies redo log to them, but the available redo would be appropriate for the block that had not been written to disc on truncate.

> Marking buffers as invalid for writes is only done in Active DataGuard
> environment on physical standby databases in some situations.
>
> Hope it helps.
>
> Thanks,
> Sai
> http://sai-oracle.blogspot.com
>
> Version: 10.0.1388 / Virus Database: 1516/3746 - Release Date: 07/05/11

--
http://www.freelists.org/webpage/oracle-l