RE: Tablespace shrink, HWM, interval partitionig
Date: Tue, 29 Mar 2011 07:03:12 -0400
Message-ID: <001d01cbee00$e6f4e190$b4dea4b0$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of John Hallas
Sent: Monday, March 28, 2011 10:55 AM
To: s.cislaghi_at_gmail.com; 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 29 2011 - 06:03:12 CDT