Re: delete five BIG tables and insert new records on same tables

"PC" <no_at_spam.com> wrote in message news:<cnalii$855$1_at_msunews.cl.msu.edu>...

> What I want is minimal down time, if I have to have little down time.
> I have sql insert scripts to run.

Here's a nice (imho) technique that would minimize down time and save grants, constraints, triggers etc that you have on the table (you have to disable constraints, triggers etc and enable them afterwards,
but you don't have to *recreate* them, which is always error-prone):

dellera_at_ORACLE9I> create table t (x int);

Table created.

dellera_at_ORACLE9I> create index t_idx on t(x);

Index created.

dellera_at_ORACLE9I> create table t_new
  2 partition by range (x) (
  3 partition t_new values less than (maxvalue)   4 )
  5 as select * from t where 1=0;

Table created.

Here i would use sqlloader direct-path insert in real life, but for illustration purposes only:

dellera_at_ORACLE9I> insert into t_new (x) values (1);

1 row created.

dellera_at_ORACLE9I> insert into t_new (x) values (2);

1 row created.

dellera_at_ORACLE9I> insert into t_new (x) values (3);

1 row created.

dellera_at_ORACLE9I> create index t_new_idx on t_new (x) LOCAL;

Index created.

The downtime is limited to the execution of the following (very fast) command, plus of course reenabling constraints (could be done in NOVALIDATE) and so on :

dellera_at_ORACLE9I> alter table t_new exchange partition t_new with table t
  2 INCLUDING INDEXES WITHOUT VALIDATION; Table altered.

dellera_at_ORACLE9I>
dellera_at_ORACLE9I> select * from t;

         X

         1
         2
         3

dellera_at_ORACLE9I> select index_name, status from user_indexes where table_name = 'T';

INDEX_NAME                     STATUS
------------------------------ ------------------------
T_IDX                          VALID

Note that you could also use rows contained in t while building t_new - in fact i use this for reorganizations.

hth
Alberto Dell'Era Received on Tue Nov 16 2004 - 17:03:00 CST