Thanks for the great inputs Mark. It is a non-partitioned table. Data pump is a
good option. CTAS is another good option since I can re-organize the columns
and data. Our deletes are more than the inserts, as we are planning for a huge
cleanup.
I will test both CTAS and data pump options.
BA
From: S3v3n11 [mailto:neisdsa@xxxxxxxxx]
Sent: Thursday, January 19, 2017 6:05 AM
To: mwf@xxxxxxxx
Cc: Bheemsen Aitha; Powell, Mark; ORACLE-L
Subject: Re: Shrink a large table (other options)
What about good ole datapump? If your outage window is large enough export the
table, drop it and import it. This will not only re-org the table but will
rebuild the indexes as well.
On Thu, Jan 19, 2017 at 7:59 AM, Mark W. Farnham
<mwf@xxxxxxxx<mailto:mwf@xxxxxxxx>> wrote:
Has the number of rows in the table permanently declined? Then you might need
to shrink. If you delete a lot of rows and you insert a lot of rows in
comparable quantities and size, then Oracle will re-use the space in accordance
with your pctfree and pctused settings.
If you have had a longer term decline in the size, then you might need to do
something.
What your best option is may require study, but at the heart of it “copy keep”
is what you want to do. If you have partitioning AND downtime is possible, easy
as pie: copy what you want to keep and exchange.
If you don’t have partitioning, but you have enough down time to CTAS the rows
you want to keep, then you can do the rename dance (remember to reapply your
indexes, if any, before downtime is over.) IF you do this, consider ordering
your data on the copy out if you have a dominant index that is used for
queries. (Order the data to match the column order of your dominant index.)
When indexed access on the dominant index is then used, fewer blocks will be
accessed to retrieve the data you need.
If this dominant index matches your delete order tendency, even better, since
blocks in the future will tend to reach pctused emptiness become eligible for
new inserts sooner than if you have swiss cheese deletes (I always think of
Craig when swiss cheese free space comes up, he should dust off that talk),
then you might be adding blocks from freespace when there is plenty of air in
your existing blocks.
All that is blather, though, if your new rows arrive by direct inserts
(append), because those always add new blocks above the current high water
mark. That is the opposite condition of what Mark Powell was referencing below.
Mark’s advice works pretty doggone well except for some edge cases, two of
which are: 1) Swiss cheese deletes that leave many blocks just a little too
full to get back on the free list and 2) New arrivals are in append mode.
Oh, third case: No partitioning and not enough time to copy keep in one fell
swoop. Create a union view including your current table and a new table. Copy
append chunks of data in acceptable sizes to lock. Select them for update, copy
them, delete them commit. Rinse and repeat. Messy, and a lot of overhead, but
it works and you control the chunk size. This is sort of why they added the
commands that are failing for you.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Bheemsen Aitha
Sent: Wednesday, January 18, 2017 2:47 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@xxxxxxx]
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx<mailto:baitha@xxxxxxxxxxxxxxxxx>>
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
