ORA-30036 during ALTER TABLE SHRINK
Date: Tue, 18 May 2010 05:30:05 -0700 (PDT)
Message-ID: <3c1de1b7-9eb7-44ab-bdd9-e094d2e8c398_at_a2g2000prd.googlegroups.com>
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.
Received on Tue May 18 2010 - 07:30:05 CDT