Prev: oracle 11g takes much longer to start
Next: Waht deos following exxplain plan mean?
From: vsevolod afanassiev on 18 May 2010 08:30
I got rather large table (HWM: 1.5 billion records, currently 1
billion, 100 GB+, 3 indexes)
that I tried to shrink. After 5 hours 44 min I got this error:

SQL> alter table owner.DATA shrink space cascade;
alter table owner.DATA shrink space cascade
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_01'

This is 10.2.0.4 (64-bit on AIX), and size of UNDO_01 tablespacee is
fixed (autoextend disabled for all datafiles). According to Admin
Guide in this situation Oracle should automatically adjust
TUNED_UNDORETENTION. V$UNDOSTAT shows that TUNED_UNDORETENTION was
adjusted as expeted, tablespace is 64 GB, 8k block size, database
generated 300,000 undo blocks in 10 min interval or 1.8 million per
hour, so 64 GB tablespace should be sufficient to hold more than 2
hours of undo.

Investigating further I found the reason: SHRINK statement generates
one huge transaction.
So on one hand SHINK is supposed to be better than ALTER TABLE MOVE as
you need some space to move table to while SHRINK doesn't need extra
space. On the other hand SHRINK needs huge undo to complete. I think
SHRINK should have been impleemented as series of small transactions.




 | 
Pages: 1
Prev: oracle 11g takes much longer to start
Next: Waht deos following exxplain plan mean?