Database Reference
In-Depth Information
EODA@ORA12CR1> insert into range_example
2 ( range_key_column, data )
3 values
4 ( to_date( '01-jan-2014 00:00:00',
5 'dd-mon-yyyy hh24:mi:ss' )-1/24/60/60,
6 'application data...' );
1 row created.
EODA@ORA12CR1> select * from range_example partition(part_1);
RANGE_KEY DATA
--------- --------------------
15-DEC-13 application data...
31-DEC-13 application data...
We take one of the rows and update the value in its
RANGE_KEY_COLUMN
such that it can remain in
PART_1
:
EODA@ORA12CR1> update range_example
2 set range_key_column = trunc(range_key_column)
3 where range_key_column =
4 to_date( '31-dec-2013 23:59:59',
5 'dd-mon-yyyy hh24:mi:ss' );
1 row updated.
As expected, this succeeds: the row remains in partition
PART_1
. Next, we update the
RANGE_KEY_COLUMN
to a
value that would cause it to belong in
PART_2
:
EODA@ORA12CR1> update range_example
2 set range_key_column = to_date('01-jan-2014','dd-mon-yyyy')
3 where range_key_column = to_date('31-dec-2013','dd-mon-yyyy');
update range_example
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
This immediately raises an error since we did not explicitly enable row movement. In Oracle8
i
and later releases,
we can enable row movement on this table to allow the row to move from partition to partition.
You should be aware of a subtle side effect of doing this, however; namely that the
ROWID
of a row will change as
the result of the update:
EODA@ORA12CR1> select rowid
2 from range_example
3 where range_key_column = to_date('31-dec-2013','dd-mon-yyyy');
ROWID
------------------
AAAtzXAAGAAAaO6AAB
EODA@ORA12CR1> alter table range_example enable row movement;
Table altered.
