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

From: Jay <no_at_spam.com>
Date: Mon, 15 Nov 2004 14:52:42 -0500
Message-ID: <cnb18m$ihu$1@msunews.cl.msu.edu>

Thanks, Mark,

I'm not so sure how bulk binds can work with CSV file. and I said i want to minimize downtime ONLY IF I have to have down time.

-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 - 13:52:42 CST