Zabbix on Oracle - few notes

We migrated Zabbix from MySQL to Oracle.
At the same time we moved to partitioning, here excellent article how to do it on MySQL. On Oracle we partitioned the same tables, created the same indexes, but go with interval partitioning (it is nice to not have to create new partitions manually or program it) based upon weeks (the partitioning key is of NUMBER type and we need an equal interval value for every period, so month is out due to variable number of days).

Almost no problems with upgrade to 2.0.2. We had to change the database however due to national character set - must be UTF-8, while we had previously fixed UTF16 - zabbix on Oracle is based mainly upon NVARCHAR2 type, and upgrade sets some columns to width bigger than 2000.
It is worth to enable CURSOR_SHARING to FORCE (actually I would say this is inevitable).
At the same time I would not recommend to use any automatic memory management (even ASMM). May be this is only our case, but frequently we run into ORA-4031 problems, which eventually ended with instance hangover or crash. As soon as I disabled any automatic memory management completely, the problems gone.

In addition to partition maintenance here there is an action for job, which will drop the HISTORY% tables' old partitions:

 begin
  for cmd in (
    with 
    conf as (select 9+1 part_num_left from dual)
    select 'alter table '||p.table_name||' drop partition '||p.partition_name||' update global indexes'  ddl1 
    from 
      user_tab_partitions p, 
      (select tp.table_name, count(*) l_pnum 
        from user_tab_partitions tp 
        where tp.table_name like 'HISTORY%' 
        group by tp.table_name 
        having count(*)>(select part_num_left from conf)) t
    where 1=1
      and p.table_name=t.table_name 
      and p.table_name like 'HISTORY%' 
      and p.partition_name<>'DONT_DELETE' 
      and p.partition_position between 2 and (t.l_pnum-(select part_num_left from conf)) 
    order by p.table_name, p.partition_position)
  loop
    execute immediate cmd.ddl1;
  end loop;
end;

The partitions labeled DONT_DELETE are partitions on the first position of relevant tables. The label is due to the fact one can not drop the first partition from a table partitioned by interval. It is comfortable to create those partitions before the time we want to store in table in order to keep it empty. The code above drops partitions leaving last n ones (here 9 + DONT_DELETE). The line with condition on DONT_DELETE partition name is actually redundant as the code starts dropping from second partition.