Re: Block DML!?

"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 Received on Thu Aug 05 2004 - 22:57:19 CDT