Re: Partitioned tables

Alexander,

The answer is, upgrade to Oracle8i, and you can enable row migration between partitions. However, this is achieved by performing a logical delete and insert of the row.

The logic can be found in the Getting to know Oracle8i documentation under Chapter 2 (Oracle8i New Features) under the Partitioning Enhancements section. The URL is; http://technet.oracle.com/doc/server.815/a68020/ch2.htm#102292

Rgds
Andrew

Alexander G Bozhik wrote:

> Hi all,
>
> Is there any way to update a partition key column so as to move the row
> from some partition to another one?
> For example, I've got the problem:
>
> SQLWKS> CREATE TABLE Doc(
> 2> Document_ID NUMBER(10, 0) NOT NULL,
> 3> Document_No VARCHAR2(40),
> 4> Document_Date DATE,
> 5> CONSTRAINT RDB$PRIMARY1 PRIMARY KEY (Document_ID))
>
> 6> PARTITION BY RANGE (Document_Date)(
>
> 7> PARTITION DCMT_T_1_99
> 8> VALUES LESS THAN (TO_DATE('01-FEB-1999','DD-MON-YYYY'))
> 9> TABLESPACE DCMT_T_1_99 ,
>
> 10> PARTITION DCMT_T_2_99
> 11> VALUES LESS THAN (TO_DATE('01-MAR-1999','DD-MON-YYYY'))
> 12> TABLESPACE DCMT_T_2_99,
>
> 16> PARTITION DCMT_T_3_99
> 17> VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY'))
> 18> TABLESPACE DCMT_T_3_99 );
>
> > Statement processed.
>
> SQLWKS> insert into Doc values (1, '1','01/20/99');
> > 1 row processed.
>
> SQLWKS> insert into Doc values (2, '2','02/21/99');
> > 1 row processed.
>
> SQLWKS> insert into Doc values (3, '3','03/22/99');
> > 1 row processed.
>
> SQLWKS> update Doc set Document_Date = '01/23/99' where Document_ID = 1;
> > Statement processed.
>
> SQLWKS> update Doc set Document_Date = '03/21/99' where Document_ID = 2;
> >ORA-14402: updating partition key column would cause a partition change
>
> That's it.
> I'll appreciate any help.
> SY,
> Alexander G Bozhik
Received on Tue Apr 20 1999 - 00:00:00 CDT