21 August, 2008

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.

No comments: