What is the purpose of a segment level checkpoint before DROP/TRUNCATE of a table?
- You have a large buffer cache and you drop table A without checkpointing the dirty buffers.
- Immediately after the drop succeeds (some buffers are still dirty in cache) some other segment (table B) reuses that space for itself and writes stuff into it.
- A few seconds later, DBWR wakes up to find & write some dirty buffers to disk (anything it finds from its lists). As there are some old & dirty blocks of table A still in the cache, they get written to disk too, overwriting some of the new table B blocks!