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

From: Jay <no_at_spam.com>
Date: Mon, 15 Nov 2004 15:15:58 -0500
Message-ID: <cnb2ka$jod$1@msunews.cl.msu.edu>

Mark,

In your first reply, you said truncate the tables. and no down time. Well there might not be no down time but user will be see no data at some point, right?

Thanks
-Jay

"Mark Bole" <makbo_at_pacbell.net> wrote in message news:tu7md.44396$QJ3.39417_at_newssvr21.news.prodigy.com...
> Jay wrote:
> > it's an Oracle 8i and it's in Linux box.
> > My inserting scripts are looks like
> >
> > INSERT /* +append */ into target_table( col1 ,col2 ) VALUES ('241319',
> > '61');
> > INSERT /* +append */ into target_table( col1 ,col2 ) VALUES ('241319',
> > '61');
> > INSERT /* +append */ into target_table( col1 ,col2 ) VALUES ('241319',
> > '61');
> > INSERT /* +append */ into target_table( col1 ,col2 ) VALUES ('241319',
> > '61');
> > .
> >
> > I am thinking of creating csv file to use loader too.
> > Last time, I ran into diskspace problem because of log files.
> >
> > Thanks.
> >
> > -Jay
> [...]

>

> Consider using PL/SQL bulk binds.

>
>
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#28134

>

> If you are going to take a user outage during this process anyway,
> consider turning off archivelogmode until it is done, and then turning
> it back on and taking a full database backup before letting users back
> in. That will avoid disk space problems and speed things up too.

>

> Purists will say that you should have enough disk space for both
> rollback (no UNDO in 8i) and archivelogs to treat the whole process as a
> single transaction, but that's not always realistic -- in any event, do
> some tests first, and have a way to detect any failures part way through
> and a plan to recover in that case.

>

> Another approach could be to perform your process using a database on
> another server and then use tranportable tablespaces to plug the result
> back into your main database.

>

> -Mark Bole

> Received on Mon Nov 15 2004 - 14:15:58 CST