Hi Tom,
We have a 300GB database. It has been proposed by the database manager that a tablespace reorg will potentially free up 30-60g.
I think this is because some of the tablespaces have high HWMs and quite a bit of free space - which cannot be resized down. Also many objects have grown significantly larger than the standard for their tablespaces
e.g
TABSML houses all tables smaller than 1GB
IDXSML houses all indexes smaller than 1GB
TABMED 5GB > tables > 1GB IDXMED 5GB > indexes > 1GB
TABLRG 15GB > tables > 5GB IDXLRG 15GB > indexes >5 GB
TABVLRG tables > 15GB IDXVLRG indexes > 15GB
But we are using locally managed tablespaces - all with uniform extent size 128K
Objects that have "outgrown" their tablespaces are proposed to be moved to an appropriate tablespace.
I have been looking at
http://asktom.oracle.com/pls/asktom/f?p=100:11:2020153653400302::::P11_QUESTION_ID:47812348053 and would like to ask you about the followup on January 25, 2010 1am:
"why? why are you doing this? are you sure it is even worth our time? "
I am asking myself these questions: We have a cloned copy of this database to test different ways to reorg the tablespaces. Already I have found that it took over 3 hours just to rebuild 2 indexes across tablespaces (4GB and 5GB) in size. To top that off the rebuild caused the indexes to be larger in size!
If we were to run this on the live database we would probably have 24hours to do it.
Are space re-orgs even needed with locally managed tablespaces?
I have pondered the following questions?
--Do they want you to move the datafiles from one location to other?? No
--Shrink the datafiles ?? Yes - to reduce the size and free up space
--Rename (name) the tablespaces ?? No
--Change tablespaces from data dictionary to LMT?? No
--Partition the table(more tablespaces) ?? No - no partioning on this DB
--Change storage clauses for Tablespaces?? No
Are there any other benefits re-orging would bring to warrant the time spent on it ?
Thanks very much for you advice
... But we are using locally managed tablespaces - all with uniform extent size 128K
...
At first I was "yes, good", and then a was very much "ugh, bad bad idea"
128K??? uniform size?
I disagree with this statement:
--Change storage clauses for Tablespaces?? No
I would mark that as YES, a resounding YES. You want to use autoallocate.
If it is taking 3 hours to rebuild 2 indexes - that must be a pretty slow machine. I think I could do that on my laptop in less time.
Now, to answer.
I'm not sure that reorganizing the entire database to pick up 30-60gig of space *temporarily* would be worth the effort. You are using uniform extents so you know that space is infinitely reusable. It must be true that at some time you had actually filled up the entire database (it had 30-60gb more data in it at some point) since we would reuse any existing extent before expanding anything. So, it is likely to me (this is my assumption) that this space will be needed again in the near future and you won't have reclaimed anything really.
If this database is not going to grow, if this database is now read only, then a reorg might be something to consider - but I would change the storage from uniform to system allocated extents.