Inserting records into table makes UNDOTBS1 very big !
Posted on 2008-10-27
Hello Experts !
I've got two tables in Oracle 10.1g
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 questions are:
1. Is the size of UNDOTBS1.DBF affected because setting up "ALTER SYSTEM SET UNDO_RETENTION = 5" or setting up "ALTER SYSTEM SET UNDO_RETENTION = 900";
2. 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!