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

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:31:37 CST