Oracle Partitioning enhances the manageability, performance, and availability of database applications. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.
Oracle provides a comprehensive range of partitioning schemes to address every database schema. Moreover, since it is entirely transparent in SQL statements, partitioning can be used with any application, from packaged OLTP applications to Data Warehouses.
What happens when you need to add a new partition to an existing table ?
Use the following code:
alter table YOURTABLE add partition PARTITION_NAME values less than (MAX_PARTITIONVALUE);
For example, let say that you have a table with partitions on date’s year. Each partition is named B_Y2001, B_Y2002, … and for each, the value must be less than the following year. In our example, B_Y2001 would have values less than 2002…and so on…
Beware that if you have defined a partition with a MAXVALUE you will have trouble in adding the new partition.
Eventually, you will have to drop the partition with the MAXVALUE (thus erasing the data contained in that partition…so check first)
ALTER TABLE YOURTABLE DROP PARTITION PARTITION_WITH_MAXVALUE
Otherwise you can also split the partition with the max value by including the new partition within the range of values.
It goes like this :
ALTER TABLE YOURTABLE SPLIT PARTITION PARTITION_WITH_MAXVALUE AT (THE_VALUE_WHERE_TO_SPLIT) INTO ( PARTITION NEW_PARTITION, PARTITION PARTITION_WITH_MAXVALUE );
For more info on this, go to Oracle’s Website here