If I have two seperate tablespaces DATA versus INDEX tablespace. There are some indexes using the DATA tablespace so I reuild those indexes to INDEX tablespaces.
One problem I've noticed though, is if the indexes get created originally in the DATA tablespace, even if you rebuild the index in another tablespace, you cannot reclaim that space easily. Anyone have any ideas on that issue?
thanks for your reply, I did coalesce DATA tablespace but It didn't release any space on the DATA tablespace.
Another questions I have is if the INDEX using space in the DATA tablespace, when we rebuild the INDEX, will it allocate the INDEX tablespace and release DATA tablespace ??? Or whatever in DATA tablespace will be in there and it will storage whatever in the future in the INDEX tablespace. Can someone give me some explanation on this .
No, each segment can only reside in one tablespace. I.e., an index can be in the data ts or the index ts, but not partially in both at the same time.
rebuild index my.index
storage (initial xx next xx)
then, it will be completely removed from the data ts and it will completely reside in the index ts. All storage it formerly used in the data ts will be freed. Then, you need to alter the data ts to coalesce free space.
If I understand the process correctly, the coalesce combines any "freed" up extents into as many contiguous extents as it can. When the next extent is needed, oracle will scan the table looking for an extent of the size it needs. If your newly recored extents are smaller than your "next" size in the storage options, then those extents will not be used.
From my readings, the only way to truely recover that space is to 1) kick everyone off the system. 2) export the data in the DATA tablespace, telling it to compress the extents 3) Truncate all the objects, 4) reimport the data. You have to tell the import to ignore any creation errors since your objects exist. On import, it will start at the beginning of the tablespace and work its way forward. This is the method for an Oracle7 system.
I seem to recall that in 8I, you can do this on a tablespace level.
Oracle does not really have a good tool for "defragging" a tablespace.
Please, everyone jump in and correct me if I am wrong. No pride in authorship and always willing to learn a new trick.