Several days ago we’ve hit in our production application an the interesting new (to me at least) Oracle error “ORA-14765: Cannot create a partition while doing a create index”. It wouldn’t be strange if it wouldn’t happen during an CREATE INDEX ONLINE operation, so therefore it indicates that ONLINE index creation is not really an ONLINE operation (sic!).
Note1: you cannot test it in SYS schema, as the defered segment creation feature on partitioned table seems to be not working for SYS (or SYSTEM tablespace) as it is dedicated for Oracle dictionary purposes only. Seems to me that this is similiar story to the enhaced-checksums always enabled for SYSTEM tablespace (hidden paramter _db_always_check_system_ts which stands for “Always perform block check and checksum for System tablespace”) etc.
Note2: the key in order to get the error is to have parameter deferred_segment_creation set to TRUE, which is pretty recommended if you want to avoid space waste, starting with 11.2g, as it allocates at minimum 8MB initial extent per (sub)partition segment.
Note3: tested only on 220.127.116.11.6 and 18.104.22.168.8.
After a little playing here are the steps to reproduce it:
DBA1@test3> create table t1 ( id number primary key, ts TIMESTAMP, txt varchar2(64) ) partition by range (ts) interval (NUMTOYMINTERVAL(1, 'MONTH')) ( partition p1 values less than (TO_DATE('01.01.2000', 'DD.MM.YYYY')), partition p2000_02 values less than (TO_DATE('01.02.2000', 'DD.MM.YYYY')), partition p2000_03 values less than (TO_DATE('01.03.2000', 'DD.MM.YYYY')), partition p2000_04 values less than (TO_DATE('01.04.2000', 'DD.MM.YYYY')), partition p2000_05 values less than (TO_DATE('01.05.2000', 'DD.MM.YYYY')), partition p2000_06 values less than (TO_DATE('01.06.2000', 'DD.MM.YYYY')) ); Table created. DBA1@test3> select table_name, segment_created from dba_tables where table_name='T1'; TABLE_NAME SEG ------------------------------ --- T1 N/A DBA1@test3> select partition_name, segment_created from dba_tab_partitions where table_name='T1'; PARTITION_NAME SEGM ------------------------------ ---- P1 NO P2000_02 NO P2000_03 NO P2000_04 NO P2000_05 NO P2000_06 NO 6 rows selected. DBA1@test3>
Now add a very large single current partition that will take some time :
DBA1@test3> insert /*+ APPEND NOLOGGING */ into t1 (id, ts, txt) SELECT level, sysdate, 'blah' FROM dual CONNECT BY LEVEL <= 2500000; 2500000 rows created. DBA1@test3> commit; Commit complete. DBA1@test3>
Actually so far we have partition with segment_size approximatly of ~73MB in case of 8kB db blocks.
DBA1@test3> select p.partition_name, p.segment_created, s.bytes/1024 kb from dba_tab_partitions p left join dba_segments s on (p.partition_name=s.partition_name and p.table_name=s.segment_name) where p.table_name='T1' order by 2,1; PARTITION_NAME SEGM KB ------------------------------ ---- ---------- P1 NO P2000_02 NO P2000_03 NO P2000_04 NO P2000_05 NO P2000_06 NO SYS_P17066 YES 73728 7 rows selected.
Now we start building online local index in our 1st session using “create index t1_i1 on t1 (ts, txt) local online”, immedietly followed by an attempt to insert single row into one of P2000_xx partition (which are without inititalized backing them segment at this point):
DBA1@test3> insert into t1 (id, ts, txt) values (3500000, TO_DATE('02.04.2000', 'DD.MM.YYYY'), 'testtesttest'); insert into t1 (id, ts, txt) values (3500000, TO_DATE('02.04.2000', 'DD.MM.YYYY'), 'testtesttest') * ERROR at line 1: ORA-14765: Cannot create a partition while doing a create index DBA1@test3>
Summary: CREATE INDEX ONLINE is not fully compatibile with sessions performing DML transaction against (sub)partitions that doesn’t come with pre-allocated segments. There seems to be very likley possibility to run into ORA-14765 for Oracle transaction systems especially creating numerous automatically managed subpartitions based on some template (like Interval Range partitions that are subpartitioned by ID). Of course you may want to avoid defered segment creation (and sacrifise a lot of storage space) or live with the risks of hitting those errors during building indexes (hopefully application might just retry at later stage).