RE: Shrink a large table (other options)

From: Bheemsen Aitha <baitha_at_itradenetwork.com>
Date: Wed, 18 Jan 2017 20:06:24 +0000
Message-ID: <3E9FC3C66B6DD445A50671ECBA1F423E0215EC2716_at_plt-exch-01.Itradenetwork.com>

Thanks for your reply. Unfortunately we have lot of indexes on that table. Maybe I should just stick with not shrinking it.

BA

From: Powell, Mark [mailto:mark.powell2_at_hpe.com] Sent: Wednesday, January 18, 2017 12:03 PM To: Bheemsen Aitha; ORACLE-L
Subject: Re: Shrink a large table (other options)

Even so, it is unlikely that shrinking the table is going to improve performance. You should take careful performance measurements to see if this action is truly justified.

If you have a window and space then ALTER TABLE MOVE followed by ALTER INDEX REBUILD would likely work.

---

From: Bheemsen Aitha <baitha_at_itradenetwork.com<mailto:baitha_at_itradenetwork.com>> Sent: Wednesday, January 18, 2017 2:47:15 PM To: Powell, Mark; ORACLE-L
Subject: RE: Shrink a large table (other options)

New data is not from the same time period. Trying to get improved performance at the same time. And yes, we can have downtime for this.

Thanks
Bheem Aitha

From: Powell, Mark [mailto:mark.powell2_at_hpe.com] Sent: Wednesday, January 18, 2017 11:44 AM To: ORACLE-L; Bheemsen Aitha
Subject: Re: Shrink a large table (other options)

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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Bheemsen Aitha <baitha_at_itradenetwork.com<mailto:baitha_at_itradenetwork.com>> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 18 2017 - 21:06:24 CET