Large copies

I have to move data from many tables from one Oracle instance to another. For small tables that require no data changes, I plan to do a simple

     insert into table select * from table_at_other;

For tables where I have to move too much data to do it in a single transaction, I am using the obscure SQL*Plus COPY statement:

     copy from user/passwd_at_other to user/password_at_new -
     insert table using select * from table;

For some tables I have to add an offset to a column, so I list the columns. The source database is Oracle 7.3.2 and the target is Oracle 8.0.5. The server is Compaq Alpha 8200 with 4 cpus. My questions are:
- Would a PL/SQL program offer better performance?

• Any tuning ideas? SQL*Plus offers set copycommit and set arraysize.
• Any server tuning ideas? We will turn off archiving, increase the number of DB_WRITERS, drop indexes on the destination tables.
• Would increasing DB_BLOCK_BUFFERS help?

Any ideas would be appreciated.
Dennis Williams
DBA
Lifetouch Inc.
dwilliamsNOSPAM_at_lifetouch.com
Remove NOSPAM from the email address.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 04 2000 - 16:05:27 CST