Shrink a large table (other options)

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



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:35:53 CET

Original text of this message