RE: Shrink a large table (other options)

From: Bheemsen Aitha <baitha_at_itradenetwork.com>
Date: Wed, 18 Jan 2017 19:47:15 +0000
Message-ID: <3E9FC3C66B6DD445A50671ECBA1F423E0215EC2655_at_plt-exch-01.Itradenetwork.com>



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 - 20:47:15 CET

Original text of this message