Why do anything? If you have data to delete on a regular basis then new data
must be coming in on a regular basis so just allow Oracle to reuse the table
free space. If the data from the same time period is inserted and deleted
together then it is likely whole blocks are being emptied by the delete and
will be refilled by inserts.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>
Sent: Wednesday, January 18, 2017 2:35:53 PM
To: ORACLE-L
Subject: Shrink a large table (other options)
Hi Gurus,
I would like to know the opinion from experienced Oracle DBAs on shrinking a
fairly large table.
Database: Oracle 11gR2, OLTP
We have a fairly large table (75 Gig) that frequently undergoes with lots of
deletes. I tried to shrink the table several times to release the space and
improve performance, and I received UNDO segment error every time. The expected
amount of space release is 25%.
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'
Our UNDO tablespace size is 32 Gig. Here are the commands I used.
alter table <table name> enable row movement;
alter table <table name> shrink space;
I would like to know what are my other options.
Thanks
Bheem Aitha
