Partitioning - Expert Oracle Database Architecture - page 606

So, the DROP we were prevented from performing before is now permitted, and it cascades to the child table
automatically. Further, if we ADD a partition, as follows, we can see that that operation is cascaded as well; there will be
a one-to-one parity between parent and child:
EODA@ORA12CR1> alter table orders add partition
2 part_2016 values less than
3 (to_date( '01-01-2017', 'dd-mm-yyyy' ));
Table altered.
EODA@ORA12CR1> select table_name, partition_name
2 from user_tab_partitions
3 where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
4 order by table_name, partition_name
5 /
TABLE_NAME PARTITION_NAME
-------------------- --------------------
ORDERS PART_2015
ORDERS PART_2016
ORDER_LINE_ITEMS PART_2015
ORDER_LINE_ITEMS PART_2016
A part of the preceding CREATE TABLE statement that we did not discuss is the ENABLE ROW MOVEMENT . This option
was added in Oracle8 i and we'll be discussing it fully in a section all its own. In short, the syntax allows an UPDATE
to take place such that the UPDATE modifies the partition key value and modifies it in such a way as to cause the row
to move from its current partition into some other partition. Prior to Oracle Database 8 i , that operation was not
permitted; you could update partition keys but not if they caused the row to belong to another partition.
Now, since we defined our parent table originally as permitting row movement, we were forced to define all of
our child tables (and their children and so on) as having that capability as well, for if the parent row moves and we are
using reference partitioning, we know the child row(s) must move as well. For example:
EODA@ORA12CR1> select '2015', count(*) from order_line_items partition(part_2015)
2 union all
3 select '2016', count(*) from order_line_items partition(part_2016);
'201 COUNT(*)
---- ----------
2015 1
2016 0
We can see that right now our data in the child table ORDER_LINE_ITEMS is in the 2015 partition. By performing a
simple update against the parent ORDERS table, as follows, we can see our data moved—in the child table:
EODA@ORA12CR1> update orders set order_date = add_months(order_date,12);
1 row updated.
EODA@ORA12CR1> select '2015', count(*) from order_line_items partition(part_2015)
2 union all
3 select '2016', count(*) from order_line_items partition(part_2016);