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.

Moving subpartitions

In Oracle database MOVE operations are done on the segment level actually. Thus while simple table has its own segment, the partitioned one has none all the segments are bound with partitions. In case of subpartitions the same rule apply. So we move:

ALTER TABLE simple_t MOVE TABLESPACE tblspace1;
ALTER TABLE partitioned_t MOVE PARTITION p1  TABLESPACE tblspace1;
ALTER TABLE subpartitioned_t MOVE SUBPARTITION s1 TABLESPACE tblspace1;

When we move all the segments with must however change the default tablespace value on the table level and possibly on the partition level if it has a different value.

ALTER TABLE partitioned_t MODIFY DEFAULT ATTRIBUTES TABLESPACE tblspace1;
ALTER TABLE subpartitioned_t MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE tblspace1;

ALTER TABLE t1 MODIFY DEFAULT ATTRIBUTES... is less known, but can be quite useful allowing for switching on and off Advanced Compression and some other segment parameters.

This applies at least to the version 11.2 (do not check other docs).

Partitioning by interval

Few notes:

• interval needs to be a constant - we had here a time recorded as UNIX timestamp, thus in the database it was a NUMBER column to store those values - in this case to use interval we may partition by days or weeks
  
• interval constant type needs to be compatible with partition key data type (ie. number with number, date with time interval) - which is somewhat obvious
  
• at least one partition needs to be created in a "static" way (with the definition of the table) - so a database will have a starting point for next partitions, despite that the last "statically" added partition can not be removed - such activity finish with error ORA-14758.
• in order to archive/delete older partitions one may:
  
  • truncate the first "static" partition (ALTER TABLE blabla TRUNCATE PARTITION dont_delete DROP STORAGE UPDATE INDEXES;)
  • drop chosen partitions despite of the first one (ALTER TABLE blabla TRUNCATE PARTITION sys_536 UPDATE INDEXES;)
  • it is easy to automate dropping old partitions by dropping after every period the partition with partition_position=2 in dba_tab_partitions (or possibly an equivalent for subpartitions)