Home » RDBMS Server » Server Administration » Undo tbsp taking more space than the table size (Oracle, 9.2.0.6.0, Windows Server 2003)
Undo tbsp taking more space than the table size [message #297496] Fri, 01 February 2008 02:40 Go to next message
irremediable
Messages: 38
Registered: December 2007
Member
Hello!
I need to insert about 3.000.000 from one table into another.
The second table is empty and is newly created.
Undo tbsp consists of 4 files each starting at 1024 mb
with maximum size of 3072 mb.

The table current size
 select 
   segment_name           table_name,    
   sum(bytes)/(1024*1024) table_size_meg 
from   
   user_extents 
where  
   segment_type='TABLE' 
and    
   segment_name = 'TABLE_NAME' 
group by segment_name
 


is 1280 mb.
Before insertion of new rows the UNDO tbsp was empty.
After the statemet
INSERT INTO TABLE2 SELECT * FROM TABLE1

was run the UNDO tbsp grew to nearly 800 MB in each of it's files and kept growing.
This is the only statement that's currently running on the database.
At the end the undo tbsp reached it's maximum size with the
error "Unable to allocate extent in tablespace UNDO_TBSP".
What can help me in this case?
Why undo tbsp takes more space than the table size?
Thank you.
Re: Undo tbsp taking more space than the table size [message #297498 is a reply to message #297496] Fri, 01 February 2008 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Remove all indexes from the table
(2/ Put the table in nologging mode)
3/ Insert with APPEND hint
4/ Recreate the indexes

Regards
Michel
Re: Undo tbsp taking more space than the table size [message #297520 is a reply to message #297496] Fri, 01 February 2008 04:06 Go to previous message
irremediable
Messages: 38
Registered: December 2007
Member




It helped.
Thank's a lot!
Previous Topic: Delete data from v$log_history
Next Topic: Log Miner error
Goto Forum:
  


Current Time: Mon Dec 05 05:11:14 CST 2016

Total time taken to generate the page: 0.14661 seconds