Testing Bug 4260477 Fix for Bug 4224840

Having posted about MetaLink Note#465226.1 on forums, earlier today, I have created a test case to simulate the same situation.
The base bug 4224840 has been logged by a customer that possibly found block corruption after running a transaction that locked more than 4,095 records in a block.
Considering that a table must have at least 1 column and that every data block loses some space to block header and PCTFREE, it is unlikely to have 4,095 rows in a 16KB data block. However, a 32KB data block can hold more than 4,095 rows.

Here is my Test Case Simulation on 10.2.0.1 64bit on Linux (you'd need 64bit for 32KB datablocks) :

[Note : I don't really create 4,095 records in the normal sense. I INSERT and DELETE that many times, but since I do not issue ROLLBACK or COMMIT, Oracle "preserves" each INSERT and DELETE ! ]

SQL>
SQL> REM Bug 4260477 is a fix for
SQL> REM     4224840 whereby Block Corruption (or error with db_block_checking) would occur with >4095 locks in a block
SQL> REM The fix in 4260477 is to return an error message (ORA-8007) to prevent the corruption
SQL>
SQL> REM This test simulates the issue
SQL>
SQL> drop tablespace TBS_32K including contents and datafiles;

Tablespace dropped.

SQL>
SQL> create tablespace TBS_32K datafile '/oracle_fs/Databases/ORT21FS/tbs_32K_01.dbf' size 10M autoextend on next 10M maxsize 2000M blocksize 32K
  2  extent management local autoallocate segment space management auto ;

Tablespace created.

SQL>
SQL> show parameter db_block_checking

NAME                                 TYPE        VALUE                                                                            
------------------------------------ ----------- ------------------------------                                                   
db_block_checking                    string      FALSE                                                                            
SQL>
SQL> drop table Test_Bug_4260477 ;
drop table Test_Bug_4260477
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table Test_Bug_4260477 (col_1 varchar2(5)) pctfree 1 tablespace tbs_32K;

Table created.

SQL>
SQL> variable i number;
SQL>
SQL> set serveroutput on size 100000
SQL> begin
  2  for i in 1 .. 5000
  3  loop
  4    insert into Test_Bug_4260477 values (to_char(i));
  5    delete from Test_Bug_4260477 ;
  6    if ((i-4080) > 0) then
  7     dbms_output.put_line('Row '  i );
  8    end if;
  9  end loop;
 10  end ;
 11  /
Row 4081                                                                                                                          
Row 4082                                                                                                                          
Row 4083                                                                                                                          
Row 4084                                                                                                                          
Row 4085                                                                                                                          
Row 4086                                                                                                                          
Row 4087                                                                                                                          
Row 4088                                                                                                                          
Row 4089                                                                                                                          
Row 4090                                                                                                                          
Row 4091                                                                                                                          
Row 4092                                                                                                                          
Row 4093                                                                                                                          
Row 4094                                                                                                                          
begin
*
ERROR at line 1:
ORA-08007: Further changes to this block by this transaction not allowed
ORA-06512: at line 5


SQL> 

The 4260477 "fix" is to return an error message (8007 : "Further changes ....") before block corruption occurs. Neat isn't it ?
Of course, that means that the transaction fails !
The final fix is in 11g.