Re: Inserting records into table makes UNDOTBS1 very big
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 DBAReceived on Mon Oct 27 2008 - 15:30:11 CDT