Inserting records into table makes UNDOTBS1 very big

From: Big Charles <>
Date: Mon, 27 Oct 2008 09:26:35 -0700 (PDT)
Message-ID: <>

Hello Experts,

I've got two tables in Oracle 10.1g

16,594,824 records

0 records, same structure as TableA

I don't have too much free space in my disk, so I need to get tablespace UNDOTBS1 as small as possible.

When I try to insert the 16 millions rows of TableA into TableB like:

INSERT INTO TableB (select * from TableA);

then UNDOTBS1.DBF gets huge, like 7GB.

I set up UNDO_RETENTION in this way:

ALTER SYSTEM SET UNDO_RETENTION = 5; I thought that doing that, UNDOTBS1.DBF won't get big.

My question is:

Is there any way that UNDOTBS1.DBF and UNDOTBS1 don't grow so much? I know how to shrink UNDOTBS1.DBF after all transactions are commit, but don't know how to prevent it!

Thank you! Received on Mon Oct 27 2008 - 11:26:35 CDT

Original text of this message