RE: Shrink a large table (other options)

Thanks for the input.

BA

From: Rama Krishna [mailto:ramakrishna.vydyula_at_gmail.com] Sent: Wednesday, January 18, 2017 12:04 PM To: Bheemsen Aitha
Cc: ORACLE-L
Subject: Re: Shrink a large table (other options)

Hi,

As Mark mentioned; if you are deleting data and adding new data, the blocks that get free during the deletes would be reused by new INSERTS there by the HIGH WATER MARK of the table would relatively stay the same.

May be you can also look at reOrganizing data to a new table space there by only the blocks for this table will be present there assuming this table stores tablespace with other large tables too.

RK.

On Wed, Jan 18, 2017 at 7:35 PM, Bheemsen Aitha <baitha_at_itradenetwork.com<mailto:baitha_at_itradenetwork.com>> wrote: 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

--

Thanks,
Ramakrishna.V
+91 7674 976 123
--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 18 2017 - 21:05:17 CET