Inserting records into table makes UNDOTBS1 very big

From: Big Charles <cherediatech_at_yahoo.com>
Date: Mon, 27 Oct 2008 09:11:05 -0700 (PDT)
Message-ID: <149a3f29-7d30-4172-9dc8-7c3dd83b2217@m32g2000hsf.googlegroups.com>


Hello Experts,

I've got two tables in Oracle 10.1g

TableA
16,594,824 records

TableB
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:11:05 CDT

Original text of this message