Howard J. Rogers wrote:
> "JZ" <ibm_97_at_yahoo.com> wrote in message
> news:10bc841c.0408051426.4dbab30b_at_posting.google.com...
>
>>Oracle 9205 on Linux 2.1
>>
>>Our app INSERTs into DB 24*7, around 200 rows per second (only insert
>>into 1 target table). What I want to do is to block the INSERT
>>temporarily, and during the block period, I want to do:
>>
>>1. rename the target table:
>>rename target to old_target;
>>
>>2. create new empty target table:
>>create table target as select * from old_target where 1=2;
>>
>>So when INSERT resumes, the records can be inserted into the NEW
>>target table.
>>
>>And during block period, I don't wanna get any error messages, like
>>'target tables doesn't exist'. Basically I don't want to lose any data
>>from INSERT fired by our app.
>>
>>How can I do this?
>>
>>Thanks a lot!
>
>
> Just to add my tuppence-worth: the suggestion to use dbms_redefinition is
> inappropriate for your purposes, since you want to insert into a new, empty
> table, not to have the existing table be redefined but with all its existing
> records still extant (which is what dbms_redefinition will get you).
>
> Much closer to the mark are those who have suggested partitioning. In
> particular, you want to investigate the exchange partition option, and you
> might want to read up on the various scenarios where partition exchange is a
> powerful answer to a 'rolling history' kind of problem, such as you are
> describing..
>
> Trouble with partitioning is it costs money. Quite a lot of it.
>
> But it's the best answer there is to your issue.
>
> Regards
> HJR
DBMS_REDEFINITION does insert records into a new table ... the one
defined in the redefinition procedure as the interim table. And, as you
indicate, the price is quite a bit lower.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Aug 06 2004 - 00:37:23 CDT
