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

On Mon, 15 Nov 2004 15:15:58 -0500, "Jay" <no_at_spam.com> wrote:

>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
>>
>

You're on the verge of asking for a free lunch. If you are going to define "no down time" as "at no point in time will the user not be able to see the data" and at the same time you want to empty and re-load "five BIG tables" ....

Ok, how 'bout this? Let's call your original tables "old_1, old_2, etc. Load your new data into tables called "new_1, new_2, etc. Then

ALTER TABLE old_1

   RENAME TO old_1x;

ALTER TABLE new_1

   RENAME TO old_1;

DROP TABLE old_1x;

Of course, you will also need to account for redefining indexes, constraints, etc. I leave that exercise to the student. Received on Tue Nov 16 2004 - 07:49:14 CST