Inserting records into table makes UNDOTBS1 very big
Date: Mon, 27 Oct 2008 09:26:35 -0700 (PDT)
Message-ID: <471be178-878b-4464-95d6-cada3a9d3ee6@m3g2000hsc.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:26:35 CDT