Re: Huge undotbs during insert

astalavista wrote:
> Hi,
>
> I have to duplicate a big table
> so I did:
>
> insert /*+ append nologging */
> into table2
> select * from table1;
>
>
> The operation failed due to
> the filling of the UNDOTBS
> Is it normal to have up to 4 times ( or more ) the size
> of table1 in undotbs ?
>
>
> How can I speed up this operation ?
> I tried parallel :
>
> alter session enable parallel dml;
> insert /*+ append nologging parallel (table1,4) */
> into table2
> select * from table1;
>
> but no parallel processing occurred ...
>
> Thanks for your help ...
>
> Oracle 9.2.0.1
> HP-UX 11.0

Hi,

Just a thought here, would the SQLPlus COPY command be of use in your scenario?

Using COPY, you could do the following:

SET COPYCOMMIT 1
SET ARRAYSIZE 1000
COPY FROM user/passwd_at_service APPEND table2 USING SELECT * FROM table1;

where user/passwd is a valid logon to your database and service is a valid service name to connect via TNS.

This would commit after every 1000 rows are inserted.

Using the above syntax will insert rows if the table exists and create it and insert rows if it doesn't.

I haven't used COPY extensively and I believe there MIGHT be issues with LONG columns, so that might be an issue for you. I have to say that I've no idea how the speed of COPY compares to other methods, so that might be another issue!

Just a thought anyway.

Regards,

Tim Kearsley
HBS Milton Keynes Received on Fri May 27 2005 - 07:35:06 CDT