RE: Tablespace shrink, HWM, interval partitionig

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <John.Hallas@xxxxxxxxxxxxxxxxxx>, <s.cislaghi@xxxxxxxxx>, "'Oracle L'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 29 Mar 2011 07:03:12 -0400

Thanks for the blog entry. That will make creating a test case for a given
version pretty easy.

 

And I really like canonical names, so if you find yourself with a tablespace
that reached a much higher size at some point in its life cycle and which
now never needs more than a small fraction of its total size, it seems like
you would like to be able to shrink it.

 

But the alter tablespace shrink clause only applies to temporary tablespace.

If you have a bigfile tablespace you can resize it, but there is no built-in
shuffledown, so it would be up to you to figure out getting low space queued
up to be allocated, moving stuff down there, and then doing the resize. I
haven't tested recently whether the highest extent ever allocated controls
the smallest you can resize to, whether it just makes sure you're not
throwing away active extents, or whether it doesn't even look (I'm doubting
that last though). If it is the highest extent you ever had, then it sounds
to me manual shuffling won't buy you anything.

 

I also haven't tested whether after a coalesce operation extents get
allocated from low to high. Let time I really scutinized that was with
dictionary managed tablespaces, where it looked for the lowest exact match
for the extent requested and then carved a piece off the low end of the
largest chunk of free space (which tended to be at the end of the datafile).
I haven't tried to figure out extent allocation patterns with locally
managed tablespaces, other than initially it uses space from low to high. I
have not looked into what order it uses once you have some dropped space.

 

If you have a smallfile tablespace and the first datafile is not too big,
you do a little dance. Let's say the first datafile is full, but the second
datafile is 50GB and you only ever use 5GB of it. Run coalesce, look at the
freespace, built interim_junk tables to take up that space exactly, add a
5GB datafile, copy what you want to keep to the new datafile, drop
everything in the second datafile, then drop the second datafile.

 

Now - I did oversimplify that a bit, because you might have tables with
extents in both file one and file two. (Remember, you cannot [at this
writing] drop the first file in a tablespace].) So you can't just make
datafile 3 big enough for everything and move it all there. But you can use
dba_extents to figure out the sizes. You may need to temporarily create a
file four to hold the things that stride files 1 and 2 to facilitate the
drop of datafile 2, and then move them back.

 

Somewhere in this, you might get to the point where the copying around to
drop the excessive data file is done so much more simply by copying
everything to a new tablespace and dealing with the rename issues.

But if the size is large and not too much has to be moved to empty a
non-first datafile that is much too large it can be worth the trouble.

 

Whether we collectively want Oracle to spend development effort on a shuffle
down operation is an interesting question. It was on the VLDB white board in
1991, but no one voted for it because we could work around it.

Now things are a lot bigger, so the remaining steady state of occupied space
could cause inconveniently long maintenance windows to copy elsewhere and
still be a tiny fraction of the tablespace size when it was its biggest. So
many it would get some votes.

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of John Hallas
Sent: Monday, March 28, 2011 10:55 AM
To: s.cislaghi@xxxxxxxxx; Oracle L
Subject: RE: Tablespace shrink, HWM, interval partitionig

 

I sympathise Stefano as I am currently doing similar work. In my case we had
a 2.8Tb tablespace that had 56Gb of data in it. 

Another has 900Gb total and 500Gb free. In both cases the space was not at
the end and therefore I could not resize the datafile downwards.

 

I think that HWM on datafiles issue is an area that Oracle have completely
overlooked.  

 

Another problem I am seeing is that when you do identify which objects are
at the end of the datafile and move them within the tablespace they do not
necessarily go to the beginning and you can move objects around for no
appreciable gain.

 

Another word of caution if using rename tablespace after moving segments
into a new tablespace beware ORA-00959 due to a problem I blogged about
last week.

 

http://jhdba.wordpress.com/2011/03/25/ora-00959-tablespace-_deleted50-does-n
ot-exist/

 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stefano Cislaghi
Sent: 28 March 2011 10:34
To: Oracle L
Subject: Tablespace shrink, HWM, interval partitionig

 

My tablespace is about 30gb. As HWM is at the end of tablespace I've

tried with ALTER TABLE ... MOVE PARTITION ... without success.

Ok I know I can export, recreate tablespace and import. Also I can

move the table to another tablespace and move back ... but I'm looking

for a correct solution to be used ONLINE.

 

 


______________________________________________________________________
Wm Morrison Supermarkets Plc is registered in England with number 358949.
The registered office of the company is situated at Gain Lane, Bradford,
West Yorkshire BD3 7DL. This email and any attachments are intended for the
addressee(s) only and may be confidential. 

If you are not the intended recipient, please inform the sender by replying
to the email that you have received in error and then destroy the email. 
If you are not the intended recipient, you must not use, disclose, copy or
rely on the email or its attachments in any way. 

This email does not constitute a contract in writing for the purposes of the
Law of Property (Miscellaneous Provisions) Act 1989.

Our Standard Terms and Conditions of Purchase, as may be amended from time
to time, apply to any contract that we enter into. The current version of
our Standard Terms and Conditions of Purchase is available at:
http://www.morrisons.co.uk/gscop

Although we have taken steps to ensure the email and its attachments are
virus-free, we cannot guarantee this or accept any responsibility, 
and it is the responsibility of recipients to carry out their own virus
checks. 
______________________________________________________________________

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2011