Thanks for the input.
BA
From: Rama Krishna [mailto:ramakrishna.vydyula@xxxxxxxxx]
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@xxxxxxxxxxxxxxxxx<mailto:baitha@xxxxxxxxxxxxxxxxx>> 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
