I have a partitioned table with a BLOB column. When multiple rows per transaction is deleted from this table by multiple users, we often run into ORA-00060 deadlock-s, which, according the trc files, are caused by ITL deadlocks in the LOB index partitions. So I want to recreate the table with higher LOB index INITRANS than the default 2. I can do that for a non-partitioned table with create table ... ( ... lob (...) store as ... ( ... index ... (... initrans 10 ... ) ) )
, but for the partitioned table Oracle says
"ORA-22879: cannot use the LOB INDEX clause for partitioned tables". Is there any other way I can set INITRANS for a partitioned LOB index?
(Update: Note that I don't need to ALTER the existing table, I can recreate it from scratch. That's usually easier.)
jonearles's edit follows:
Here's an example of how the normal method does not work:
create table test1(a number, b clob)
partition by range (a) (partition p1 values less than (1));
select * from dba_indexes where owner = user and table_name = 'TEST1';
--ORA-22864: cannot ALTER or DROP LOB indexes
alter index SYS_IL0000111806C00002$$ initrans 3;
It's interesting to note that the index DDL looks really odd. The code below would not run - there's no expression in the index and the parenthesis is not even closed.
select dbms_metadata.get_ddl('INDEX', 'SYS_IL0000111806C00002$$') from dual;
CREATE UNIQUE INDEX "JHELLER"."SYS_IL0000111806C00002$$" ON "JHELLER"."TEST1" (
PCTFREE 10 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "SYS_IL_P19289"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE( INITIAL 65536 NEXT 1048576 MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
PARTITION "SYS_IL_P19290"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE( INITIAL 65536 NEXT 1048576 MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" )
PARALLEL (DEGREE 0 INSTANCES 0)
Oracle technical support said that I can't specify the INITRANS for a partitioned LOB index. However, they have also noticed that using SECUREFILE
option together with DEDUPLICATE LOB
option (which we did) might be the cause the problem. So I have given up the DEDUPLICATE LOB
(just to be sure, replaced it with KEEP_DUPLICATES
) and now the LOB index deadlocks are gone! (I have written a little stress test, and with that you can cause dozens of dead lock errors in minutes with DEDUPLICATE LOB
, but none with KEEP_DUPLICATES
.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With