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

  • From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
  • To: free <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 10 Jul 2011 23:46:07 -0700 (PDT)

Here is a test case to simulate the behavior of Oracle not doing object level 
checkpoint during truncate table operation (and hence not guaranteeing dirty 
buffers write to disk) and Oracle reading stale data (logical corruption).

Run t1.sql from one session, and run t2.sql from another session while first 
session is waiting on dbms_lock.sleep (Test was done on with 8K block 

create tablespace tr1 datafile '?/dbs/tr1.dbf' size 10M
extent management local
segment space management auto;

create table tr1(a number, b char(1000)) tablespace tr1;
create table tr2(a number) tablespace sysaux;

insert into tr1 select rownum, 'A' from dual connect by level <= 1000;


alter tablespace tr1 begin backup;

host cp $ORACLE_HOME/dbs/tr1.dbf $ORACLE_HOME/dbs/

alter tablespace tr1 end backup;

update tr1 set a=0;


REM Run t2.sql from another session 

exec dbms_lock.sleep(15); 

truncate table tr1;

alter system flush buffer_cache;

host cp $ORACLE_HOME/dbs/ $ORACLE_HOME/dbs/tr1.dbf

exec dbms_lock.sleep(60);

select count(*), count(distinct a) from tr2;

alter session set db_file_multiblock_read_count=2;

j number := 1;
for i in (select a from tr1) loop
insert into tr2 values (i.a);
if (j <= 60) then
end if;
j := j+1;
end loop;

Even though TR1 table was updated to set all values of column "A" to "0", 
Oracle reported some records from latest update operation and the rest from 
before update operation. I think this can be classified as logical corruption, 
although re-running the same query will fix the problem.

This is by far the biggest reason I think Oracle should do object level 
checkpoint as part of truncate.

Another reason which I'm still very convinced is that if Oracle generated redo 
for a change that is supposed to be persistent across database bounces, then 
the data block buffers which had this redo applied in the buffer cache should 
be written to disk regardless of any state change happening to the underlying 
object. This not only keeps code base simple but leave the door open for new 
features (think of anybody predicting flashback database feature in 8i days) 
which may very well rely on this fact.

I'll continue to test flashback feature if it can be rigged for data loss 
scenario when object level checkpoint is missing during truncate operation.

Other related posts: