Re: Inserting records into table makes UNDOTBS1 very big

From: <sybrandb_at_hccnet.nl>
Date: Mon, 27 Oct 2008 21:30:11 +0100
Message-ID: <g69cg4t2net95f51j80gjl29tttv5m6vqo@4ax.com>


On Mon, 27 Oct 2008 09:26:35 -0700 (PDT), Big Charles <cherediatech_at_yahoo.com> wrote:

>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!

One would first need to question why a properly designed system would need to have two identical tables.
Subsequently one would could imagine the /* +APPEND */ hint would work, but there is too little background to determine whether this is feasible at all.
One would also consider splitting up the transaction in smaller transactions, determined by some formal criterion, like 'all records starting with the letter 'A'
One would also consider disk is cheap nowadays.

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Mon Oct 27 2008 - 15:30:11 CDT

Original text of this message