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 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> 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> REM This test simulates the issue
SQL> drop tablespace TBS_32K including contents and datafiles;

Tablespace dropped.

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> show parameter db_block_checking

------------------------------------ ----------- ------------------------------
db_block_checking string FALSE
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> variable i number;
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
ERROR at line 1:
ORA-08007: Further changes to this block by this transaction not allowed
ORA-06512: at line 5


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: