Interval Partitioning New Feature and Logging Option Problem

One of my old friends who is an Oracle DBA for at least 15 years always argued with me that “every new feature of Oracle is an evil!” :) He advised that it is not secure to upgrade a production environment to 10gR2 for example unless 11gR2 is released on your platform. Personally I always loved new features and tried to test them as soon as possible. I accept and also experience that there are always bugs around new features especially when they are complemented with some other feature but this is why we have Oracle support.

Recently on our 11.1 ODS production database we saw that the automatic created partitions do not inherit the LOGGING property of neither the table nor the tablespace it belongs to where as it is created always with LOGGING on. If you are trying to benefit from direct path inserts and your database is running on ARCHIVELOG mode and the target segments are LOGGING you will be failing to benefit and producing lots of redo and undo information depending on the size of data you are inserting. So an SR opened, bug escalated, a typical process.

Until 11.1 we had our own interval partitioning maintenance scripts and still we stick to them especially because the automatically created partitions’ naming. This is something I think will be evolving with the future releases since anybody who uses partitioning feature love to name their partitions and is not happy with anonymous partition names. But there is no evil with this and it is the life cycle of any software I think; a new feature comes out – some people test it/consume it and find bugs or report additional feature requests – the producer handles these within future releases or patches.

ps: for exchange of anonymous named partitions you may use EXCHANGE PARTITION FOR clause as a workaround but still with several limitations, you may check this post for more details.