Re: oracle partitioning issue

From: Jim Kennedy <jim>
Date: Sun, 21 May 2006 08:13:46 -0700
Message-ID: <A56dnXqgsrGSHu3ZnZ2dnUVZ_tSdnZ2d@comcast.com>

<msreejith_at_yahoo.com> wrote in message
news:1148213621.268630.278140_at_y43g2000cwc.googlegroups.com...
> Thanks Brian and Jim for your inputs.
>
> Both of your solutions require copying the data from a "new_table" to
> "old_table" using something like:
> INSERT INTO new_table SELECT * FROM old_table
>
> Would this work seamlessly... considering factors like rollback error
> and other applications querying the table whilst this operation is
> happening. Pls let me know if there are any precautions to be taken
> care of.
>
> Thanks a lot,
> Srm
>

1.Assuming you aren't updating the rows at the same time.
2.create new _table partitioned on the col you want.
3. rename old table to old1
4. create view called old_table of new_table union old_table1;
5. insert into new table from old_table1 where new_partition_column=x;
6. delete from old_table1 where new_partition_column=x;
7. commit;

repeat steps 5,6,7 until done.
once done, create view as select from new_table. drop old_table1

Try this in a test case. You should notice that the inserted rows operation is atomic and you shouldn't see any missing or doubled rows when querying the view from another session.
Jim Received on Sun May 21 2006 - 10:13:46 CDT