Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Huge undotbs during insert

Re: Huge undotbs during insert

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 27 May 2005 19:50:14 +0800
Message-ID: <42970976.6E8E@yahoo.com>


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

nologging is not a hint as far as I can recall...

However, with the append hint, the undo generation should be small - have you got indexes on the table ? best to set these to unusable before adding lots of rows

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------
Received on Fri May 27 2005 - 06:50:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US