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)