SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(
2 3 PARTITION P1 VALUES LESS THAN (TO_DATE('2007-01-01', 'YYYY-MM-DD')),
4 PARTITION P2 VALUES LESS THAN (TO_DATE('2008-01-01', 'YYYY-MM-DD')),
5 PARTITION P3 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> insert into t_new values(1,TO_DATE('2006-11-01', 'YYYY-MM-DD'));
insert into t_new values(2,TO_DATE('2007-01-01', 'YYYY-MM-DD'));
insert into t_new values(3,TO_DATE('2007-12-01', 'YYYY-MM-DD'));
commit;
SQL> select * from t_new partition(p1);
ID TIME
---------- ------------
1 01-NOV-06
SQL> select * from t_new partition(p2);
ID TIME
---------- ------------
2 01-JAN-07
3 01-DEC-07
SQL> select * from t_new partition(p3);
no rows selected
SQL> update t_new set time=TO_DATE('2008-02-05', 'YYYY-MM-DD') where id=1;
update t_new set time=TO_DATE('2008-02-05', 'YYYY-MM-DD') where id=1
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
SQL> !oerr ora 14402
14402, 00000, "updating partition key column would cause a partition change"
// *Cause: An UPDATE statement attempted to change the value of a partition
// key column causing migration of the row to another partition
// *Action: Do not attempt to update a partition key column or make sure that
// the new partition key is within the range containing the old
// partition key.
SQL> alter table t_new enable row movement
Table altered.
SQL> update t_new set time=TO_DATE('2008-02-05', 'YYYY-MM-DD') where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t_new partition(p1);
no rows selected
SQL> select * from t_new partition(p2);
ID TIME
---------- ------------
2 01-JAN-07
3 01-DEC-07
SQL> select * from t_new partition(p3);
ID TIME
---------- ------------
1 05-FEB-08