Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: oracle partitioning issue
<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;
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
![]() |
![]() |