-
My tablespace is heavily fragmented. I guess.
I have 30 datafiles for this tablespace.
Each datafile has 1 GIG.
Out of the 1 GIG per datafile, only 30 -50 %
is used.
I have come to a conclusion that the spaces
are not contigous. Also the tablespace has
250 tables(big, medium and small). Also most
of the small to medium tables has a lot of
extents.
This is what I have decided to do:
1.Export the entire database with compress=y
NOTE:The only table with a lot of extents
is 650M.
2.Drop all the tables/truncate the
tables with drop storage
3.Import all entire database
What do you guys think about this approach?
-
That will be a pretty extreme measure to take.
Is this tablespace the most significant part of you database ?
Why cann't you recreate just the tables that are causing the fragmentation ?
-
Create three tablespaces one for big,medium and the small .
each tablespace will have a single datafile with 2GB size to start with .
move all the small tables in the databases to the 3 tablespace to start with .You can do that with alter table move storage
then with the medium and then the large .
then rebuild the indexes ... accordingly last .
drop the old tablespace ...
